Examples of casts from XML schema data types to SQL data types

You can use the XMLCAST specification to cast an XML schema data type to an SQL data type.

Example: casting to compare XML values to SQL values

Suppose that the PORDER column of the PURCHASEORDER table contains the following document:

<PurchaseOrder xmlns="http://posample.org"  PoNum="5000" 
  OrderDate="2007-02-18" Status="Unshipped">
   <item>
       <partid>100-100-01</partid>
       <name>Snow Shovel, Basic 22"</name>
       <quantity>3</quantity>
       <price>9.99</price>
   </item>
   <item>
       <partid>100-103-01</partid>
       <name>Snow Shovel, Super Deluxe 26" Wide</name>
       <quantity>5</quantity>
       <price>49.99</price>
   </item>
</PurchaseOrder>
Use the XMLCAST specification to cast a value with the xs:string type to a value with the VARCHAR type so that you can compare the values.
SELECT P.POID FROM PURCHASEORDER P, PRODUCT R WHERE 
  R.PID = 
    XMLCAST(XMLQUERY(                                         
   'declare default element namespace "http://posample.org";  
   $d/PurchaseOrder/item[name="Snow Shovel, Basic 22"]/partid'   
   PASSING P.PORDER AS "d") AS VARCHAR(10))
The result table of the SELECT statement is:
POID
5000

Example: casting to order a result table

Suppose that the sample PRODUCT table has the following rows:

PID DESCRIPTION
100-100-01 
<product xmlns="http://posample.org" pid="100-100-01" >
 <description>
  <name>Snow Shovel, Basic 22"</name>
  <details>Basic Snow Shovel, 22" wide, straight handle 
with D-Grip</details>
  <price>9.99</price>
  <weight>1 kg</weight>
 </description>
</product>
100-101-01 
<product xmlns="http://posample.org" pid="100-101-01" >
 <description>
  <name>Snow Shovel, Deluxe 24"</name>
  <details>A Deluxe Snow Shovel, 24 inches wide, ergonomic 
curved handle with D-Grip</details>
  <price>19.99</price>
  <weight>2 kg</weight>
 </description>
</product
100-103-01 
<product xmlns="http://posample.org" pid="100-103-01" >
 <description>
  <name>Snow Shovel, Super Deluxe 26"</name>
  <details>Super Deluxe Snow Shovel, 26" wide, ergonomic 
battery heated curved handle with upgraded D-Grip</details>
  <price>49.99</price>
  <weight>3 kg</weight>
 </description>
</product>
100-201-01 
<product xmlns="http://posample.org" pid="100-201-01" >
 <description>
  <name>Ice Scraper, Windshield 4" Wide</name>
  <details>Basic Ice Scraper 4" wide, foam handle</details>
  <price>3.99</price>
 </description>
</product>
Use the XMLCAST specification to cast values in XML documents in the sample PRODUCT table to an SQL data type so that you can use those values in an ORDER BY clause.
SELECT PID
 FROM PRODUCT
 ORDER BY 
  XMLCAST(XMLQUERY ('declare default element namespace "http://posample.org";
  $d/product/description/name'
  PASSING DESCRIPTION AS "d") AS VARCHAR(128))
The result table of the SELECT statement is:
PID
100-201-01
100-100-01
100-101-01
100-103-01

Example: casting an attribute node for retrieval of an attribute value

When you retrieve an attribute value, you cannot serialize the attribute node directly. For example, this query results in an error:

SELECT XMLQUERY('declare default element namespace "http://posample.org";
/PurchaseOrder/@PoNum' PASSING PORDER)
 FROM PURCHASEORDER
 WHERE POID=5000

Instead, you can select the attribute node, and then use XMLCAST to serialize the selected node before retrieving the attribute value. For example:

SELECT XMLCAST(XMLQUERY('declare default element namespace "http://posample.org";
/PurchaseOrder/@PoNum' PASSING PORDER) as INT)
 FROM PURCHASEORDER
 WHERE POID=5000

Example: casting xs:date values to the DATE type

If you cast an xs:date value without a time zone component to a DATE type, the result is the same as the input. For example:
SELECT XMLCAST(XMLQUERY(' "2007-10-12" ') AS DATE)
 FROM SYSIBM.SYSDUMMY1
The result is 2007-10-12.
If you cast an xs:date value with a time zone component to a DATE type, the result is adjusted to UTC time. For example:
SELECT XMLCAST(XMLQUERY(' "2007-10-12+13:00" ') AS DATE)
 FROM SYSIBM.SYSDUMMY1
The input value is assumed to be at the beginning of the day on 2007-10-12, in a time zone that is 13 hours ahead of UTC time. Therefore, after the date is adjusted to UTC time, the result is 2007-10-11.

Example: casting xs:time values to the TIME type

If you cast an xs:time value without a time zone component to a TIME type, the result is the input value with the fractional part truncated. For example:
SELECT XMLCAST(XMLQUERY(' "13:20:15.054" ') AS TIME)
 FROM SYSIBM.SYSDUMMY1
The result is 13:20:15.
If you cast an xs:time value with a time zone component to a TIME type, the result is adjusted to UTC time, and the fractional part is truncated. For example:
SELECT XMLCAST(XMLQUERY(' "13:20:15.054+05:00" ') AS TIME)
 FROM SYSIBM.SYSDUMMY1
The input value is in a time zone that is five hours ahead of UTC time. Therefore, after the time is adjusted to UTC time, the result is 08:20:15.

Example: casting xs:dateTime values to the TIMESTAMP or TIMESTAMP(p) type

If you cast an xs:dateTime value without a time zone component to a TIMESTAMP(p) type, and the number of fractional digits in the input value is less than p, the result is the input value with the fractional part expanded to p digits. If p is not specified, the default precision for the result is 6. For example:
SELECT XMLCAST(XMLQUERY('xs:dateTime("2009-03-25T05:01:01.123456789")') 
  AS TIMESTAMP(12))
 FROM SYSIBM.SYSDUMMY1
The result is 2009-03-25 05:01:01.123456789000.
If you cast an xs:dateTime value without a time zone component to a TIMESTAMP(p) type, and the number of fractional digits in the input value is greater than p, the result is the input value with the fractional part truncated to p digits. If p is not specified, the default precision for the result is 6. For example:
SELECT XMLCAST(XMLQUERY('xs:dateTime("2009-03-25T05:01:01.123456789")') 
  AS TIMESTAMP)
 FROM SYSIBM.SYSDUMMY1
The result is 2009-03-25 05:01:01.123456.
If you cast an xs:dateTime value with a time zone component to a TIMESTAMP(p) type, and the number of fractional digits in the input value is less than p, the result is adjusted to UTC time, and the fractional part is expanded to p digits. If p is not specified, the default precision for the result is 6. For example:
SELECT XMLCAST(XMLQUERY(' "2009-03-25T05:01:01.123456789+08:00" ') 
  AS TIMESTAMP(12))
 FROM SYSIBM.SYSDUMMY1
The input value is in a time zone that is eight hours ahead of UTC time. Therefore, after the date and time are adjusted to UTC time, the result is 2009-03-24 21:01:01.123456789000.
If you cast an xs:dateTime value with a time zone component to a TIMESTAMP(p) type, and the number of fractional digits in the input value is greater than p, the result is adjusted to UTC time, and the fractional part is truncated to p digits. If p is not specified, the default precision for the result is 6. For example:
SELECT XMLCAST(XMLQUERY(' "2009-03-25T05:01:01.123456789+08:00" ') 
  AS TIMESTAMP)
 FROM SYSIBM.SYSDUMMY1
The input value is in a time zone that is eight hours ahead of UTC time. Therefore, after the date and time are adjusted to UTC time, the result is 2009-03-24 21:01:01.123456.