rzasppdf.ps

Transcription

1 IBM i 7.2 Database SQL XML

2

3 IBM i 7.2 Database SQL XML

4 237 IBM i 7.1 ( 5770-SS1) RISC CISC IBM IBM i Version 7.2 Database SQL XML Programming Copyright IBM Corporation 2012, 2013.

5 SQL XML IBM i SQL PDF SQL SQL/XML XML XML.6 XML : XML 8 2: XML XML : XML XML : XML XML : XSLT 12 XML XML XML XML XML SQL/XML 18 : XML 19 : XML 20 : NULL XML : XSLT.. 21 : XSLT : XSLT : XSLT XML.. 29 SQL/XML 29 XML XML XML XMLTABLE XML : XMLTABLE 34 : XMLTABLE : XMLTABLE 35 : XMLTABLE : XMLTABLE XML XML XML CLI XML CLI XML SQL XML : SQL XML XML SQL SQLDA XML Java JDBC XML 48 SQLJ XML SQL XML 57 XML XML XML XML XML XML JDBC SQLJ XML XML XML XML XML XMLSERIALIZE XML.70 XML CCSID XML CCSID XML XML XML XML.. 73 Copyright IBM Corp. 2012, 2013 iii

6 XML 73 XML XML XML db2-xdb:defaultsqlschema 76 db2-xdb:rowset db2-xdb:table db2-xdb:column db2-xdb:locationpath db2-xdb:expression db2-xdb:condition db2-xdb:contenthandling 95 db2-xdb:normalization.. 99 db2-xdb:order db2-xdb:truncate db2-xdb:rowsetmapping 105 db2-xdb:rowsetoperationorder XML XML CDATA XML NULL XML XML : XML : : : : : XML SQL XML XML XML SQL XML XPath DB2 XPath / DB2 XPath DB2 XPath DB2 XPath XML XPath xs:anytype xs:anysimpletype xs:anyatomictype xs:untyped xs:untypedatomic xs:string xs:decimal xs:double xs:integer xs:boolean xs:date xs:time xs:datetime xs:duration xs:daytimeduration xs:yearmonthduration XML XPath DB2 XPath iv IBM i: SQL XML

7 XPath fn:abs fn:adjust-date-to-timezone fn:adjust-datetime-to-timezone fn:adjust-time-to-timezone fn:boolean fn:compare fn:concat fn:contains fn:count fn:current-date fn:current-datetime db2-fn:current-local-date db2-fn:current-local-datetime db2-fn:current-local-time fn:current-time fn:data fn:datetime fn:day-from-date fn:day-from-datetime fn:days-from-duration fn:distinct-values fn:exists fn:hours-from-datetime fn:hours-from-duration fn:hours-from-time function fn:implicit-timezone fn:last fn:local-name db2-fn:local-timezone fn:lower-case fn:matches fn:max fn:min fn:minutes-from-datetime fn:minutes-from-duration fn:minutes-from-time fn:month-from-date fn:month-from-datetime fn:months-from-duration fn:name fn:normalize-space fn:not fn:position fn:replace fn:round fn:seconds-from-datetime fn:seconds-from-duration fn:seconds-from-time fn:starts-with fn:string fn:string-length fn:substring fn:sum fn:timezone-from-date fn:timezone-from-datetime fn:timezone-from-time fn:tokenize fn:translate fn:upper-case fn:year-from-date fn:year-from-datetime fn:years-from-duration v

8 vi IBM i: SQL XML

9 SQL XML DB2 for IBM i (SQL) XML SQL XML XML XML XML : 235 IBM i 7.2 SQL XML SQL XML SQL XML Information Center v v PDF ( ) v v () Copyright IBM Corp. 2012,

10 required_item v required_item optional_item required_item optional_item v 1 1 required_item required_choice required_choice required_item optional_choice optional_choice required_item default_choice optional_choice optional_choice required_item optional_choice optional_choice default_choice required_choice required_choice v required_item repeatable_item 2 IBM i: SQL XML

11 , required_item repeatable_item v (: FROM) (column-name) v SQL PDF PDF PDF SQL XML PDF PDF 1. PDF 2. PDF 3. PDF 4. Adobe Reader PDF Adobe Reader Adobe Web ( SQL SQL/XML SQL XML XML XML XML XML XML DB2 SQL/XML XML SQL XML 3

12 XML XML 2 GB XML XML CCSID CCSID SQL_XML_DATA_CCSID QAQQINI 1208 (UTF-8) XML 1 Unicode 2 1 XML LOB 3.5 GB XML XML XML XML XML XML XML XML INSERT UPDATE XML LOB SQL LOB SQL XML XML SQL XML v ILE RPG v ILE COBOL v C C++ ( SQL DB2 CLI) v Java (JDBC SQLJ) SQL CREATE PROCEDURE XML XML SQL CREATE FUNCTION XML XML SQL SQL XML XML SQL XML XML URI XML (XSR) XML (XSR) XML XML XML XML XML XML 4 IBM i: SQL XML

13 XML IBM i XML XML XML XML XML XML XML XML XML DB2 XML XML XML XML XML XML XML XML XML SQL INSERT XML XML XML XML (XSR) SQL UPDATE XML XML XML XML XML XML XML XML (XSR) XML (XSR) XML (XSR) XML XML XML XML XML DB2 XSR XML SQL SQL XML 5

14 SQL XML XML XMLTABLE SQL XML DB2 XML XML XML XML XML DB2 XML XML XML XML XML XML 1 XML XML 1 ORDER BY XML 1 1 XML XML XML 6 IBM i: SQL XML

15 (OLAP) XML XML XML XML XML XML XML XML XML XML XML XML 1 XML DB2 XML XML v XML v XML XML v XML XML v XML XML v XSLT SQL XML 7

16 IBM i Navigator Run SQL SQL XML 1: XML XML XML Customer CREATE SCHEMA POSAMPLE; SET CURRENT SCHEMA POSAMPLE; CREATE TABLE Customer (Cid BIGINT NOT NULL PRIMARY KEY, Info XML); XML ALTER TABLE SQL 1 XML 2: XML XML XML SQL INSERT XML XML XML XML XML XML XML XML XML SQL XML XML XML XMLPARSE INSERT UPDATE XMLPARSE XML 2 XML 1 Customer 3 XML INSERT INTO Customer (Cid, Info) VALUES (1000, <customerinfo xmlns=" Cid="1000"> <name>kathy Smith</name> <addr country="canada"> <street>5 Rosewood</street> <city>toronto</city> <prov-state>ontario</prov-state> <pcode-zip>m6w 1E6</pcode-zip> </addr> <phone type="work"> </phone> </customerinfo> ); INSERT INTO Customer (Cid, Info) VALUES (1002, <customerinfo xmlns=" Cid="1002"> 8 IBM i: SQL XML

17 <name>jim Noodle</name> <addr country="canada"> <street>25 EastCreek</street> <city>markham</city> <prov-state>ontario</prov-state> <pcode-zip>n9c 3T6</pcode-zip> </addr> <phone type="work"> </phone> </customerinfo> ); INSERT INTO Customer (Cid, Info) VALUES (1003, <customerinfo xmlns=" Cid="1003"> <name>robert Shoemaker</name> <addr country="canada"> <street>1596 Baseline</street> <city>aurora</city> <prov-state>ontario</prov-state> <pcode-zip>n8x 7F8</pcode-zip> </addr> <phone type="work"> </phone> </customerinfo> ); SELECT * from Customer; SQL XML XMLSERIALIZE 3: XML XML SQL XML SQL XML XML SQL SQL UPDATE 2 1 (<street><city> <pcode-zip> ) UPDATE customer SET info = <customerinfo xmlns=" Cid="1002"> <name>jim Noodle</name> <addr country="canada"> <street>1150 Maple Drive</street> <city>newtown</city> <prov-state>ontario</prov-state> <pcode-zip>z9z 2P2</pcode-zip> </addr> <phone type="work"> </phone> </customerinfo> WHERE Cid = 1002; XML SELECT * from Customer; SQL XML XMLSERIALIZE SQL XML 9

18 Cid="1002" <street><city> <pcode-zip> XML XML 4: XML XML XML XML XML DTD (DTD DOCTYPE DTD ) IBM Rational Application Developer DTDXML XML XML (XSR) XML XML XML XML posample.customer XML CREATE PROCEDURE SAMPLE_REGISTER LANGUAGE SQL BEGIN DECLARE CONTENT BLOB(1M); VALUES BLOB( <?xml version="1.0"?> <xs:schema targetnamespace=" xmlns:xs=" elementformdefault="qualified"> <xs:element name="customerinfo"> <xs:complextype> <xs:sequence> <xs:element name="name" type="xs:string" minoccurs="1" /> <xs:element name="addr" minoccurs="1" maxoccurs="unbounded"> <xs:complextype> <xs:sequence> <xs:element name="street" type="xs:string" minoccurs="1" /> <xs:element name="city" type="xs:string" minoccurs="1" /> <xs:element name="prov-state" type="xs:string" minoccurs="1" /> <xs:element name="pcode-zip" type="xs:string" minoccurs="1" /> </xs:sequence> <xs:attribute name="country" type="xs:string" /> </xs:complextype> </xs:element> <xs:element name="phone" nillable="true" minoccurs="0" maxoccurs="unbounded"> <xs:complextype> <xs:simplecontent> <xs:extension base="xs:string"> <xs:attribute name="type" form="unqualified" type="xs:string" /> </xs:extension> </xs:simplecontent> </xs:complextype> </xs:element> <xs:element name="assistant" minoccurs="0" maxoccurs="unbounded"> <xs:complextype> <xs:sequence> <xs:element name="name" type="xs:string" minoccurs="0" /> <xs:element name="phone" nillable="true" minoccurs="0" maxoccurs="unbounded"> <xs:complextype> <xs:simplecontent > <xs:extension base="xs:string"> <xs:attribute name="type" type="xs:string" /> </xs:extension> </xs:simplecontent> 10 IBM i: SQL XML

19 </xs:complextype> </xs:element> </xs:sequence> </xs:complextype> </xs:element> </xs:sequence> <xs:attribute name="cid" type="xs:integer" /> </xs:complextype> </xs:element> </xs:schema> ) INTO CONTENT; CALL SYSPROC.XSR_REGISTER( POSAMPLE, CUSTOMER, CONTENT, null); END; SET PATH POSAMPLE; CALL SAMPLE_REGISTER; CALL SYSPROC.XSR_COMPLETE( POSAMPLE, CUSTOMER, null, 0); XSR QSYS2.XSROBJECTS XML () SELECT XSROBJECTSCHEMA, XSROBJECTNAME FROM QSYS2.XSROBJECTS WHERE XSROBJECTSCHEMA = POSAMPLE ; XSROBJECTSCHEMA XSROBJECTNAME POSAMPLE CUSTOMER XML INSERT UPDATE XMLVALIDATE XMLVALIDATE INSERT UPDATE INSERT posample.customer XML Customer Info XML INSERT INTO Customer(Cid, Info) VALUES (1004, XMLVALIDATE (XMLPARSE (DOCUMENT <customerinfo xmlns=" Cid="1004"> <name>robert Shoemaker</name> <addr country="canada"> <street>1596 Baseline</street> <city>aurora</city> <prov-state>ontario</prov-state> <pcode-zip>n8x 7F8</pcode-zip> </addr> <phone type="work"> </phone> <phone type="home"> </phone> <phone type="cell"> </phone> <phone type="cottage"> </phone> </customerinfo> PRESERVE WHITESPACE ) ACCORDING TO XMLSCHEMA ID posample.customer )); XMLVALIDATE XML XML XMLVALIDATE XMLPARSE XML INSERTUPDATE MERGE INSERT XMLPARSE XML XML Info SQL XML 11

20 SELECT Info FROM Customer; 3 XML 1 5: XSLT XSLTRANSFORM XML XSLTRANSFORM XML XML student ID <?xml version="1.0"?> <students xmlns:xsi=" <student studentid="1" firstname="steffen" lastname="siegmund" age="23" university="rostock"/> </students> XSLT XML HTML Web XSLT <?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl=" <xsl:param name="headline"/> <xsl:param name="showuniversity"/> <xsl:template match="students"> <html> <head/> <body> <h1><xsl:value-of select="$headline"/></h1> <table border="1"> <th> <tr> <td width="80">studentid</td> <td width="200">first Name</td> <td width="200">last Name</td> <td width="50">age</td> <xsl:choose> <xsl:when test="$showuniversity = true "> <td width="200">university</td> </xsl:when> </xsl:choose> </tr> </th> <xsl:apply-templates/> </table> </body> </html> </xsl:template> <xsl:template match="student"> <tr> <td><xsl:value-of select="@studentid"/></td> <td><xsl:value-of select="@firstname"/></td> <td><xsl:value-of select="@lastname"/></td> <td><xsl:value-of select="@age"/></td> <xsl:choose> <xsl:when test="$showuniversity = true "> <td><xsl:value-of select="@university"/></td> </xsl:when> 12 IBM i: SQL XML

21 </xsl:choose> </tr> </xsl:template> </xsl:stylesheet> XSLT 1. XML SET CURRENT SCHEMA USER; CREATE TABLE XML_TAB (DOCID INTEGER, XML_DOC XML, XSL_DOC CLOB(1M)); 2. XML XSLT INSERT 3 XSLT INSERT XSLT INSERT INTO XML_TAB VALUES (1, <?xml version="1.0"?> <students xmlns:xsi=" <student studentid="1" firstname="steffen" lastname="siegmund" age="23" university="rostock"/> </students>, ); <?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0"... </xsl:stylesheet> 3. XSLTRANSFORM XML SELECT XSLTRANSFORM (XML_DOC USING XSL_DOC AS CLOB(1M)) FROM XML_TAB; HTML <html> <head> <META http-equiv="content-type" content="text/html; charset=utf-8"> </head> <body> <h1></h1> <table border="1"> <th> <tr> <td width="80">studentid</td> <td width="200">first Name</td> <td width="200">last Name</td> <td width="50">age</td> </tr> </th> <tr> <td>1</td> <td>steffen</td><td>siegmund</td> <td>23</td> </tr> </table> </body> </html> XML ( HTML XHTML ) XSLT XSLT SQL XML 13

22 XML XSLT param 2 <xsl:param name="showuniversity"/> <xsl:param name="headline"/> CREATE TABLE PARAM_TAB (DOCID INTEGER, PARAM VARCHAR(1000)); INSERT INTO PARAM_TAB VALUES (1, <?xml version="1.0"?> <params xmlns=" <param name="showuniversity" value="true"/> <param name="headline">the student list...</param> </params> ); SELECT XSLTRANSFORM ( XML_DOC USING XSL_DOC WITH PARAM AS CLOB(1M)) FROM XML_TAB X, PARAM_TAB P WHERE X.DOCID=P.DOCID; HTML <html> <head> <META http-equiv="content-type" content="text/html; charset=utf-8"> </head> <body> <h1>the student s list...</h1> <table border="1"> <th> <tr> <td width="80">studentid</td> <td width="200">first Name</td> <td width="200">last Name</td> <td width="50">age</td> <td width="200">university</td> </tr> </th> <tr> <td>1</td> <td>steffen</td> <td>siegmund</td><td>23</td><td>rostock</td> </tr> </table> </body> </html> XML XML XML XML 14 IBM i: SQL XML

23 XML XML ALTER TABLE ADD XML 1 XML 2 XML CREATE TABLE Customer (Cid BIGINT NOT NULL PRIMARY KEY, Info XML, History XML); Customer MyCustomer XML SET CURRENT SCHEMA USER; CREATE TABLE MyCustomer LIKE Customer; ALTER TABLE MyCustomer ADD COLUMN Preferences XML; XML XML SQL INSERT XML XML 1.0 XML XML DB2 XML XML XML XML XML DB2 XML XMLPARSE XML XML XML XMLVALIDATE XML XML Customer MyCustomer XML c6.xml <customerinfo xmlns=" Cid="1015"> <name>christine Haas</name> <addr country="canada"> <street>12 Topgrove</street> <city>toronto</city> <prov-state>ontario</prov-state> <pcode-zip>n8x-7f8</pcode-zip> </addr> <phone type="work"> </phone> <phone type="home"> </phone> </customerinfo> : JDBC XML c6.xml XML SQL XML 15

24 PreparedStatement insertstmt = null; String sqls = null; int cid = 1015; sqls = "INSERT INTO MyCustomer (Cid, Info) VALUES (?,?)"; insertstmt = conn.preparestatement(sqls); insertstmt.setint(1, cid); File file = new File("c6.xml"); insertstmt.setbinarystream(2, new FileInputStream(file), (int)file.length()); insertstmt.executeupdate(); : C XML XML EXEC SQL BEGIN DECLARE SECTION; sqlint64 cid; SQL TYPE IS XML AS BLOB (10K) xml_hostvar; EXEC SQL END DECLARE SECTION;... cid=1015; /* Read data from file c6.xml into xml_hostvar */... EXEC SQL INSERT INTO MyCustomer (Cid,Info) VALUES (:cid, :xml_hostvar); XML XML XML XML DB2 XML XML v XML XML v INSERTUPDATE MERGE ( ) SQL XML XML XMLPARSE XML XMLPARSE XML XML XML XMLPARSE XML Unicode SQL XMLPARSE INSERT INTO MyCustomer (Cid, Info) VALUES (?, XMLPARSE(DOCUMENT CAST(? AS CLOB(1K)) PRESERVE WHITESPACE)) SQL XMLPARSE XML (XML AS BLOBXML AS CLOBXML AS DBCLOB ) 16 IBM i: SQL XML

25 XML XML XML (U+0020) (U+000D) (U+000A) (U+0009) <a> <b> </b> </a> <a> <b> and between </b> </a> XMLPARSE STRIP WHITESPACE PRESERVE WHITESPACE XML v XML XML DB2 v XML CURRENT IMPLICIT XMLPARSE OPTION STRIP WHITESPACE PRESERVE WHITESPACE XML DB2 CURRENT IMPLICIT XMLPARSE OPTION xmlvalidate(? ACCORDING TO XMLSCHEMA ID schemaname) xmlvalidate(?) xmlvalidate(:hvxml ACCORDING TO XMLSCHEMA ID schemaname) xmlvalidate(:hvxml) xmlvalidate(cast(? as xml) ACCORDING TO XMLSCHEMA ID schemaname) xmlvalidate(cast(? as xml))? XML :hvxml XML XML XML xml:space xml:space XML <b> XML xml:space="preserve" <a xml:space="preserve"> <b> <c>c</c>b </b></a> <b> XML xml:space="default" <a xml:space="default"> <b> <c>c</c>b </b></a> SQL XML 17

26 XML SQL/XML XML XML ( XML ) SQL/XML XML XML SQL XML SQL/XML XML XMLAGG XML NULL 1 XML XMLATTRIBUTES XML XMLELEMENT XMLCOMMENT XML XMLCONCAT XML XMLDOCUMENT XML XML DB2 XML XML XML XMLELEMENT XML XML DB2 XML XMLELEMENT XML XMLDOCUMENT XMLFOREST XML XML XMLGROUP XMLNAMESPACES XMLELEMENT XMLFOREST XMLPI XML XMLROW 18 IBM i: SQL XML

27 XMLTEXT XML XSLTRANSFORM XML XML : XML XML SQL/XML XML v <item> XMLELEMENT PRODUCT NAME v XMLAGG <item> <allproducts> v XMLNAMESPACES <allproducts> SELECT XMLELEMENT (NAME "allproducts", XMLNAMESPACES (DEFAULT ), XMLAGG(XMLELEMENT (NAME "item", p.name))) FROM Product p XML <allproducts xmlns=" <item>snow Shovel, Basic 22 inch</item> <item>snow Shovel, Deluxe 24 inch</item> <item>snow Shovel, Super Deluxe 26 inch</item> <item>ice Scraper, Windshield 4 inch</item> </allproducts> XMLAGG XMLROW XML SELECT XMLELEMENT (NAME "products", XMLNAMESPACES ( AS "po"), XMLROW(NAME AS "po:item")) FROM Product <products xmlns:po=" <row> <po:item>snow Shovel, Basic 22 inch</po:item> </row> </products> <products xmlns:po=" <row> <po:item>snow Shovel, Deluxe 24 inch</po:item> </row> </products> <products xmlns:po=" <row><po:item>snow Shovel, Super Deluxe 26 inch</po:item> </row> </products> <products xmlns:po=" <row><po:item>ice Scraper, Windshield 4 inch</po:item> </row> </products> SQL XML 19

28 : XML XML SQL/XML XML v XMLFOREST <prod> (name numinstock) PRODUCT NAME INVENTORY QUANTITY v <prod> <saleproducts> SELECT XMLELEMENT (NAME "saleproducts", XMLNAMESPACES (DEFAULT ), XMLAGG (XMLELEMENT (NAME "prod", XMLATTRIBUTES (p.pid AS "id"), XMLFOREST (p.name AS "name", i.quantity AS "numinstock")))) FROM PRODUCT p, INVENTORY i WHERE p.pid = i.pid XML <saleproducts xmlns=" <prod id=" "> <name>snow Shovel, Basic 22 inch</name> <numinstock>5</numinstock> </prod> <prod id=" "> <name>snow Shovel, Deluxe 24 inch</name> <numinstock>25</numinstock> </prod> <prod id=" "> <name>snow Shovel, Super Deluxe 26 inch</name> <numinstock>55</numinstock> </prod> <prod id=" "> <name>ice Scraper, Windshield 4 inch</name> <numinstock>99</numinstock> </prod> </saleproducts> : NULL XML XML NULL SQL/XML XMLELEMENT XMLFOREST XML NULL XMLELEMENT XMLFOREST EMPTY ON NULL NULL ON NULL NULL XMLELEMENT NULL EMPTY ON NULL XMLFOREST NULL NULL ON NULL INVENTORY LOCATION 1 NULL <loc> XMLFOREST NULL NULL 20 IBM i: SQL XML

29 SELECT XMLELEMENT (NAME "newelem", XMLATTRIBUTES (PID AS "prodid"), XMLFOREST (QUANTITY AS "quantity", LOCATION AS "loc")) FROM INVENTORY NULL <loc> <newelem prodid=" "> <quantity>5</quantity> </newelem> EMPTY ON NULL <loc> SELECT XMLELEMENT (NAME "newelem", XMLATTRIBUTES (PID AS "prodid"), XMLFOREST (QUANTITY AS "quantity", LOCATION AS "loc" OPTION EMPTY ON NULL)) FROM INVENTORY <loc> <newelem prodid=" "> <quantity>5</quantity> <loc/> </newelem> : XSLT XML XSLT (Extensible Stylesheet Language Transformations) XSLTRANSFORM XML HTML XML XSLT XML XPath XSLT XML XSLT XML HTML XML XML XSLT XML (troff ) XSLT 2 v (XML HTML ) v (1 XML XML SOAP ) XML XSLT XPath XML XSLT XSLT XSLT XSL (Extensible Stylesheet Language) XML XSL C Perl XSL XSL 1 template XML XSLT <xsl:template match="product"> SQL XML 21

30 XML <product> XSLT XSLT XML <?xml version="1.0"?> <product pid=" "> <description> <name>ice Scraper, Windshield 4 inch</name> <details>basic Ice Scraper 4 inches wide, foam handle</details> <price>3.99</price> </description> </product> <name> ( <product> pid ) Web XSLT <?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" xmlns:xsl=" <xsl:output method="html"/> <xsl:template match="/"> <html> <body> <h1><xsl:value-of select="/product/description/name"/></h1> <table border="1"> <th> <xsl:apply-templates select="product"/> </th> </table> </body> </html> </xsl:template> <xsl:template match="product"> <tr> <td width="80">product ID</td> <td><xsl:value-of select="@pid"/></td> </tr> <tr> <td width="200">product name</td> <td><xsl:value-of select="/product/description/name"/></td> </tr> <tr> <td width="200">price</td> <td>$<xsl:value-of select="/product/description/price"/></td> </tr> <tr> <td width="50">details</td> <td><xsl:value-of select="/product/description/details"/></td> </tr> </xsl:template> </xsl:stylesheet> XSLT HTML <html> <body> <h1>ice Scraper, Windshield 4 inch</h1> <table border="1"> 22 IBM i: SQL XML

31 <th> <tr> <td width="80">product ID</td><td> </td> </tr> <tr> <td width="200">product name</td><td>ice Scraper, Windshield 4 inch</td> </tr> <tr> <td width="200">price</td><td>$3.99</td> </tr> <tr> <td width="50">details</td><td>basic Ice Scraper 4 inches wide, foam handle</td> </tr> </th> </table> </body> </html> XSLT XML ( 1 1 ) HTML "product ID." XPath (: <xsl:template match="product">) XML (: <h1><xsl:value-of select="/product/description/ name"/></h1>) XSLTRANSFORM XSLTRANSFORM XSLT XML XML XSLT : XSLT XSLTRANSFORM 2 INSERT INTO XML_TAB VALUES (1, <?xml version="1.0"?> <students xmlns:xsi=" xsi:nonamespaceschemalocation = "/home/steffen/xsd/xslt.xsd"> <student studentid="1" firstname="steffen" lastname="siegmund" age="23" university="rostock"/> </students>, <?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl=" <xsl:param name="headline"/> <xsl:param name="showuniversity"/> <xsl:template match="students"> <html> <head/> <body> <h1><xsl:value-of select="$headline"/></h1> <table border="1"> <th> <tr> SQL XML 23

32 <td width="80">studentid</td> <td width="200">first Name</td> <td width="200">last Name</td> <td width="50">age</td> <xsl:choose> <xsl:when test="$showuniversity = true "> <td width="200">university</td> </xsl:when> </xsl:choose> </tr> </th> <xsl:apply-templates/> </table> </body> </html> </xsl:template> <xsl:template match="student"> <tr> <td><xsl:value-of select="@studentid"/></td> <td><xsl:value-of select="@firstname"/></td> <td><xsl:value-of select="@lastname"/></td> <td><xsl:value-of select="@age"/></td> <xsl:choose> <xsl:when test="$showuniversity = true "> <td><xsl:value-of select="@university"/></td> </xsl:when> </xsl:choose> </tr> </xsl:template> </xsl:stylesheet> ); XSLTRANSFORM XML HTML SELECT XSLTRANSFORM (XML_DOC USING XSL_DOC AS CLOB(1M)) FROM XML_TAB; <html> <head> <META http-equiv="content-type" content="text/html; charset=utf-8"> </head> <body> <h1></h1> <table border="1"> <th> <tr> <td width="80">studentid</td> <td width="200">first Name</td> <td width="200">last Name</td> <td width="50">age</td> </tr> </th> <tr> <td>1</td> <td>steffen</td><td>siegmund</td> <td>23</td> </tr> </table> </body> </html> HTML HTML XSLT 24 IBM i: SQL XML

33 : XSLT XSLTRANSFORM XML xsl:param INSERT INTO Display_productdetails values(1, <?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" xmlns:xsl=" <xsl:param name="headline"/> <xsl:param name="supermarketname"/> <xsl:template match="product"> <html> <head/> <body> <h1><xsl:value-of select="$headline"/></h1> <table border="1"> <th> <tr> <td width="80">product ID</td> <td width="200">product name</td> <td width="200">price</td> <td width="50">details</td> <xsl:choose> <xsl:when test="$supermarket = true "> <td width="200">big BAZAAR super market</td> </xsl:when> </xsl:choose> </tr> </th> <xsl:apply-templates/> </table> </body> </html> </xsl:template> <xsl:template match="product"> <tr> <td><xsl:value-of select="@pid"/></td> <td><xsl:value-of select="/product/description/name"/></td> <td><xsl:value-of select="/product/description/price"/></td> <td><xsl:value-of select="/product/description/details"/></td> </tr> </xsl:template> </xsl:stylesheet> ); XSLT CREATE TABLE PARAM_TAB (DOCID INTEGER, PARAM VARCHAR (10K)); INSERT INTO PARAM_TAB VALUES (1, <?xml version="1.0"?> <params xmlns=" <param name="supermarketname" value="true"/> <param name="headline">big BAZAAR super market</param> </params> ); SQL XML 25

34 SELECT XSLTRANSFORM (XML_DOC USING XSL_DOC WITH PARAM AS CLOB (1M)) FROM product_details X, PARM_TAB P WHERE X.DOCID=P.DOCID; HTML XML <html> <head> <META http-equiv="content-type" content="text/html; charset=utf-8"> </head> <body> <h1></h1> <table border="1"> <th> <tr> <td width="80">product ID</td> <td width="200">product Name</td> <td width="200">price</td> <td width="50">details</td> </tr> </th> </table> </body> </html> XSLTRANSFORM HTML XML (EDI ) EDI SOAP (E ) XML XSLT : XSLT XML XSLT XSLT XML XML XSLT XML XSLTRANSFORM XSLT XML CREATE XMLDATA XMLTRANS XMLDATA XML XMLTRANS XSLT CREATE TABLE XMLDATA (ID BIGINT NOT NULL PRIMARY KEY, XMLDOC XML ); CREATE TABLE XMLTRANS (XSLID BIGINT NOT NULL PRIMARY KEY, XSLT XML ); INSERT XML XMLDATA insert into XMLDATA (ID, XMLDOC) values ( 1, <newinfo xmlns=" <!-- merged customer information --> <customerinfo xmlns=" xmlns:d=" Cid="1004"> <name>matt Foreman</name> <addr country="canada"> <street>1596 Baseline</street> <city>toronto</city> <prov-state>ontario</prov-state> 26 IBM i: SQL XML

35 <pcode-zip>m3z 5H9</pcode-zip> </addr > <phone type="work" > </phone> <h:phone xmlns:h=" type="home"> </h:phone> <d:assistant> <name>gopher Runner</name> <h:phone xmlns:h=" type="home"> </h:phone> </d:assistant> </customerinfo> </newinfo> ); XSLT XSLT XMLDATA XML XMLTRANS SELECT XML INSERT XMLTRANS insert into XMLTRANS (XSLID, XSLT) values ( 1, <xsl:stylesheet version="1.0" xmlns:xsl=" <xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/> <!-- keep comments --> <xsl:template match="comment()"> <xsl:copy> <xsl:apply-templates/> </xsl:copy> </xsl:template> <xsl:template match="*"> <!-- remove element prefix --> <xsl:element name="{local-name()}"> <!-- process attributes --> <xsl:for-each select="@*"> <!-- remove attribute prefix --> <xsl:attribute name="{local-name()}"> <xsl:value-of select="."/> </xsl:attribute> </xsl:for-each> <xsl:apply-templates/> </xsl:element> </xsl:template> </xsl:stylesheet> ) ; SELECT XSLT XML SELECT XSLTRANSFORM (XMLDOC USING XSLT ) FROM XMLDATA, XMLTRANS where ID = 1 and XSLID = 1 XSLTRANSFORM XSLT XML XML <?xml version="1.0" encoding="utf-8"?> <newinfo> <!-- merged customer information --> <customerinfo Cid="1004"> <name>matt Foreman</name> <addr country="canada"> <street>1596 Baseline</street> <city>toronto</city> <prov-state>ontario</prov-state> SQL XML 27

36 <pcode-zip>m3z 5H9</pcode-zip> </addr> <phone type="work"> </phone> <phone type="home"> </phone> <assistant> <name>gopher Runner</name> <phone type="home"> </phone> </assistant> </customerinfo> </newinfo> XSLT phone XSLT XSLT mynode XMLTRANS SELECT XML INSERT XMLTRANS insert into XMLTRANS (XSLID, XSLT) values ( 2, <xsl:stylesheet version="1.0" xmlns:xsl=" <xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/> <xsl:variable name ="mynode">phone</xsl:variable> <!-- keep comments --> <xsl:template match="comment()"> <xsl:copy> <xsl:apply-templates/> </xsl:copy> </xsl:template> <xsl:template xmlns:d=" xmlns:h=" match="*"> <xsl:choose> <!-- keep namespace prefix for node names $mynode --> <xsl:when test="local-name() = $mynode " > <xsl:element name="{name()}"> <!-- process node attributes --> <xsl:for-each select="@*"> <!-- remove attribute prefix --> <xsl:attribute name="{local-name()}"> <xsl:value-of select="."/> </xsl:attribute> </xsl:for-each> <xsl:apply-templates/> </xsl:element> </xsl:when> <!-- remove namespace prefix from node --> <xsl:otherwise> <xsl:element name="{local-name()}"> <!-- process node attributes --> <xsl:for-each select="@*"> <!-- remove attribute prefix --> <xsl:attribute name="{local-name()}"> <xsl:value-of select="."/> </xsl:attribute> </xsl:for-each> <xsl:apply-templates/> </xsl:element> </xsl:otherwise> </xsl:choose> </xsl:template> </xsl:stylesheet> ); 28 IBM i: SQL XML

37 SELECT XSLID = 2 2 XSLT XML SELECT XSLTRANSFORM (XMLDOC USING XSLT) FROM XMLDATA, XMLTRANS where ID = 1 and XSLID = 2; XSLTRANSFORM 2 XSLT XML phone XML <?xml version="1.0" encoding="utf-8"?> <newinfo> <!-- merged customer information --> <customerinfo Cid="1004"> <name>matt Foreman</name> <addr country="canada"> <street>1596 Baseline</street> <city>toronto</city> <prov-state>ontario</prov-state> <pcode-zip>m3z 5H9</pcode-zip> </addr> <phone type="work"> </phone> <h:phone xmlns:h=" type="home"> </h:phone> <assistant> <name>gopher Runner</name> <h:phone xmlns:h=" type="home"> </h:phone> </assistant> </customerinfo> </newinfo> XML XSLTRANSFORM XML XML v XML 1 v XSLT UTF-8 Unicode v W3C XSLT 1.10 v SQL v (xsl:include ) SQL/XML SQL/XML SQL XML XML SQL XML 29

38 1. < < > > & & " " SQL/XML SQL XML SQL ID QName SQL XML SQL ID XML QName SQL ID QName SQL ID QName QName SQL QName QName XMLELEMENT SQL ID XMLFOREST XMLATTRIBUTES AS SQL ID QName W3C XML namespace specifications XML XML XML DB2 DB2 XMLSERIALIZE XML XML XML XML DB2 XML XML () XMLSERIALIZE XML BLOB 30 IBM i: SQL XML

39 XML DB2 CLI SQL XML DB2.NET DB2 XML Java SQLXML SQLXML DB2 XML : C ID 1000 customerinfo XML UTF-8 XML EXEC SQL BEGIN DECLARE SECTION; SQL TYPE IS XML AS BLOB (1M) xmlcustinfo; EXEC SQL END DECLARE SECTION;... EXEC SQL SELECT INFO INTO :xmlcustinfo FROM Customer WHERE Cid=1000; XML XMLSERIALIZE XML XMLSERIALIZE v SQL v (EXCLUDING XMLDECLARATION INCLUDING XMLDECLARATION) XMLSERIALIZE Unicode XML XML XML XMLSERIALIZE XML XML DB2 CLOB DBCLOB XMLSERIALIZE : Customer XML Info <customerinfo xml:space="default" xmlns=" Cid= 1000 > <name>kathy Smith</name> <addr country= Canada > SQL XML 31

40 <street>5 Rosewood</street> <city>toronto</city> <prov-state>ontario</prov-state> <pcode-zip>m6w 1E6</pcode-zip> </addr> <phone type= work > </phone> </customerinfo> XMLSERIALIZE BLOB SELECT XMLSERIALIZE(Info as BLOB(1M)) into :hostvar from Customer WHERE CID=1000 XML XML DB2 XML SQL/XML v XMLVALIDATE v XML (U+000D U+000A) (U+000D) (U+000A) XML 1.0 (U+000A) (U+0020) XML v XML XML DB2 CLI SQL XML DB2.NET DB2 XML Java SQLXML SQLXML DB2 XML XMLSERIALIZE INCLUDING XMLDECLARATION DB2 XML v XML Unicode & U+0026 & U+003C < U+003E > 32 IBM i: SQL XML

41 v (U+0022) XML " v DTD DTD v CDATA XML XML XML XML XML BLOB CCSID XMLTABLE XML XMLTABLE XML SQL XML EMP CREATE TABLE EMP (DOC XML) 2 <dept bldg="101"> <employee id="901"> <name> <first>john</first> <last>doe</last> </name> <office>344</office> <salary currency="usd">55000</salary> </employee> <employee id="902"> <name> <first>peter</first> <last>pan</last> </name> <office>216</office> <phone> </phone> </employee> </dept> <dept bldg="114"> <employee id="903"> <name> <first>mary</first> <last>jones</last> </name> <office>415</office> <phone> </phone> <phone> </phone> <salary currency="usd">64000</salary> </employee> </dept> XMLTABLE XPath COLUMNS 1 XPath $d/dept/employee $d emp XML doc SELECT X.* FROM emp, XMLTABLE ( $d/dept/employee PASSING emp.doc AS "d" SQL XML 33

42 COLUMNS empid INTEGER firstname VARCHAR(20) PATH name/first, lastname VARCHAR(25) PATH name/last ) AS X XML XML 1 employee () XMLTABLE employee 1 XPath SELECT COLUMNS XML SQL empid firstnamelastname 3 IntegerVarchar(20) Varchar(25) XPath employee SQL name/first firstname employee XMLTABLE EMPID FIRSTNAME LASTNAME John Doe 902 Peter Pan 903 Mary Jones COLUMNS 1 2 XMLTABLE XML 1 SQL : XMLTABLE XML Peter Pan salary XMLTABLE NULL XMLTABLE salary SELECT X.* FROM emp, XMLTABLE ( $d/dept/employee PASSING doc AS "d" COLUMNS empid INTEGER firstname VARCHAR(20) PATH name/first, lastname VARCHAR(25) PATH name/last, salary INTEGER PATH salary ) AS X XML salary Peter Pan salary NULL EMPID FIRSTNAME LASTNAME SALARY John Doe Peter Pan Mary Jones NULL NULL 0 salary 34 IBM i: SQL XML

43 SELECT X.* FROM emp, XMLTABLE ( $d/dept/employee PASSING doc AS "d" COLUMNS empid INTEGER firstname VARCHAR(20) PATH name/first, lastname VARCHAR(25) PATH name/last, salary INTEGER DEFAULT 0 PATH salary ) AS X : XMLTABLE 1 WHERE XMLTABLE 114 XMLTABLE SELECT X.* FROM emp, XMLTABLE ( $d/dept[@bldg="114"]/employee PASSING doc AS "d" COLUMNS empid INTEGER firstname VARCHAR(20) PATH name/first, lastname VARCHAR(25) PATH name/last, salary INTEGER DEFAULT 0 PATH salary ) AS X 114 Mary Jones : XMLTABLE COLUMNS 1 Mary Jones 2 SELECT X.* FROM emp, XMLTABLE ( $d/dept/employee PASSING doc AS "d" COLUMNS firstname VARCHAR(20) PATH name/first, lastname VARCHAR(25) PATH name/last, phone VARCHAR(12) PATH phone ) AS X phone phone 1 XPath phone SQL XML 35

44 XPath phone [1] [fn:position()=1] [1] 2 SELECT X.* FROM emp, XMLTABLE ( $d/dept/employee PASSING doc AS "d" COLUMNS firstname VARCHAR(20) PATH name/first, lastname VARCHAR(25) PATH name/last, phone VARCHAR(12) PATH phone[1] ) AS X XML 1 phone XML phone XML XML XPath SELECT X.* FROM emp, XMLTABLE ( $d/dept/employee PASSING doc AS "d" COLUMNS firstname VARCHAR(20) PATH name/first, lastname VARCHAR(25) PATH name/last, phone XML PATH phone ) AS X FIRSTNAME LASTNAME PHONE John Doe - Peter Pan <phone> </phone> Mary Jones <phone> </phone><phone> </phone> Mary Jones phone XML XML DB2 XML XML VARCHAR XML phone VARCHAR 2 SELECT X.* FROM emp, XMLTABLE ( $d/dept/employee PASSING doc AS "d" COLUMNS firstname VARCHAR(20) PATH name/first, lastname VARCHAR(25) PATH name/last, phone VARCHAR(12) PATH phone[1], phone2 VARCHAR(12) PATH phone[2] ) AS X NULL IBM i: SQL XML

45 XMLTABLE 1 1 SELECT X.* FROM emp, XMLTABLE ( $d/dept/employee/phone PASSING doc AS "d" COLUMNS firstname VARCHAR(20) PATH../name/first, lastname VARCHAR(25) PATH../name/last, phone VARCHAR(12) PATH. ) AS X FIRSTNAME LASTNAME PHONE Peter Pan Mary Jones Mary Jones John Doe Xquery phone John Doe phone John Doe John Doe employee 2 XMLTABLE SQL UNION SELECT X.* FROM emp, XMLTABLE ( $d/dept/employee/phone PASSING doc AS "d" COLUMNS firstname VARCHAR(20) PATH../name/first, lastname VARCHAR(25) PATH../name/last, phone VARCHAR(12) PATH. ) AS X UNION SELECT Y.*, CAST(NULL AS VARCHAR(12)) FROM emp, XMLTABLE ( $d/dept/employee[fn:not(phone)] PASSING doc AS "d" COLUMNS firstname VARCHAR(20) PATH name/first, lastname VARCHAR(25) PATH name/last ) AS Y 2 XMLTABLE $d/dept/employee[fn:not(phone)] XMLTABLE employee : XMLTABLE XML XML W3C XML XML XML xmlns XML Universal Resource Identifier (URI) URI ID ID URL Web SQL XML 37

46 xmlns:ibm = " xmlns = " XMLTABLE 3 <dept bldg="101"> <employee id="901"> <name> <first>john</first> <last>doe</last> </name> <office>344</office> <salary currency="usd">55000</salary> </employee> <employee id="902"> <name> <first>peter</first> <last>pan</last> </name> <office>216</office> <phone> </phone> </employee> </dept> <dept bldg="114"> <employee id="903"> <name> <first>mary</first> <last>jones</last> </name> <office>415</office> <phone> </phone> <phone> </phone> <salary currency="usd">64000</salary> </employee> </dept> <ibm:dept bldg="123" xmlns:ibm=" <ibm:employee id="144"> <ibm:name> <ibm:first>james</ibm:first> <ibm:last>bond</ibm:last> </ibm:name> <ibm:office>007</ibm:office> <ibm:phone> </ibm:phone> <ibm:salary currency="usd">77007</ibm:salary> </ibm:employee> </ibm:dept> * (*) () SELECT X.* FROM emp, XMLTABLE ( $d/*:dept/*:employee PASSING doc AS "d" COLUMNS empid INTEGER firstname VARCHAR(20) PATH *:name/*:first, lastname VARCHAR(25) PATH *:name/*:last ) AS X 38 IBM i: SQL XML

47 EMPID FIRSTNAME LASTNAME John Doe 902 Peter Pan 903 Mary Jones 144 James James XPath SELECT X.* FROM emp, XMLTABLE ( declare default element namespace " $d/dept/employee PASSING doc AS "d" COLUMNS empid INTEGER firstname VARCHAR(20) PATH declare default element namespace " name/first, lastname VARCHAR(25) PATH declare default element namespace " name/last ) AS X EMPID FIRSTNAME LASTNAME James Bond XPath 1 XMLTABLE XMLNAMESPACES() XMLTABLE XMLNAMESPACES XMLTABLE XPath XMLNAMESPACES XMLTABLE XPath 1 SELECT X.* FROM emp, XMLTABLE (XMLNAMESPACES(DEFAULT ), $d/dept/employee PASSING doc AS "d" COLUMNS empid INTEGER firstname VARCHAR(20) PATH name/first, lastname VARCHAR(25) PATH name/last ) AS X SQL XML 39

48 XMLNAMESPACES XMLNAMESPACES XMLNAMESPACES SELECT X.* FROM emp, XMLTABLE (XMLNAMESPACES( AS "ibm"), $d/ibm:dept/ibm:employee PASSING doc AS "d" COLUMNS empid INTEGER firstname VARCHAR(20) PATH ibm:name/ibm:first, lastname VARCHAR(25) PATH ibm:name/ibm:last ) AS X : XMLTABLE XMLTABLE FOR ORDINALITY XMLTABLE 1 SELECT X.* FROM emp, XMLTABLE ( $d/dept/employee PASSING doc AS "d" COLUMNS seqno FOR ORDINALITY, empid INTEGER firstname VARCHAR(20) PATH name/first, lastname VARCHAR(25) PATH name/last ) AS X SEQNO EMPID FIRSTNAME LASTNAME John Doe Peter Pan Mary Jones XML XML SQL UPDATE WHERE XML XML XML XML XML XML XMLVALIDATE XML XML Customer MyCustomer ID 1004 MyCustomer XML c7.xml <customerinfo xmlns=" Cid="1004"> <name>christine Haas</name> <addr country="canada"> <street>12 Topgrove</street> <city>toronto</city> 40 IBM i: SQL XML

49 <prov-state>ontario</prov-state> <pcode-zip>n9y-8g9</pcode-zip> </addr> <phone type="work"> </phone> <phone type="home"> </phone> </customerinfo> : JDBC XML c7.xml XML PreparedStatement updatestmt = null; String sqls = null; int cid = 1004; sqls = "UPDATE MyCustomer SET Info=? WHERE Cid=?"; updatestmt = conn.preparestatement(sqls); updatestmt.setint(1, cid); File file = new File("c7.xml"); updatestmt.setbinarystream(2, new FileInputStream(file), (int)file.length()); updatestmt.executeupdate(); : C XML XML EXEC SQL BEGIN DECLARE SECTION; sqlint64 cid; SQL TYPE IS XML AS BLOB (10K) xml_hostvar; EXEC SQL END DECLARE SECTION;... cid=1004; /* Read data from file c7.xml into xml_hostvar */... EXEC SQL UPDATE MyCustomer SET Info=:xml_hostvar WHERE Cid=:cid; <customerinfo> Cid Cid UPDATE WHERE Cid XML XML SQL DELETE WHERE XML NULL XML XML XML NULL SET NULL UPDATE NULL XML XML Customer MyCustomer MyCustomer Customer : MyCustomer Cid 1002 DELETE FROM MyCustomer WHERE Cid=1002 XML XML (XSR) XML XML XML URI (Uniform Resource Identifier) URI DB2 SQL XML 41

50 XML XSR URI XML XSR XML XML XML DB2 XSR DB2 XML DB2 SYSPROC.XSR_REGISTER XML XML XML CALL SYSPROC.XSR_REGISTER ( user1, POschema, :content_host_var, NULL) SYSPROC.XSR_ADDSCHEMADOC XML XML SYSPROC.XSR_ADDSCHEMADOC XML CALL SYSPROC.XSR_ADDSCHEMADOC ( user1, POschema, :content_host_var, NULL) SYSPROC.XSR_COMPLETE XML CALL SYSPROC.XSR_COMPLETE ( user1, POschema, :schemaproperty_host_var, 0) XML DB2 XML XML XML XML DB2 XML XML v SYSPROC.XSR_REMOVE v DROP XSROBJECT SQL XML (XSR) ASP (ASP) ASP XML XML ASP ASP ASP ASP XML XML DB2 XML 42 IBM i: SQL XML

51 v ILE RPG v ILE COBOL v C C++ ( SQL DB2 CLI) v Java (JDBC SQLJ) XML XML DB2 XML CCSID XML XML DB2 CCSID XML XML XML CCSID CCSID XML CCSID XML CCSID CCSID XML CLI XML XML XML SQLBindParameter() SQL XML C v SQL_C_CHAR v SQL_VARCHAR v SQL_C_WCHAR v SQL_VARGRAPHIC LOB v SQL_C_CLOB v SQL_C_CLOB_LOCATOR v SQL_C_BINARY v SQL_C_BLOB v SQL_C_BLOB_LOCATOR v SQL_C_BINARY SQL XML 43

52 XML DB2 CLI XML : XML XML SQL_C_CHAR SQL_C_WCHAR XML DB2 CLI XML DB2 CLI v C SQL_C_WCHAR DB2 CLI UTF-16 v C SQL_C_CHAR DB2 CLI 1 CCSID XML SQLBindParameter() SQL_XML SQL_C_BINARY XML XML char xmlbuffer[10240]; integer length; // Assume a table named dept has been created with the following statement: // CREATE TABLE dept (id CHAR(8), deptdoc XML) // xmlbuffer contains an internally encoded XML document that is to replace // the existing XML document length = strlen (xmlbuffer); SQLPrepare (hstmt, "UPDATE dept SET deptdoc =? WHERE id = &apos;001&apos;", SQL_NTS); SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_XML, 0, 0, xmlbuffer, 10240, &length); SQLExecute (hstmt); CLI XML XML XML SQLBindCol() C LOB XML CCSID XML CCSID CCSID XML DB2 CLI v C SQL_C_BINARY DB2 CLI XML v C SQL_C_CHAR DB2 CLI 44 IBM i: SQL XML

53 v C SQL_C_WCHAR DB2 CLI UTF-16 XMLSERIALIZE XML XMLSERIALIZE XML XML char xmlbuffer[10240]; // xmlbuffer is used to hold the retrieved XML document integer length; // Assume a table named dept has been created with the following statement: // CREATE TABLE dept (id CHAR(8), deptdoc XML) length = sizeof (xmlbuffer); SQLExecute (hstmt, "SELECT deptdoc FROM dept WHERE id=&apos;001&apos;", SQL_NTS); SQLBindCol (hstmt, 1, SQL_C_BINARY, xmlbuffer, &length, NULL); SQLFetch (hstmt); SQLCloseCursor (hstmt); // xmlbuffer now contains a valid XML document encoded in UTF-8 SQL XML SQL XML XML XML XML SQL_TYP_XML SQLTYPE XML SQL SQL/XML XML XML XML XML XML XML XML Unicode Unicode (BOM) BOM Unicode XML CHARVARCHARCLOBDBCLOBBLOB XML XML XMLPARSE SQL XML XML LOB C SQL XML 45

54 v SQL TYPE IS XML AS CLOB(n) <hostvar_name> SQL_XML_DATA_CCSID QAQQINI CCSID XML CLOB v SQL TYPE IS XML AS DBCLOB(n) <hostvar_name> XML DBCLOB UCS-2 UTF-16 SQL_XML_DATA_CCSID QAQQINI CCSID CCSID UTF-16 v SQL TYPE IS XML AS BLOB(n) <hostvar_name> XML BLOB v SQL TYPE IS XML AS LOCATOR <hostvar_name> XML v SQL TYPE IS XML AS CLOB_FILE <hostvar_name> CCSID XML CLOB v SQL TYPE IS XML AS DBCLOB_FILE <hostvar_name> 2 CCSID XML DBCLOB v SQL TYPE IS XML AS BLOB_FILE <hostvar_name> XML BLOB : SQL XML : C COBOL XML C SQL EXEC SQL BEGIN DECLARE; SQL TYPE IS XML AS CLOB( 10K ) xmlbuf; SQL TYPE IS XML AS BLOB( 10K ) xmlblob; SQL TYPE IS CLOB( 10K ) clobbuf; EXEC SQL END DECLARE SECTION; // using XML AS CLOB host variable // The XML value written to xmlbuf will be prefixed by an XML declaration // similar to: <?xml version = "1.0" encoding = "UTF-8"?> // Note: The encoding name will depend upon the SQL_XML_DATA_CCSID QAQQINI setting EXEC SQL SELECT xmlcol INTO :xmlbuf FROM mytable WHERE id = 001 ; EXEC SQL UPDATE mytable SET xmlcol = :xmlbuf WHERE id = 001 ; // using XML AS BLOB host variable // The XML value written to xmlblob will be prefixed by an XML declaration // similar to: <?xml version = "1.0" encoding = "UTF-8"?> EXEC SQL SELECT xmlcol INTO :xmlblob FROM mytable WHERE id = 001 ; EXEC SQL UPDATE mytable SET xmlcol = :xmlblob WHERE id = 001 ; // using CLOB host variable // The output will be encoded in the application single byte default CCSID, // but will not contain an XML declaration EXEC SQL SELECT XMLSERIALIZE (xmlcol AS CLOB(10K)) INTO :clobbuf FROM mytable 46 IBM i: SQL XML

55 WHERE id = 001 ; EXEC SQL UPDATE mytable SET xmlcol = XMLPARSE (:clobbuf PRESERVE WHITESPACE) WHERE id = 001 ; COBOL SQL EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 xmlbuf USAGE IS SQL TYPE IS XML as CLOB(5K). 01 clobbuf USAGE IS SQL TYPE IS CLOB(5K). 01 xmlblob USAGE IS SQL TYPE IS XML AS BLOB(5K). EXEC SQL END DECLARE SECTION END-EXEC. * using XML AS CLOB host variable EXEC SQL SELECT xmlcol INTO :xmlbuf FROM mytable WHERE id = 001. EXEC SQL UPDATE mytable SET xmlcol = :xmlbuf WHERE id = 001. * using XML AS BLOB host variable EXEC SQL SELECT xmlcol INTO :xmlblob FROM mytable WHERE id = 001. EXEC SQL UPDATE mytable SET xmlcol = :xmlblob WHERE id = 001. * using CLOB host variable EXEC SQL SELECT XMLSERIALIZE(xmlCol AS CLOB(10K)) INTO :clobbuf FROM mytable WHERE id= 001. EXEC SQL UPDATE mytable SET xmlcol = XMLPARSE(:clobBuf) PRESERVE WHITESPACE WHERE id = 001. XML SQL : SQL XML v XML v XML 2GB v XML ( ) v XML CCSID v XML LOB v SQL XML CHARVARCHARCLOBDBCLOB BLOB ('STRIP WHITESPACE') XMLPARSE XML SQL XML 47

56 SQLDA XML : XML SQLVAR sqlname v sqlname.length 8 v sqlname.data 2 X'0000' v sqlname.data 3 4 X'0000' v sqlname.data 5 X'01' ( 2 XML ) v X'000000' SQLTYPE LOB SQLVAR XML SQL0804 : SQL_TYP_XML DESCRIBE SQLDA sqlname XML Java Java XML JDBC XML : JDBC XML XML XML XML XML JDBC v setxxx XML XML v getxxx XML XML JDBC 4.0 java.sql.sqlxml XML ResultSetMetaData.getColumnTypeName XML java.sql.types.sqlxml JDBC XML : XML JDBC XML 2. XML PreparedStatement.setAsciiStream InputStream 48 IBM i: SQL XML

57 2. XML () PreparedStatement.setBinaryStream InputStream PreparedStatement.setBlob Blob PreparedStatement.setBytes byte[] PreparedStatement.setCharacterStream Reader PreparedStatement.setClob Clob PreparedStatement.setObject byte[]blobclobsqlxmlinputstream ReaderString PreparedStatement.setString String XML () () XML XML Java Unicode XML XML CCSID XML CCSID SQLXML XML String public void insertsqlxml() { Connection con = DriverManager.getConnection(url); SQLXML info = con.createsqlxml; // Create an SQLXML object PreparedStatement insertstmt = null; String infodata = "<customerinfo xmlns="" " + "Cid=""1000"" xmlns="" cid.setstring(ciddata); // Populate the SQLXML object int cid = 1000; try { sqls = "INSERT INTO CUSTOMER (CID, INFO) VALUES (?,?)"; insertstmt = con.preparestatement(sqls); insertstmt.setint(1, cid); insertstmt.setsqlxml(2, info); // Assign the SQLXML object value // to an input parameter if (insertstmt.executeupdate()!= 1) { System.out.println("insertSQLXML: No record inserted."); } } catch (IOException ioe) { SQL XML 49

58 ioe.printstacktrace(); } catch (SQLException sqle) { System.out.println("insertSQLXML: SQL Exception: " + sqle.getmessage()); System.out.println("insertSQLXML: SQL State: " + sqle.getsqlstate()); System.out.println("insertSQLXML: SQL Error Code: " + sqle.geterrorcode()); } } XML public void insertbinstream() { PreparedStatement insertstmt = null; String sqls = null; int cid = 0; ResultSet rs=null; Statement stmt=null; try { sqls = "INSERT INTO CUSTOMER (CID, INFO) VALUES (?,?)"; insertstmt = conn.preparestatement(sqls); insertstmt.setint(1, cid); File file = new File(fn); insertstmt.setbinarystream(2, new FileInputStream(file), (int)file.length()); if (insertstmt.executeupdate()!= 1) { System.out.println("insertBinStream: No record inserted."); } } catch (IOException ioe) { ioe.printstacktrace(); } catch (SQLException sqle) { System.out.println("insertBinStream: SQL Exception: " + sqle.getmessage()); System.out.println("insertBinStream: SQL State: " + sqle.getsqlstate()); System.out.println("insertBinStream: SQL Error Code: " + sqle.geterrorcode()); } } JDBC XML : JDBC ResultSet.getXXX ResultSet.getObject XML DB2 XML XML v ResultSet.getSQLXML SQLXML.getXXX SQLXML.getBinaryStream XML SQLXML.getString SQLXML.getCharacterStream XML v ResultSet.getObject ResultSet.getXXX 50 IBM i: SQL XML

59 XML ResultSet 3. XML ResultSet ResultSet.getAsciiStream ResultSet.getBinaryStream ResultSet.getBytes ResultSet.getCharacterStream ResultSet.getSQLXML ResultSet.getString InputStream InputStream byte[] Reader SQLXML String java.sql.sqlxml XML 4. SQLXML DB2Xml XML SQLXML.getBinaryStream InputStream XML CCSID SQLXML.getCharacterStream Reader SQLXML.getSource SQLXML.getString String XMLSERIALIZE XML XMLSERIALIZE SQLXML XML SQLXML.getString public void fetchtosqlxml() { System.out.println(">> fetchtosqlxml: Get XML data as an SQLXML object " + "using getsqlxml"); PreparedStatement selectstmt = null; String sqls = null, stringdoc = null; ResultSet rs = null; try{ sqls = "SELECT info FROM customer WHERE cid ="+ cid; selectstmt = conn.preparestatement(sqls); rs = selectstmt.executequery(); // Get metadata // Column type for XML column is the integer java.sql.types.other ResultSetMetaData meta = rs.getmetadata(); String coltype = meta.getcolumntype(1); System.out.println("fetchToSQLXML: Column type ="+ coltype); while (rs.next()) { // Retrieve the XML data with getsqlxml. // Then write it to a string with // explicit internal ISO UCS-2 encoding. java.sql.sqlxml xml = rs.getsqlxml(1); SQL XML 51

60 } System.out.println (xml.getstring()); } rs.close(); } catch (SQLException sqle) { System.out.println("fetchToSQLXML: SQL Exception: " + sqle.getmessage()); System.out.println("fetchToSQLXML: SQL State: " + sqle.getsqlstate()); System.out.println("fetchToSQLXML: SQL Error Code: " + sqle.geterrorcode()); } XML public void fetchtostring() { System.out.println(">> fetchtostring: Get XML data " + "using getstring"); PreparedStatement selectstmt = null; String sqls = null, stringdoc = null; ResultSet rs = null; try{ sqls = "SELECT info FROM customer WHERE cid ="+ cid; selectstmt = conn.preparestatement(sqls); rs = selectstmt.executequery(); // Get metadata // Column type for XML column is the integer java.sql.types.other ResultSetMetaData meta = rs.getmetadata(); String coltype = meta.getcolumntype(1); System.out.println("fetchToString: Column type ="+ coltype); } while (rs.next()) { stringdoc = rs.getstring(1); System.out.println("Document contents:"); System.out.println(stringDoc); } catch (SQLException sqle) { System.out.println("fetchToString: SQL Exception: " + sqle.getmessage()); System.out.println("fetchToString: SQL State: " + sqle.getsqlstate()); System.out.println("fetchToString: SQL Error Code: " + sqle.geterrorcode()); } Java XML : SQL XML SQL XML XML XML AS XML XML JDBC java.sql.sqlxml XML java.sql.types.sqlxml 52 IBM i: SQL XML

61 : 3 XML (IN OUT INOUT ) JDBC JDBC 4.0 java.sql.sqlxml in_xml = xmlvar; java.sql.sqlxml out_xml = null; java.sql.sqlxml inout_xml = xmlvar; // Declare an input, output, and // input/output XML parameter Connection con; CallableStatement cstmt; ResultSet rs;... stmt = con.preparecall("call SP_xml(?,?,?)"); // Create a CallableStatement object cstmt.setobject (1, in_xml); // Set input parameter cstmt.setobject (3, inout_xml); // Set inout parameter cstmt.registeroutparameter (2, java.sql.types.sqlxml); // Register out and input parameters cstmt.registeroutparameter (3, java.sql.types.sqlxml); cstmt.executeupdate(); // Call the stored procedure out_xml = cstmt.getsqlxml(2); // Get the OUT parameter value inout_xml = cstmt.getsqlxml(3); // Get the INOUT parameter value System.out.println("Parameter values from SP_xml call: "); System.out.println("Output parameter value "); MyUtilities.printString(out_xml.getString()); // Use the SQLXML.getString // method to convert the out_xml // value to a string for printing. // Call a user-defined method called // printstring (not shown) to print // the value. System.out.println("INOUT parameter value "); MyUtilities.printString(inout_xml.getString()); // Use the SQLXML.getString // method to convert the inout_xml // value to a string for printing. // Call a user-defined method called // printstring (not shown) to print // the value. : 3 XML (IN OUT INOUT ) SQLJ JDBC 4.0 java.sql.sqlxml in_xml = xmlvar; java.sql.sqlxml out_xml = null; java.sql.sqlxml inout_xml = xmlvar; // Declare an input, output, and // input/output XML parameter... #sql [myconnctx] {CALL SP_xml(:IN in_xml, :OUT out_xml, :INOUT inout_xml)}; // Call the stored procedure System.out.println("Parameter values from SP_xml call: "); System.out.println("Output parameter value "); MyUtilities.printString(out_xml.getString()); // Use the SQLXML.getString // method toconvert the out_xml value // to a string for printing. // Call a user-defined method called // printstring (not shown) to print // the value. System.out.println("INOUT parameter value "); MyUtilities.printString(inout_xml.getString()); // Use the SQLXML.getString // method to convert the inout_xml SQL XML 53

62 SQLJ XML : // value to a string for printing. // Call a user-defined method called // printstring (not shown) to print // the value. SQLJ XML XML DB2 XML XML XML SQLJ v INSERT UPDATE XML XML v SELECT XML XML JDBC 4.0 java.sql.sqlxml XML ResultSetMetaData.getColumnTypeName XML java.sql.types.sqlxml SQLJ XML : XML SQLJ XML v java.sql.sqlxml (SQLJ 4.0 ) v String v byte v Blob v Clob v sqlj.runtime.asciistream v sqlj.runtime.binarystream v sqlj.runtime.characterstream JDBC java.io.typeinputstream sqlj.runtime.typestream XML () () XML XML JVM Java Unicode 54 IBM i: SQL XML

63 XML XML CCSID String xmlstring XML xmlstring #sql [ctx] {INSERT INTO CUSTACC VALUES (1, :xmlstring)}; sqlj.runtime.characterstream : sqlj.runtime.characterstream sqlj.runtime.characterstream host XML java.io.stringreader xmlreader = new java.io.stringreader(xmlstring); sqlj.runtime.characterstream sqljxmlcharacterstream = new sqlj.runtime.characterstream(xmlreader, xmlstring.length()); #sql [ctx] {INSERT INTO CUSTACC VALUES (4, :sqljxmlcharacterstream)}; sqljxmlcharacterstream XML java.sql.sqlxml XML java.sql.resultset rs = s.executequery ("SELECT * FROM CUSTACC"); rs.next(); java.sql.sqlxml xmlobject = (java.sql.sqlxml)rs.getobject(2); #sql [ctx] {INSERT INTO CUSTACC VALUES (6, :xmlobject)}; UTF-8 XML SQLJ XML : SQLJ XML XML v java.sql.sqlxml (SQLJ 4.0) v String v byte[] v sqlj.runtime.asciistream v sqlj.runtime.binarystream v sqlj.runtime.characterstream XMLSERIALIZE UTF-8 XML java.sql.sqlxml com.ibm.db2.jcc.db2xml XML SQL XML 55

64 java.sql.sqlxml com.ibm.db2.jcc.db2xml XML 5. SQLXML DB2Xml XML SQLXML.getBinaryStream InputStream XML CCSID SQLXML.getCharacterStream Reader SQLXML.getSource SQLXML.getString String XMLSERIALIZE XML XMLSERIALIZE XML String #sql iterator XmlStringIter (int, String); #sql [ctx] siter = {SELECT C1, CADOC from CUSTACC}; #sql {FETCH :siter INTO :row, :outstring}; String UTF-8 XML XML byte[] #sql iterator XmlByteArrayIter (int, byte[]); XmlByteArrayIter biter = null; #sql [ctx] biter = {SELECT c1, CADOC from CUSTACC}; #sql {FETCH :biter INTO :row, :outbytes}; byte[] UTF-8 XML XML java.sql.sqlxml #sql iterator SqlXmlIter (int, java.sql.sqlxml); SqlXmlIter SQLXMLiter = null; java.sql.sqlxml outsqlxml = null; #sql [ctx] SqlXmlIter = {SELECT c1, CADOC from CUSTACC}; #sql {FETCH :SqlXmlIter INTO :row, :outsqlxml}; java.io.inputstream XmlStream = outsqlxml.getbinarystream(); FETCH UTF-8 SQLXML SQLXML.getBinaryStream XML 56 IBM i: SQL XML

65 SQL XML : SQL XML SQL SQL XML CREATE TABLE T1(C1 XML) ; CREATE PROCEDURE proc1(in parm1 XML, IN parm2 VARCHAR(32000)) LANGUAGE SQL BEGIN DECLARE var1 XML; END ; /* insert the value of parm1 into table T1 */ INSERT INTO T1 VALUES(parm1); /* parse parameter parm2 s value and assign it to a variable */ SET var1 = XMLPARSE(document parm2 preserve whitespace); /* insert variable var1 into table T1 INSERT INTO T1 VALUES(var1); 1 XML T1 SQL parm1 parm2 2 parm1 XML SQL XML var1 XMLPARSE parm2 XML var1 XML T1 C1 SQL XML XML : SQL XML SQL XML XML XML SQL SQL XML XML SQL ROLLBACK COMMIT XML XML XML : XML v ILE RPG SQL XML 57

66 v ILE COBOL v C v C++ v Java XML XML CREATE PROCEDURE CREATE FUNCTION XML XML XML CREATE PROCEDURE parm1 XML C CREATE PROCEDURE CREATE PROCEDURE myproc(in parm1 XML AS CLOB(2M), IN parm2 VARCHAR(32000)) LANGUAGE C FENCED PARAMETER STYLE SQL EXTERNAL NAME mylib.myproc ; UDF CREATE FUNCTION myfunc (IN parm1 XML AS CLOB(2M)) RETURNS SMALLINT LANGUAGE C PARAMETER STYLE SQL DETERMINISTIC NOT FENCED NULL CALL NO SQL NO EXTERNAL ACTION EXTERNAL NAME mylib1.myfunc XML XML : Java (JDBC) XML : Java JDBC (API) Java XML Java Java v JAVA CREATE PROCEDURE v JAVA v XML Java Java CREATE PROCEDURE Java Java 2 58 IBM i: SQL XML

67 Java stpclass.java myjar JAR Java using System; import java.lang.*; import java.io.*; import java.sql.*; import java.util.*; public class stpclass {... // Java procedure implementations... } JAR DB2 CREATE PROCEDURE EXTERNAL : XML JAVA v JAVA CREATE PROCEDURE v XML JAVA Java inxml SQL XQuery XML 2 outxml1 outxml2 XML JAVA CREATE PROCEDURE xmlproc1 ( IN innum INTEGER, IN inxml XML as CLOB (1K), OUT out1xml XML as CLOB (1K), OUT out2xml XML as CLOB (1K) ) DYNAMIC RESULT SETS 0 DETERMINISTIC LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA FENCED THREADSAFE DYNAMIC RESULT SETS 0 PROGRAM TYPE SUB NO DBINFO EXTERNAL NAME stpclass.xmlproc1 ; public void xmlproc1(int innum, CLOB inxml, CLOB [] out1xml, ) throws Exception { Connection con = DriverManager.getConnection("jdbc:default:connection"); // Insert data including the XML parameter value into a table String query = "INSERT INTO xmldatatable (num, inxml ) VALUES (?,? )" ; String xmlstring = inxml.getcharacterstream() ; SQL XML 59

68 stmt = con.preparestatement(query); stmt.setint(1, innum); stmt.setstring (2, xmlstring ); stmt.executeupdate(); stmt.close(); // Query and retrieve a single XML value from a table using SQL query = "SELECT xdata from xmldatatable WHERE num =?"; stmt = con.preparestatement(query); stmt.setint(1, innum); ResultSet rs = stmt.executequery(); if ( rs.next() ) { out1xml[0] = (CLOB) rs.getobject(1); } rs.close() ; stmt.close(); } return ; : C XML : C XML XML C XML XML C XML C SQL v SQL CREATE PROCEDURE v XML SQL C 2 innum INTEGER 2 inxml XML xmldatatable XML SQL XML out1xml CREATE PROCEDURE xmlproc1 ( IN innum INTEGER, IN inxml XML as CLOB (1K), OUT out1xml XML as CLOB (1K) ) LANGUAGE C PARAMETER STYLE SQL DYNAMIC RESULT SETS 0 DETERMINISTIC MODIFIES SQL DATA PROGRAM TYPE SUB EXTERNAL NAME xmlproc1 ; //************************************************************************* // Stored Procedure: xmlproc1 // // Purpose: insert XML data into XML column // // Parameters: // // IN: innum -- the sequence of XML data to be insert in xmldata table 60 IBM i: SQL XML

69 // inxml -- XML data to be inserted // OUT: out1xml -- XML data returned - value retrieved using SQL //************************************************************************* #include <stdio.h> #include <string.h> #include <stdlib.h> #include <sqlda.h> #include <sqlca.h> #include <sqludf.h> #include <sql.h> #include <memory.h> #ifdef cplusplus extern "C" #endif SQL_API_RC SQL_API_FN testseca1(sqlint32* innum, SQLUDF_CLOB* inxml, SQLUDF_CLOB* out1xml, SQLUDF_NULLIND *innum_ind, SQLUDF_NULLIND *inxml_ind, SQLUDF_NULLIND *out1xml_ind, SQLUDF_TRAIL_ARGS) { char *str; FILE *file; EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; sqlint32 hvnum1; SQL TYPE IS XML AS CLOB(200) hvxml1; SQL TYPE IS XML AS CLOB(200) hvxml2; EXEC SQL END DECLARE SECTION; /* Check null indicators for input parameters */ if ((*innum_ind < 0) (*inxml_ind < 0)) { strcpy(sqludf_sqlstate, "38100"); strcpy(sqludf_msgtext, "Received null input"); return 0; } /* Copy input parameters to host variables */ hvnum1 = *innum; hvxml1.length = inxml->length; strncpy(hvxml1.data, inxml->data, inxml->length); /* Execute SQL statement */ EXEC SQL INSERT INTO xmldatatable (num, xdata) VALUES (:hvnum1, :hvxml1); /* Execute SQL statement */ EXEC SQL SELECT xdata INTO :hvxml2 FROM xmldatatable WHERE num = :hvnum1; exit: /* Set output return code */ *outreturncode = sqlca.sqlcode; *outreturncode_ind = 0; return 0; } SQL XML 61

70 XML XML () () XML XML v XML XML CCSID v XML XML XML DB2 CCSID XML XML DB2 DB2 Java XML : XML DB2 v XML ( ) v XML ( ) XML : XML DB2 XML : DB2 XML XML 62 IBM i: SQL XML

71 XML SQL XML AS XML AS CCSID JDBC SQLJ XML : Java XML CLI SQL XML Java CCSID Unicode Java XML v (setbinarystream) v Java DB2 (setstring ) UTF-16 ( CCSID) XML CCSID Java XML v XML XML Unicode INCLUDING XMLDECLARATION XMLSERIALIZE JDBC v XML UTF-8UCS-2 UTF-16 XML XML () XML XML XML : XML XML SQL XML 63

72 1 CCSID UTF-8 Unicode (UTF-8 BOM XML ) 2 : INSERT INTO T1 (XMLCOL) VALUES (?) INSERT INTO T1 (XMLCOL) VALUES (XMLPARSE(DOCUMENT CAST(? AS BLOB) PRESERVE WHITESPACE)) : : : 2 CCSID UTF-16 BOM XML UTF-16 Unicode 2 : INSERT INTO T1 (XMLCOL) VALUES (?) INSERT INTO T1 (XMLCOL) VALUES (XMLPARSE(DOCUMENT CAST(? AS BLOB) PRESERVE WHITESPACE)) : DB2 XML UTF-8 XML UTF-16 UTF-8 : : 3 XML ISO IBM i: SQL XML

73 CCSID 2 : INSERT INTO T1 (XMLCOL) VALUES (?) INSERT INTO T1 (XMLCOL) VALUES (XMLPARSE(DOCUMENT CAST(? AS BLOB) PRESERVE WHITESPACE)) : DB2 XML UTF-8 XML CCSID 819 UTF-8 : : 4 CCSID XML Shift_JIS 2 : INSERT INTO T1 (XMLCOL) VALUES (?) INSERT INTO T1 (XMLCOL) VALUES (XMLPARSE(DOCUMENT CAST(? AS BLOB) PRESERVE WHITESPACE)) : DB2 XML UTF-8 XML CCSID 943 UTF-8 : : XML : XML XML CCSID 1 2 Java Java Unicode Java Unicode SQL XML 65

74 1 CCSID UTF-8 Unicode ( BOM ) 1208 (UTF-8) : INSERT INTO T1 (XMLCOL) VALUES (?) INSERT INTO T1 (XMLCOL) VALUES (XMLPARSE(DOCUMENT CAST(? AS CLOB) PRESERVE WHITESPACE)) : : : 2 CCSID UTF-16 Unicode ( BOM ) : INSERT INTO T1 (XMLCOL) VALUES (?) INSERT INTO T1 (XMLCOL) VALUES (XMLPARSE(DOCUMENT CAST(? AS DBCLOB) PRESERVE WHITESPACE)) : DB2 XML UTF-8 XML UTF-16 UTF-8 : : UTF-16 UTF-8 3 ISO () 66 IBM i: SQL XML

75 CCSID 819 : INSERT INTO T1 (XMLCOL) VALUES (?) INSERT INTO T1 (XMLCOL) VALUES (XMLPARSE(DOCUMENT CAST(? AS CLOB) PRESERVE WHITESPACE)) : DB2 XML UTF-8 XML CCSID 819 UTF-8 : : 4 CCSID Shift_JIS () 943 : INSERT INTO T1 VALUES (?) INSERT INTO T1 VALUES (XMLPARSE(DOCUMENT CAST(? AS DBCLOB))) : DB2 XML UTF-8 XML CCSID 943 UTF-8 : : XML : XML 1 2 Java Java Unicode Java Unicode SQL XML 67

76 1 CCSID UTF-8 Unicode 2 : SELECT XMLCOL FROM T1 : : : : Java XML <?xml version="1.0" encoding="utf-8"?> Java com.ibm.db2.jcc.db2xml getdb2xmlxxx getdb2xmlxxx 2 CCSID UTF-16 Unicode : SELECT XMLCOL FROM T1 : UTF-8 UTF-16 : : UTF-8 UTF-16 : Java.NET UTF-16 (BOM) XML 68 IBM i: SQL XML

77 <?xml version="1.0" encoding="utf-16"?> Java com.ibm.db2.jcc.db2xml getdb2xmlxxx getdb2xmlxxx 3 CCSID ISO : SELECT XMLCOL FROM T1 : UTF-8 CCSID 819 : CCSID 819 UTF-8 DB2 : : XML <?xml version="1.0" encoding="iso "?> 4 CCSID Windows-31J (Shift_JIS ) 943 : SELECT XMLCOL FROM T1 : UTF-8 CCSID 943 : CCSID 943 UTF-8 DB2 : UTF-8 CCSID 943 SQL XML 69

78 : XML <?xml version="1.0" encoding="windows-31j"?> XMLSERIALIZE XML : XMLSERIALIZE XML 1 2 Java.NET Java Unicode 1 CCSID UTF-8 Unicode 2 : SELECT XMLSERIALIZE(XMLCOL AS BLOB(1M) INCLUDING XMLDECLARATION) FROM T1 : : : : XML <?xml version="1.0" encoding="utf-8"?> 2 CCSID UTF-16 Unicode : SELECT XMLSERIALIZE(XMLCOL AS CLOB(1M) EXCLUDING XMLDECLARATION) FROM T1 : UTF-8 UTF IBM i: SQL XML

79 : : UTF-8 UTF-16 : EXCLUDING XMLDECLARATION INCLUDING XMLDECLARATION UTF-16 UTF-8 XML 3 CCSID ISO : SELECT XMLSERIALIZE(XMLCOL AS CLOB(1M) EXCLUDING XMLDECLARATION) FROM T1 : UTF-8 CCSID 819 : CCSID 819 UTF-8 CCSID 819 DB2 : : EXCLUDING XMLDECLARATION INCLUDING XMLDECLARATION ISO UTF-8 XML 4 CCSID Windows-31J (Shift_JIS ) 943 : SELECT XMLSERIALIZE(XMLCOL AS CLOB(1M) EXCLUDING XMLDECLARATION) FROM T1 SQL XML 71

80 : UTF-8 CCSID 943 : CCSID 943 UTF-8 CCSID 943 : UTF-8 CCSID 943 : EXCLUDING XMLDECLARATION INCLUDING XMLDECLARATION Windows-31J UTF-8 XML XML CCSID XML XML DB2 CCSID IANA CCSID QlgCvtTextDescToDesc API XML CCSID XMLSERIALIZE DB2 XML <?xml version="1.0" encoding="encoding-name"?> ID CCSID XML CCSID DB2 XML CCSID IANA CCSID IANA QlgCvtTextDescToDesc API XML XML XML XML XML XML SQL XML 1 XML XML XML v XML v SQL SQL v 72 IBM i: SQL XML

81 XML (XSR) XML XML XML XML XML 1 XML XML XML XML XML XML 1. XML XML XML XML XML 4. SYSPROC.XDBDECOMPXML XML XML XML v XML 1 XML XML 1. XSR_REGISTER 1 2. XML XSR_ADDSCHEMADOC 3. issuedfordecomposition 1 XSR_COMPLETE XML XML XML db2-xdb:contenthandling db2-xdb:expression XML v XML v XML <db2-xdb:contenthandling> <db2-xdb:contenthandling> text SQL XML 73

82 stringvalue serializedsubtree v db2-xdb:expression XML XML db2-xdb:expression XML XML XML XML XML XML "db2-xdb" XML <xs:annotation> XML XML XML XML db2-xdb:rowset db2-xdb:column 2 2 XML XML v v () 74 IBM i: SQL XML

83 db2-xdb:rowset db2-xdb:column <xs:element name="isbn" type="xs:string" db2-xdb:rowset="textbooks" db2-xdb:column="isbn"/> db2-xdb:rowset db2-xdb:column isbn XML <xs:annotation><xs:appinfo></xs:appinfo></xs:annotation> db2-xdb:rowset db2-xdb:column (<db2-xdb:rowsetmapping> ) <xs:element name="isbn" type="xs:string"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>textbooks</db2-xdb:rowset> <db2-xdb:column>isbn</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:element> db2-xdb:rowset db2-xdb:column 1 <db2-xdb:rowsetmapping> ( ) <xs:schema> XML XML <db2-xdb:defaultsqlschema> XML SQL <db2-xdb:defaultsqlschema> <xs:schema> <xs:schema> <xs:annotation> <xs:appinfo> <db2-xdb:defaultsqlschema>admin</db2-xdb:defaultsqlschema> </xs:appinfo> </xs:annotation>... </xs:schema> XML admin SQL SQL XML 75

84 XML - DB2 XML XML XML 6. SQL SQL SQL SQL XML db2-xdb:defaultsqlschema db2-xdb:table (<db2-xdb:sqlschema> ) 7. XML XML XML 1 1 db2-xdb:column db2-xdb:rowset db2-xdb:rowsetmapping 1 1 db2-xdb:rowsetmapping 1 db2-xdb:table db2-xdb:rowsetoperationorder, db2-xdb:rowset, db2-xdb:order 8. XML () XML db2-xdb:contenthandling db2-xdb:normalizationdb2-xdb:expressiondb2-xdb:truncate db2-xdb:condition db2-xdb:locationpath db2-xdb:defaultsqlschema db2-xdb:defaultsqlschema SQL db2-xdb:table XML <xs:annotation> <xs:appinfo> db2-xdb:defaultsqlschema (value ) <xs:schema> <xs:annotation> <xs:appinfo> 76 IBM i: SQL XML

85 <db2-xdb:defaultsqlschema>value</db2-xdb:defaultsqlschema> </xs:appinfo> </xs:annotation>... </xs:schema> SQL SQL () SQL / SQL ( SQL ID ) <& SQL XML SQL 2 1 <db2-xdb:table> <db2-xdb:sqlschema> 1 <db2-xdb:defaultsqlschema> <db2-xdb:defaultsqlschema> SQL () SQL ID admin SQL <xs:schema xmlns:xs=" xmlns:db2-xdb=" <xs:annotation> <xs:appinfo> <db2-xdb:defaultsqlschema>admin</db2-xdb:defaultsqlschema> </xs:appinfo> </xs:annotation>... </xs:schema> SQL ID admin schema SQL admin schema <xs:schema xmlns:xs=" xmlns:db2-xdb=" <xs:annotation> <xs:appinfo> <db2-xdb:defaultsqlschema>"admin schema"</db2-xdb:defaultsqlschema> </xs:appinfo> </xs:annotation>... </xs:schema> db2-xdb:rowset db2-xdb:rowset XML SQL XML 77

86 <xs:element> <xs:attribute> < db2-xdb:rowsetmapping> <db2-xdb:order> db2-xdb:rowset (value ) v <xs:element db2-xdb:rowset="value" /> v <xs:attribute db2-xdb:rowset="value" /> v <db2-xdb:rowsetmapping> <db2-xdb:rowset> value</db2-xdb:rowset>... </db2-xdb:rowsetmapping> v <db2-xdb:order> <db2-xdb:rowset> value</db2-xdb:rowset>... </db2-xdb:order> SQL ID ID ID db2-xdb:rowset XML rowset ( rowset db2-xdb:table ) rowset () rowset XML XML v db2-xdb:rowset <db2-xdb:table> <db2-xdb:rowset> <db2-xdb:defaultsqlschema> SQL 1 db2-xdb:rowset v db2-xdb:rowset <db2-xdb:table> <db2-xdb:rowset> 1 <db2-xdb:table> <db2-xdb:name> () db2-xdb:rowset : XML XML (XML db2-xdb:column 78 IBM i: SQL XML

87 ) XML <db2-xdb:table> db2-xdb:rowset db2-xdb:column db2-xdb:condition db2-xdb:rowset db2-xdb:column db2-xdb:rowset db2-xdb:condition rowset true ( <db2-xdb:table> ) db2-xdb:rowset 2 1 BOOKCONTENTS <db2-xdb:defaultsqlschema> SQL BOOKCONTENTS <db2-xdb:rowset> <db2-xdb:table> <xs:element name="book"> <xs:complextype> <xs:sequence> <xs:element name="authorid" type="xs:integer" /> <xs:element name="chapter" type="chaptertype" maxoccurs="unbounded" /> </xs:sequence> <xs:attribute name="isbn" type="xs:string" db2-xdb:rowset="bookcontents" db2-xdb:column="isbn" /> <xs:attribute name="title" type="xs:string" /> </xs:complextype> </xs:element> <xs:complextype name="chaptertype"> <xs:sequence> <xs:element name="paragraph" type="paragraphtype" maxoccurs="unbounded" db2-xdb:rowset="bookcontents" db2-xdb:column="chptcontent" /> </xs:sequence> <xs:attribute name="number" type="xs:integer" db2-xdb:rowset="bookcontents" db2-xdb:column="chptnum" /> <xs:attribute name="title" type="xs:string" db2-xdb:rowset="bookcontents" db2-xdb:column="chpttitle" /> </xs:complextype> <xs:simpletype name="paragraphtype"> <xs:restriction base="xs:string"/> </xs:simpletype> XML <book isbn=" " title="my First XML Book"> <authorid>22</authorid> <!-- this book does not have a preface --> <chapter number="1" title="introduction to XML"> <paragraph>xml is fun...</paragraph>... </chapter> <chapter number="2" title="xml and Databases"> <paragraph>xml can be used with...</paragraph> </chapter>... <chapter number="10" title="further Reading"> SQL XML 79

88 <paragraph>recommended tutorials...</paragraph> </chapter>... </book> BOOKCONTENTS 9. BOOKCONTENTS ISBN CHPTNUM CHPTTITLE CHPTCONTENT Introduction to XML XML is fun XML and Databases XML can be used with Further Reading Recommended tutorials... db2-xdb:rowset <db2-xdb:table> <db2-xdb:rowset> <db2-xdb:table> ALLBOOKS <db2-xdb:defaultsqlschema> SQL <!-- global annotation --> <xs:annotation> <xs:appinfo> <db2-xdb:table> <db2-xdb:name>allbooks</db2-xdb:name> <db2-xdb:rowset>book</db2-xdb:rowset> <db2-xdb:rowset>textbook</db2-xdb:rowset> </db2-xdb:table> </xs:appinfo> </xs:annotation> <xs:element name="book"> <xs:complextype> <xs:sequence> <xs:element name="authorid" type="xs:integer" db2-xdb:rowset="book" db2-xdb:column="authorid" /> <xs:element name="chapter" type="chaptertype" maxoccurs="unbounded" /> </xs:sequence> <xs:attribute name="isbn" type="xs:string" db2-xdb:rowset="book" db2-xdb:column="isbn" /> <xs:attribute name="title" type="xs:string" db2-xdb:rowset="book" db2-xdb:column="title" /> </xs:complextype> </xs:element> <xs:element name="textbook"> <xs:complextype> <xs:sequence> <xs:element name="isbn" type="xs:string" db2-xdb:rowset="textbook" db2-xdb:column="isbn" /> <xs:element name="title" type="xs:string" db2-xdb:rowset="textbook" db2-xdb:column="title" /> <xs:element name="primaryauthorid" type="xs:integer" db2-xdb:rowset="textbook" db2-xdb:column="authorid" /> <xs:element name="coauthorid" type="xs:integer" minoccurs="0" maxoccurs="unbounded" /> <xs:element name="subject" type="xs:string" /> <xs:element name="edition" type="xs:integer" /> <xs:element name="chapter" type="chaptertype" maxoccurs="unbounded" /> </xs:sequence> </xs:complextype> 80 IBM i: SQL XML

89 </xs:element> <xs:complextype name="chaptertype"> <xs:sequence> <xs:element name="paragraph" type="paragraphtype" maxoccurs="unbounded" /> </xs:sequence> <xs:attribute name="number" type="xs:integer" /> <xs:attribute name="title" type="xs:string" /> </xs:complextype> <xs:simpletype name="paragraphtype"> <xs:restriction base="xs:string"/> </xs:simpletype> XML <book isbn=" " title="my First XML Book"> <authorid>22</authorid> <!-- this book does not have a preface --> <chapter number="1" title="introduction to XML"> <paragraph>xml is fun...</paragraph> </chapter> <chapter number="2" title="xml and Databases"> <paragraph>xml can be used with...</paragraph> </chapter> <chapter number="10" title="further Reading"> <paragraph>recommended tutorials...</paragraph> </chapter> </book> <textbook> <isbn> </isbn> <title>programming with XML</title> <primaryauthorid>435</primaryauthorid> <subject>programming</subject> <edition>4</edition> <chapter number="1" title="programming Basics"> <paragraph>before you being programming...</paragraph> </chapter> <chapter number="2" title="writing a Program"> <paragraph>now that you have learned the basics...</paragraph> </chapter>... <chapter number="10" title="advanced techniques"> <paragraph>you can apply advanced techniques...</paragraph> </chapter> </textbook> ALLBOOKS 2 v /book/@isbn, /book/@authorid, /book/title v /textbook/isbn, /textbook/primaryauthorid, /textbook/title rowset 10. ALLBOOKS ISBN TITLE AUTHORID My First XML Book Programming with XML 435 SQL XML 81

90 db2-xdb:table db2-xdb:table XML XML <db2-xdb:defaultsqlschema> SQL SQL <xs:appinfo> (<xs:annotation> ) db2-xdb:table <db2-xdb:sqlschema> () SQL <db2-xdb:name> <db2-xdb:sqlschema> <db2-xdb:defaultsqlschema> XML <db2-xdb:table> <db2-xdb:rowset> <db2-xdb:rowset> 1 <db2-xdb:name> <db2-xdb:rowset> 1 <db2-xdb:rowset> db2-xdb:column 1 XML 1 <db2-xdb:rowset> <db2-xdb:rowset> XML <db2-xdb:table> db2-xdb:table SQL ID / <& SQL ID db2-xdb:table v v <db2-xdb:defaultsqlschema> SQL 82 IBM i: SQL XML

91 db2-xdb:table 1 XML () <root>... <book isbn=" " title="my First XML Book"> <authorid>22</authorid> < >author22@any .com</ > <!-- this book does not have a preface --> <chapter number="1" title="introduction to XML"> <paragraph>xml is fun...</paragraph>... </chapter> <chapter number="2" title="xml and Databases"> <paragraph>xml can be used with...</paragraph> </chapter>... <chapter number="10" title="further Reading"> <paragraph>recommended tutorials...</paragraph> </chapter> </book>... <author ID="0800" ="author800@ .com"> <firstname>alexander</firstname> <lastname>smith</lastname> <activestatus>0</activestatus> </author>... <root> ID E AUTHORSCONTACT ID E <book> <author> <db2-xdb:table> <db2-xdb:table> <!-- global annotation --> <xs:annotation> <xs:appinfo> <db2-xdb:defaultsqlschema>adminschema</db2-xdb:defaultsqlschema> <db2-xdb:table> <db2-xdb:sqlschema>user1</db2-xdb:sqlschema> <db2-xdb:name>authorscontact</db2-xdb:name> <db2-xdb:rowset>bookrowset</db2-xdb:rowset> <db2-xdb:rowset>authorrowset</db2-xdb:rowset> </db2-xdb:table> </xs:appinfo> </xs:annotation> <xs:element name="book"> <xs:complextype> <xs:sequence> <xs:element name="authorid" type="xs:integer" db2-xdb:rowset="bookrowset" db2-xdb:column="authid" /> <xs:element name=" " type="xs:string" db2-xdb:rowset="bookrowset" db2-xdb:column=" addr" /> <xs:element name="chapter" type="chaptertype" maxoccurs="unbounded" /> </xs:sequence> <xs:attribute name="isbn" type="xs:string" /> <xs:attribute name="title" type="xs:string" /> SQL XML 83

92 </xs:complextype> </xs:element> <xs:element name="author"> <xs:complextype> <xs:sequence> <xs:element name="firstname" type="xs:string" /> <xs:element name="lastname" type="xs:string" /> <xs:element name="activestatus" type="xs:boolean" /> </xs:sequence> <xs:attribute name="id" type="xs:integer" db2-xdb:rowset="authorrowset" db2-xdb:column="authid" /> <xs:attribute name=" " type="xs:string" db2-xdb:rowset="authorrowset" db2-xdb:column=" addr" /> </xs:complextype> </xs:element> db2-xdb:table db2-xdb:name AUTHORSCONTACT <book> ID E <author> () <db2-xdb:rowset> <book> <author> rowsets AUTHORSCONTACT SQL SQL <db2-xdb:sqlschema> AUTHORSCONTACT 11. AUTHORSCONTACT AUTHID ADDR 22 author22@any .com 0800 author800@ .com db2-xdb:column db2-xdb:column XML XML <xs:element> <xs:attribute> <db2-xdb:rowsetmapping> db2-xdb:column (value ) v <xs:element db2-xdb:rowset="value" db2-xdb:column="value" /> v <xs:attribute db2-xdb:rowset="value" db2-xdb:column="value" /> v <db2-xdb:rowsetmapping> <db2-xdb:rowset>value</db2-xdb:rowset> <db2-xdb:column>value</db2-xdb:column>... </db2-xdb:rowsetmapping> 84 IBM i: SQL XML

93 v / v (") (&) (<) SQL ID XML " " & & < < v db2-xdb:locationpath db2-xdb:column db2-xdb:rowsetmapping db2-xdb:column <book> BOOKCONTENTS <xs:element name="book"> <xs:complextype> <xs:sequence> <xs:element name="authorid" type="xs:integer" /> <xs:element name="chapter" type="chaptertype" maxoccurs="unbounded" /> </xs:sequence> <xs:attribute name="isbn" type="xs:string" db2-xdb:rowset="bookcontents" db2-xdb:column="isbn" /> <xs:attribute name="title" type="xs:string" /> </xs:complextype> </xs:element> <xs:complextype name="chaptertype"> <xs:sequence> <xs:element name="paragraph" type="paragraphtype" maxoccurs="unbounded" db2-xdb:rowset="bookcontents" db2-xdb:column="chptcontent" /> </xs:sequence> <xs:attribute name="number" type="xs:integer" db2-xdb:rowset="bookcontents" db2-xdb:column="chptnum" /> <xs:attribute name="title" type="xs:string" db2-xdb:rowset="bookcontents" db2-xdb:column="chpttitle" /> </xs:complextype> <xs:simpletype name="paragraphtype"> <xs:restriction base="xs:string"/> </xs:simpletype> <book> BOOKCONTENTS <book isbn=" " title="my First XML Book"> <authorid>22</authorid> <!-- this book does not have a preface --> <chapter number="1" title="introduction to XML"> <paragraph>xml is fun...</paragraph>... </chapter> SQL XML 85

94 <chapter number="2" title="xml and Databases"> <paragraph>xml can be used with...</paragraph> </chapter>... <chapter number="10" title="further Reading"> <paragraph>recommended tutorials...</paragraph> </chapter> </book> 12. BOOKCONTENTS ISBN CHPTNUM CHPTTITLE CHPTCONTENT Introduction to XML XML is fun XML and Databases XML can be used with Further Reading Recommended tutorials... db2-xdb:locationpath db2-xdb:locationpath XML db2-xdb:locationpath v v v v <xs:element> <xs:attribute> <db2-xdb:rowsetmapping>> db2-xdb:locationpath (value ) v <xs:element db2-xdb:locationpath="value" /> v <xs:attribute db2-xdb:locationpath="value" /> v <db2-xdb:rowsetmapping> db2-xdb:locationpath="value"> <db2-xdb:rowset>value</db2-xdb:rowset>... </db2-xdb:rowsetmapping>> 86 IBM i: SQL XML

95 db2-xdb:locationpath / name / name prefix : prefix name prefix : name prefix db2-xdb:locationpath <xs:schema> ( ) db2-xdb:locationpath (<xs:element ref="abc">)db2-xdb:locationpath XML db2-xdb:locationpath rowset db2-xdb:locationpath rowset db2-xdb:locationpath attributeformdefault ="unqualified" attributeformdetail = "unqualified" <!-- global attribute --> <xs:attribute name="title" type="xs:string" db2-xdb:rowset="books" db2-xdb:column="title" db2-xdb:locationpath="/books/book/@title"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> db2-xdb:locationpath="/books/book/chapter/@title"> <db2-xdb:rowset>bookcontents</db2-xdb:rowset> <db2-xdb:column>chpttitle</db2-xdb:column> SQL XML 87

96 </db2-xdb:rowsetmapping>> </xs:appinfo> </xs:annotation> </xs:attribute> <xs:element name="books"> <xs:complextype> <xs:sequence> <xs:element name="book"> <xs:complextype> <xs:sequence> <xs:element name="authorid" type="xs:integer" /> <xs:element name="chapter" type="chaptertype" maxoccurs="unbounded" /> </xs:sequence> <xs:attribute name="isbn" type="xs:string" /> <xs:attribute ref="title" /> </xs:complextype> </xs:element> </xs:sequence> </xs:complextype> </xs:element> <xs:complextype name="chaptertype"> <xs:sequence> <xs:element name="paragraph" type="paragraphtype" maxoccurs="unbounded" /> </xs:sequence> <xs:attribute name="number" type="xs:integer" /> <xs:attribute ref="title" /> </xs:complextype> <xs:simpletype name="paragraphtype"> <xs:restriction base="xs:string"/> </xs:simpletype> "title" <book> 1 <chapter> "title" "title" BOOKS BOOKCONTENTS <books> BOOKS <books> <book isbn=" " title="my First XML Book"> <authorid>22</authorid> <!-- this book does not have a preface --> <chapter number="1" title="introduction to XML"> <paragraph>xml is fun...</paragraph>... </chapter> <chapter number="2" title="xml and Databases"> <paragraph>xml can be used with...</paragraph> </chapter>... <chapter number="10" title="further Reading"> <paragraph>recommended tutorials...</paragraph> </chapter> </book>... </books> 88 IBM i: SQL XML

97 13. BOOKS ISBN TITLE CONTENT NULL My First XML Book NULL 14. BOOKCONTENTS ISBN CHPTNUM CHPTTITLE CHPTCONTENT NULL NULL Introduction to XML NULL NULL NULL XML and Databases NULL NULL NULL Further Reading NULL db2-xdb:expression db2-xdb:expression <xs:element> <xs:attribute> <db2-xdb:rowsetmapping> ( ) db2-xdb:expression (value ) v <xs:element db2-xdb:expression="value" db2-xdb:column="value" /> v <xs:attribute db2-xdb:expression="value" db2-xdb:column="value" /> v <db2-xdb:rowsetmapping> <db2-xdb:rowset>value</db2-xdb:rowset> <db2-xdb:column>value</db2-xdb:column> <db2-xdb:expression>value</db2-xdb:expression>... </db2-xdb:rowsetmapping> db2-xdb:expression SQL : SQL XML 89

98 function-name ( expression ) constant $DECOMP_CONTENT $DECOMP_DOCUMENTID ( scalar-fullselect ) expression + expression - * / CONCAT ( expression ) special-register CAST ( expression AS data-type ) XML-function, db2-xdb:expression $DECOMP_CONTENT XML db2-xdb:expression () db2-xdb:expression SQL SQL SQL db2-xdb:expression SQL / function ( expression-list ) SQL (NULL ) constant $DECOMP_CONTENT db2-xdb:contenthandling XML $DECOMP_DOCUMENTID XDBDECOMPXML documentid XML ( scalar-fullselect ) NULL expression operator expression 2 90 IBM i: SQL XML

99 ( expression ) special-register CAST ( expression AS data-type ) NULL SQL NULL SQL NULL NULL NULL ( CAST (NULL AS INTEGER)) XML-function SQL/XML db2-xdb:expression XML SQL XML <num> </num> XML CREATE TABLE TAB1 (ASIS DECIMAL(9,2), ADD INTEGER, TAX INTEGER, STR VARCHAR(25), LITERAL DECIMAL(5,1) SELECT VARCHAR(25)) XML CREATE TABLE SCH1.TAB2 (ID INTEGER, COL1 VARCHAR(25)) SCH1.TAB2 (100, 'TAB2COL1VAL') 1 <xs:element name="num" type="xs:double"> <xs:annotation> <xs:appinfo> <xdb:rowsetmapping> <xdb:rowset>tab1</xdb:rowset> <xdb:column>asis</xdb:column> </xdb:rowsetmapping> <xdb:rowsetmapping> <xdb:rowset>tab1</xdb:rowset> <xdb:column>add</xdb:column> <xdb:expression> CAST(XDB.ADD(1234,CAST($DECOMP_CONTENT AS INTEGER)) AS INTEGER) </xdb:expression> </xdb:rowsetmapping> <xdb:rowsetmapping> <xdb:rowset>tab1</xdb:rowset> <xdb:column>tax</xdb:column> <xdb:expression> SQL XML 91

100 CAST(XDB.TAX(CAST($DECOMP_CONTENT AS DOUBLE)) AS INTEGER) </xdb:expression> </xdb:rowsetmapping> <xdb:rowsetmapping> <xdb:rowset>tab1</xdb:rowset> <xdb:column>str</xdb:column> <xdb:expression> CAST($DECOMP_CONTENT AS VARCHAR(25)) </xdb:expression> </xdb:rowsetmapping> <xdb:rowsetmapping> <xdb:rowset>tab1</xdb:rowset> <xdb:column>literal</xdb:column> <xdb:expression>32.3</xdb:expression> </xdb:rowsetmapping> <xdb:rowsetmapping> <xdb:rowset>tab1</xdb:rowset> <xdb:column>select</xdb:column> <xdb:expression> (SELECT "COL1" FROM "SCH1"."TAB2" WHERE "ID" = 100) </xdb:expression> </xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:element> AuthNumBooks ID 15. TAB1 ASIS ADD TAX STR LITERAL SELECT TAB2COL1VAL db2-xdb:condition db2-xdb:condition (rowset ) <xs:element> <xs:attribute> <db2-xdb:rowsetmapping> db2-xdb:condition (value ) v <xs:element db2-xdb:condition="value" /> v <xs:attribute db2-xdb:condition="value" /> v <db2-xdb:rowsetmapping> <db2-xdb:rowset>value</db2-xdb:rowset> <db2-xdb:condition>value</db2-xdb:condition>... </db2-xdb:rowsetmapping> 92 IBM i: SQL XML

101 SQL v v v BETWEEN v DISTINCT v EXISTS v IN v LIKE v NULL db2-xdb:expression rowset db2-xdb:condition AND true db2-xdb:condition db2-xdb:condition SQL rowset db2-xdb:condition rowset SELECT db2-xdb:condition db2-xdb:condition <xs:element name="a" type="xs:string" db2-xdb:rowset="rowseta" db2-xdb:condition="columnx= abc " /> <xs:element name="b" type="xs:string" db2-xdb:rowset="rowsetb" db2-xdb:condition="columnx" /> <a> "columnx" "columnx" <b> <b> "columnx" <a> XML <a>abc</a> <b>def</b> false <b> "def" SQL XML 93

102 $DECOMP_CONTENT ( ) <a> db2-xdb:condition <b> <a> <xs:element name="a" type="xs:string" db2-xdb:rowset="rowseta" db2-xdb:condition="$decomp_content= abc " /> <xs:element name="b" type="xs:string" db2-xdb:rowset="rowsetb" db2-xdb:column="columnx" /> XML <a>abc</a> <b>def</b> true <a> "abc" $DECOMP_CONTENT XML <xs:element name="intelem" type="xs:integer" db2-xdb:rowset="rowseta" db2-xdb:column="colint" db2-xdb:condition="colint > 100" default="0" /> XML <intelem> "colint > 100" <intelem> "colint" 0 0 > 100 false XML <author> <author ID="0800"> <firstname>alexander</firstname> <lastname>smith</lastname> <activestatus>1</activestatus> </author> db2-xdb:condition <author> 2 <author> ID <firstname> <lastname> NULL <activestatus> 1 <xs:element name="author"> <xs:complextype> <xs:sequence> <xs:element name="firstname" type="xs:string" db2-xdb:rowset="authors" db2-xdb:column="givenname" db2-xdb:condition="$decomp_content IS NOT NULL" /> <xs:element name="lastname" type="xs:string" db2-xdb:rowset="authors" db2-xdb:column="surname" db2-xdb:condition="$decomp_content IS NOT NULL" /> 94 IBM i: SQL XML

103 <xs:element name="activestatus" type="xs:integer" db2-xdb:rowset="authors" db2-xdb:column="statuscode" db2-xdb:condition="$decomp_content=1" /> <xs:attribute name="id" type="xs:integer" db2-xdb:rowset="authors" db2-xdb:column="authid" db2-xdb:condition="$decomp_content BETWEEN 1 and 999 /> </xs:sequence> </xs:complextype> </xs:element> <author> db2-xdb:condition <author> AUTHORS 16. AUTHORS AUTHID GIVENNAME SURNAME STATUSCODE NUMBOOKS 0800 Alexander Smith 1 NULL 1 ID <firstname> <lastname> NULL <author> <xs:element name="author"> <xs:complextype> <xs:sequence> <xs:element name="firstname" type="xs:string" db2-xdb:rowset="authors" db2-xdb:column="givenname" db2-xdb:condition="$decomp_content IS NOT NULL" /> <xs:element name="lastname" type="xs:string" db2-xdb:rowset="authors" db2-xdb:column="surname" db2-xdb:condition="$decomp_content IS NOT NULL"/> <xs:element name="activestatus" type="xs:integer" /> <xs:attribute name="id" type="xs:integer" db2-xdb:rowset="authors" db2-xdb:column="authid" db2-xdb:condition="$decomp_content BETWEEN 1 and 100" /> </xs:sequence> </xs:complextype> </xs:element> <author> <firstname> <lastname> ID AUTHORS 3 AND 1 false AND false db2-xdb:contenthandling db2-xdb:contenthandling <xs:element> <db2- xdb:rowsetmapping> db2-xdb:contenthandling (value ) SQL XML 95

104 v <xs:element db2-xdb:contenthandling="value" /> v <db2-xdb:rowsetmapping db2-xdb:contenthandling="value"> <db2-xdb:rowset>value</db2-xdb:rowset>... </db2-xdb:rowsetmapping> / v text v stringvalue v serializesubtree XML db2-xdb:contenthandling db2-xdb:rowset db2-xdb:column db2-xdb:contenthandling 3 text v (CDATA ) v CDATA ("<![CDATA[" "]]>") () stringvalue v (CDATA ) v CDATA ("<![CDATA[" "]]>") serializesubtree v ( ) CDATA ("<![CDATA[" "]]>") v v : XML XML CDATA XML CCSID XML CCSID XML UTF-8 96 IBM i: SQL XML

105 BLOB XML UTF-8 XML XML XML db2-xdb:contenthandling serializesubtree db2-xdb:contenthandling stringvalue ( mixed true 1 ) db2-xdb:contenthandling "text" db2-xdb:contenthandling db2-xdb:contenthandling db2-xdb:expression db2-xdb:condition $DECOMP_CONTENT db2-xdb:contenthandling db2-xdb:contenthandling db2-xdb:contenthandling <paragraph> ( db2-xdb:contenthandling "text" db2-xdb:contenthandling ) <xs:schema> <xs:element name="books"> <xs:complextype> <xs:sequence> <xs:element name="book"> <xs:complextype> <xs:sequence> <xs:element name="authorid" type="xs:integer" /> <xs:element name="chapter" type="chaptertype" maxoccurs="unbounded" /> </xs:sequence> <xs:attribute name="isbn" type="xs:string" db2-xdb:rowset="bookcontents" db2-xdb:column="isbn" /> <xs:attribute name="title" type="xs:string" /> </xs:complextype> </xs:element> </xs:sequence> </xs:complextype> </xs:element> <xs:complextype name="chaptertype"> <xs:sequence> <xs:element name="paragraph" type="paragraphtype" maxoccurs="unbounded" db2-xdb:rowset="bookcontents" db2-xdb:column="chptcontent" db2-xdb:contenthandling="text" /> </xs:sequence> <xs:attribute name="number" type="xs:integer" db2-xdb:rowset="bookcontents" db2-xdb:column="chptnum" /> <xs:attribute name="title" type="xs:string" db2-xdb:rowset="bookcontents" db2-xdb:column="chpttitle" /> </xs:complextype> SQL XML 97

106 <xs:complextype name="paragraphtype" mixed="1"> <xs:choice> <xs:element name="b" type="xs:string" minoccurs="0" maxoccurs="unbounded" /> </xs:choice> </xs:complextype> </xs:schema> <books> <books> <book isbn=" " title="my First XML Book"> <authorid>22</authorid> <chapter number="1" title="introduction to XML"> <paragraph>xml is <b>lots of</b> fun...</paragraph> </chapter> <chapter number="2" title="xml and Databases"> <paragraph><!-- Start of chapter -->XML can be used with...</paragraph> <paragraph><?processinstr example?> Escape characters such as <![CDATA[ <, >, and & ]]>...</paragraph> </chapter>... <chapter number="10" title="further Reading"> <paragraph>recommended tutorials...</paragraph> </chapter> </book>... <books> 3 db2-xdb:contenthandling XML : CHPTTITLE CHPTCONTENT db2-xdb:contenthandling="text" 17. BOOKCONTENTS ISBN CHPTNUM CHPTTITLE CHPTCONTENT "Introduction to XML" "XML is fun..." "XML and Databases" "XML can be used with..." "XML and Databases" " Escape characters such as <, >, and &..." "Further Reading" "Recommended tutorials..." "text" 1 <b> "text" "text" 2 <paragraph> 98 IBM i: SQL XML

107 db2-xdb:contenthandling="stringvalue" 18. BOOKCONTENTS ISBN CHPTNUM CHPTTITLE CHPTCONTENT "Introduction to XML" "XML is lots of fun..." "XML and Databases" "XML can be used with..." "XML and Databases" " Escape characters such as <, >, and &..." "Further Reading" "Recommended tutorials..." CHPTCONTENT "lots of" (<paragraph> <b> ) db2-xdb:contenthandling "text" "text" "stringvalue" "text" db2-xdb:contenthandling="serializesubtree" 19. BOOKCONTENTS ISBN CHPTNUM CHPTTITLE CHPTCONTENT "Introduction to XML" "<paragraph>xml is <b>lots of</b> fun...</paragraph>" "XML and Databases" "<paragraph><!-- Start of chapter -->XML can be used with...</paragraph>" "XML and Databases" "<paragraph><?processinstr example?> Escape characters such as <![CDATA[ <, >, and & ]]>...</paragraph>" "Further Reading" "<paragraph>recommended tutorials...</paragraph>" 2 <paragraph> (<paragraph> ) CHPTCONTENT <b> XML db2-xdb:normalization db2-xdb:normalization XML $DECOMP_CONTENT (db2-xdb:expression ) XML <xs:element> <xs:attribute> <db2-xdb:rowsetmapping>> SQL XML 99

108 db2-xdb:normalization (value ) v <xs:element db2-xdb:normalization="value" /> v <xs:attribute db2-xdb:normalization="value" /> v <db2-xdb:rowsetmapping> db2-xdb:normalization="value"> <db2-xdb:rowset>value</db2-xdb:rowset>... </db2-xdb:rowsetmapping>> / canonical XML db2-xdb:normalization $DECOMP_CONTENT XML XML XML original XML db2-xdb:normalization $DECOMP_CONTENT XML XML XML whitespacestrip XML db2-xdb:normalization $DECOMP_CONTENT XML v XML v db2-xdb:normalization XML v byteunsigned byte v integerpositiveintegernegativeintegernonpositiveintegernonnegativeinteger v intunsignedint v longunsignedlong v shortunsignedshort v decimal v float v double v boolean 100 IBM i: SQL XML

109 v time v date v datetime v CHAR v VARCHAR v CLOB v DBCLOB v GRAPHIC v VARGRAPHIC db2-xdb:normalization db2-xdb:normalization <xs:element name="author"> <xs:complextype> <xs:sequence> <xs:element name="firstname" type="xs:string" db2-xdb:rowset="authors" db2-xdb:column="firstname" /> <xs:element name="lastname" type="xs:string" db2-xdb:rowset="authors" db2-xdb:column="surname" db2-xdb:normalization="whitespacestrip" /> <xs:element name="activestatus" type="xs:boolean" db2-xdb:rowset="authors" db2-xdb:column="active" db2-xdb:normalization="canonical" /> <xs:attribute name="id" type="xs:integer" db2-xdb:rowset="authors" db2-xdb:column="authid" db2-xdb:normalization="whitespacestrip" /> </xs:sequence> </xs:complextype> </xs:element> <author> ( '_' ) AUTHORS <author ID=" 22"> <firstname>ann</firstname> <lastname> Brown_</lastname> <activestatus>1</activestatus> </author> 20. AUTHORS AUTHID FIRSTNAME SURNAME ACTIVE NUMBOOKS 22 Ann Brown_ true NULL ID db2-xdb:normalization="whitespacestrip" AUTHORS <activestatus> db2-xdb:normalization="canonical" AUTHORS true false SQL XML 101

110 db2-xdb:order db2-xdb:order <db2-xdb:rowsetoperationorder> db2-xdb:order (value ) <xs:schema> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetoperationorder> <db2-xdb:order> <db2-xdb:rowset>value</db2-xdb:rowset>... </db2-xdb:order> </db2-xdb:rowsetoperationorder> </xs:appinfo> </xs:annotation>... </xs:schema> <db2-xdb:order> db2-xdb:rowset XML db2-xdb:order rowset rowset XML db2-xdb:order db2-xdb:rowset 1 rowset RS1 db2-xdb:order RS1 RS2 rowset RS2 rowset rowset <db2-xdb:rowset> rowset rowset rowset rowset <db2-xdb:order> IBM i: SQL XML

111 SQL ID SQL ID &< db2-xdb:order <xs:schema> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetoperationorder> <db2-xdb:order> <db2-xdb:rowset>customer</db2-xdb:rowset> <db2-xdb:rowset>purchase_order</db2-xdb:rowset> </db2-xdb:order> <db2-xdb:order> <db2-xdb:rowset>items_master</db2-xdb:rowset> <db2-xdb:rowset>po_items</db2-xdb:rowset> </db2-xdb:order> </db2-xdb:rowsetoperationorder> </xs:appinfo> </xs:annotation> </xs:schema> 2 CUSTOMER rowset PURCHASE_ORDER 2 ITEMS_MASTER rowset PO_ITEMS 2 ITEMS_MASTER PURCHASE_ORDER rowset db2-xdb:truncate db2-xdb:truncate XML <xs:element> <xs:attribute> <db2-xdb:rowsetmapping>> db2-xdb:truncate (value ) v <xs:element db2-xdb:truncate="value" /> v <xs:attribute db2-xdb:truncate="value" /> v <db2-xdb:rowsetmapping> db2-xdb:truncate="value"> <db2-xdb:rowset>value</db2-xdb:rowset>... </db2-xdb:rowsetmapping>> SQL XML 103

112 0 false 1 true XML db2-xdb:truncate false0 XML XML true1 db2-xdb:truncate XML xs:date xs:time xs:datetime CHAR VARCHAR CLOB GRAPHIC VARGRAPHIC DBCLOB DATE TIME TIMESTAMP db2-xdb:truncate db2-xdb:expression db2-xdb:truncate XML DATETIME TIMESTAMP XML db2-xdb:truncate true 1 <author> <xs:element name="author"> <xs:complextype> <xs:sequence> <xs:element name="firstname" type="xs:string" db2-xdb:rowset="authors" db2-xdb:column="firstname" db2-xdb:truncate="true" /> <xs:element name="lastname" type="xs:string" /> <xs:element name="activestatus" type="xs:boolean" /> <xs:element name="activated" type="xs:date" 104 IBM i: SQL XML

113 db2-xdb:truncate="true" /> <xs:attribute name="id" type="xs:integer" /> <xs:sequence> </xs:complextype> </xs:element> <author> <author ID="0800"> <firstname>alexander</firstname> <lastname>smith</lastname> <activestatus>0</activestatus> <activated> z</activated> </author> FIRSTNAME CHAR SQL 7ACTIVEDATE DATE SQL AUTHORS 21. AUTHORS AUTHID FIRSTNAME SURNAME ACTIVE ACTIVEDATE NUMBOOKS NULL Alexand NULL NULL NULL <firstname> Alexander SQL XML <activated> db2-xdb:truncate true <firstname> <activated> db2-xdb:truncate db2-xdb:truncate () db2-xdb:rowsetmapping db2-xdb:rowsetmapping XML XML 1 <xs:element> <xs:attribute> <xs:appinfo> (<xs:annotation> ) db2-xdb:rowsetmapping (value ) v <xs:element> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>value</db2-xdb:rowset>... </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation>... </xs:element> v <xs:attribute> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>value</db2-xdb:rowset> SQL XML 105

114 ... </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation>... </xs:attribute> <db2-xdb:rowsetmapping> db2-xdb:contenthandling db2-xdb:locationpath XML XML db2-xdb:normalization XML XML db2-xdb:truncate XML <db2-xdb:rowsetmapping> <db2-xdb:rowset> XML XML <db2-xdb:column> XML XML db2-xdb:condition db2-xdb:locationpath <db2-xdb:expression> db2-xdb:rowset <db2-xdb:condition> db2-xdb:expression db2-xdb:truncate db2-xdb:truncate 1 db2-xdb:rowsetmapping db2-xdb:rowset db2-xdb:column <db2-xdb:rowsetmapping> SQL ID 106 IBM i: SQL XML

115 SQL ID &< <db2-xdb:rowsetmapping> isbn 1 isbn BOOKS BOOKCONTENTS <xs:element name="book"> <xs:complextype> <xs:sequence> <xs:element name="authorid" type="xs:integer"/> <xs:element name="chapter" type="chaptertype" maxoccurs="unbounded" /> </xs:sequence> <xs:attribute name="isbn" type="xs:string"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>books</db2-xdb:rowset> <db2-xdb:column>isbn</db2-xdb:column> </db2-xdb:rowsetmapping> <db2-xdb:rowsetmapping> <db2-xdb:rowset>bookcontents</db2-xdb:rowset> <db2-xdb:column>isbn</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:attribute> <xs:attribute name="title" type="xs:string" /> </xs:complextype> </xs:element> <book> BOOKS BOOKCONTENTS <book isbn=" " title="my First XML Book"> <authorid>22</authorid> <!-- this book does not have a preface --> <chapter number="1" title="introduction to XML"> <paragraph>xml is fun...</paragraph>... </chapter>... </book> 22. BOOKS ISBN TITLE CONTENT NULL NULL 23. BOOKCONTENTS ISBN CHPTNUM CHPTTITLE CHPTCONTENT NULL NULL NULL SQL XML 107

116 <db2-xdb:rowsetmapping>db2-xdb:rowset db2-xdb:column XML 2 1 <db2-xdb:rowsetmapping> db2-xdb:rowset db2-xdb:column <xs:element name="book"> <xs:complextype> <xs:sequence> <xs:element name="authorid" type="xs:integer"/> <xs:element name="chapter" type="chaptertype" maxoccurs="unbounded" /> </xs:sequence> <xs:attribute name="isbn" type="xs:string" db2-xdb:rowset="books" db2-xdb:column="isbn" > <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>bookcontents</db2-xdb:rowset> <db2-xdb:column>isbn</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:attribute> <xs:attribute name="title" type="xs:string" /> </xs:complextype> </xs:element> db2-xdb:rowsetoperationorder db2-xdb:rowsetoperationorder 1 db2-xdb:order db2-xdb:order <xs:annotation> <xs:appinfo> db2-xdb:rowsetoperationorder (value ) <xs:schema> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetoperationorder> <db2-xdb:order> <db2-xdb:rowset>value</db2-xdb:rowset>... </db2-xdb:order> </db2-xdb:rowsetoperationorder> </xs:appinfo> </xs:annotation>... </xs:schema> IBM i: SQL XML

117 <db2-xdb:rowsetoperationorder> db2-xdb:order <db2-xdb:rowsetoperationorder> <db2-xdb:order> <db2-xdb:order> XML db2- xdb:rowsetoperationorder db2-xdb:order XML db2-xdb:rowsetoperationorder XML rowset 102 db2-xdb:order XML XML db2-xdb:condition db2-xdb:expression $DECOMP_CONTENT XML XML db2-xdb:contenthandling $DECOMP_CONTENT $DECOMP_CONTENT db2-xdb:expression $DECOMP_CONTENT db2-xdb:normalization db2-xdb:expression $DECOMP_CONTENT $DECOMP_DOCUMENTID XDBDECOMPXML documentid XML $DECOMP_DOCUMENTID $DECOMP_DOCUMENTID XML ID ID XDBDECOMPXML ID ID ID XML CDATA CDATA CDATA CDATA ("<![CDATA[" "]]>") SQL XML 109

118 CDATA (U+000D U+000A) (U+000D) (U+000A) db2-xdb:contenthandling="serializesubtree" XML XML CDATA v CDATA ("<![CDATA[" "]]>") v CDATA (&) &amp: v CDATA (<) &lt: CDATA CDTATA XML NULL XML NULL XML XML NULL 24. NULL NULL X : X v v xsi:nil="true" xsi:nil="1" : v v xsi:nil="true" xsi:nil="1" v xsd:stringxsd:normalizedstringxsd:token xsd:hexbinaryxsd:base64binary xsd:anyurixsd:anysimpletype () X : 1. db2-xdb:condition db2-xdb:expression NULL () 2. CHAR GRAPHIC XML XML NULL NULL 110 IBM i: SQL XML

119 25. NULL NULL ( ) X : v v xsd:stringxsd:normalizedstringxsd:token xsd:hexbinaryxsd:base64binary xsd:anyuri xsd:anysimpletype () : db2-xdb:condition db2-xdb:expression NULL () X XML XML XML XML XML XML v v XML v v v XSR XML XML XML XML XML SQL XML 111

120 XML (restriction extension complextype ) XML db2-xdb:locationpath : XML outofprintbooktype booktype booktype db2-xdb:locationpath outofprinttype <lastpublished> <publisher> db2-xdb:locationpath <xs:schema xmlns:xs=" xmlns:db2-xdb=" <xs:annotation> <xs:appinfo> <db2-xdb:table> <db2-xdb:name>books</db2-xdb:name> <db2-xdb:rowset>inprintrowset</db2-xdb:rowset> </db2-xdb:table> <db2-xdb:table> <db2-xdb:name>outofprint</db2-xdb:name> <db2-xdb:rowset>outofprintrowset</db2-xdb:rowset> </db2-xdb:table> </xs:appinfo> </xs:annotation> <xs:element name="books"> <xs:complextype> <xs:choice> 1 <xs:element name="book" type="booktype" minoccurs="0" maxoccurs="unbounded"/> <xs:element name="outofprintbook" type="outofprintbooktype" minoccurs="0" maxoccurs="unbounded"/> </xs:choice> </xs:complextype> </xs:element> <xs:complextype name="booktype"> <xs:sequence> <xs:element name="authorid" type="xs:integer"/> <xs:element name="chapter" type="chaptertype" maxoccurs="unbounded"/> </xs:sequence> <xs:attribute name="title" type="xs:string" 2a db2-xdb:locationpath="/books/book/@title" db2-xdb:rowset="inprintrowset" db2-xdb:column="title"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping db2-xdb:locationpath="/books/outofprintbook/@title"> 112 IBM i: SQL XML

121 <db2-xdb:rowset>outofprintrowset</db2-xdb:rowset> <db2-xdb:column>title</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:attribute> <xs:attribute name="isbn" type="xs:string" 2b db2-xdb:rowset="inprintrowset" db2-xdb:column="isbn"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping <db2-xdb:rowset>outofprintrowset</db2-xdb:rowset> <db2-xdb:column>isbn</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:attribute> </xs:complextype> <xs:complextype name="outofprintbooktype"> <xs:complexcontent> <xs:extension base="booktype"> <xs:sequence> <xs:element name="lastpublished" type="xs:date" 3 db2-xdb:rowset="outofprintrowset" db2-xdb:column="lastpubdate"/> <xs:element name="publisher" type="xs:string" db2-xdb:rowset="outofprintrowset" db2-xdb:column="publisher"/> </xs:sequence> </xs:extension> </xs:complexcontent> </xs:complextype> <xs:simpletype name="paragraphtype"> <xs:restriction base="xs:string"/> </xs:simpletype> <xs:complextype name="chaptertype"> <xs:sequence> <xs:element name="paragraph" type="paragraphtype" maxoccurs="unbounded" db2-xdb:locationpath="/books/book/chapter/paragraph" db2-xdb:rowset="inprintrowset" db2-xdb:column="content"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping db2-xdb:locationpath="/books/outofprintbook/chapter/paragraph"> <db2-xdb:rowset>outofprintbook</db2-xdb:rowset> <db2-xdb:column>content</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:element> </xs:sequence> <xs:attribute name="number" type="xs:integer"/> <xs:attribute name="title" type="xs:string"/> </xs:complextype> </xs:schema> <book> BOOKS <outofprintbook> OUTOFPRINT XML : 1 <books> <book> <outofprintbook> 2 2 <outofprintbook> <book> SQL XML 113

122 2a 2b XML title isbn <book> <outofprintbook> in-print out-of-print in-print out-of-print ISBN db2-xdb:locationpath 3 lastpublished publisher <outofprintbook> db2-xdb:locationpath XML XML <books> <book isbn=" " title="my First XML Book"> <authorid>22</authorid> <chapter number="1" title="introduction to XML"> <paragraph>xml is fun...</paragraph> </chapter> <chapter number="2" title="xml and Databases"> <paragraph>xml can be used with...</paragraph> </chapter> </book> <outofprintbook isbn=" " title="early XML Book"> <authorid>41</authorid> <chapter number="1" title="introductory XML"> <paragraph>early XML...</paragraph> </chapter> <chapter number="2" title="what is XML"> <paragraph>xml is an emerging technology...</paragraph> </chapter> <lastpublished> </lastpublished> <publisher>early Publishers Group</publisher> </outofprintbook> </books> 26. BOOKS ISBN TITLE CONTENT My First XML Book XML is fun My First XML Book XML can be used with OUTOFPRINT ISBN TITLE CONTENT LASTPUBDATE PUBLISHER Early XML Book Early XML Early Publishers Group Early XML Book XML is an emerging technology Early Publishers Group XML db2-xdb:rowset XML db2-xdb:rowset db2-xdb:rowset <db2-xdb:rowsetmapping> 114 IBM i: SQL XML

123 XML 1 db2-xdb:rowset db2-xdb:rowset : isbn title ALLPUBLICATIONS <publications> <textbook title="programming with XML"> <isbn> </isbn> <author>mary Brown</author> <author>alex Page</author> <publicationdate>2002</publicationdate> <university>university of London</university> </textbook> <childrensbook title="children s Fables"> <isbn> </isbn> <author>bob Carter</author> <author>melanie Snowe</author> <publicationdate>1999</publicationdate> </childrensbook> </publications> v isbn v isbn XML <xs:schema xmlns:xs=" xmlns:db2-xdb=" elementformdefault="qualified" attributeformdefault="unqualified"> <xs:annotation> <xs:appinfo> <db2-xdb:defaultsqlschema>admin</db2-xdb:defaultsqlschema> <db2-xdb:table> <db2-xdb:name>allpublications</db2-xdb:name> 1 <db2-xdb:rowset>textbk_rowset</db2-xdb:rowset> <db2-xdb:rowset>childrens_rowset</db2-xdb:rowset> </db2-xdb:table> </xs:appinfo> </xs:annotation> <xs:element name="publications"> <xs:complextype> <xs:sequence> <xs:element name="textbook" maxoccurs="unbounded"> <xs:complextype> <xs:sequence> <xs:element name="isbn" type="xs:string" 2a db2-xdb:rowset="textbk_rowset" db2-xdb:column="pubs_isbn"/> <xs:element name="author" type="xs:string" maxoccurs="unbounded"/> <xs:element name="publicationdate" type="xs:gyear"/> <xs:element name="university" type="xs:string" maxoccurs="unbounded"/> </xs:sequence> <xs:attribute name="title" type="xs:string" use="required" 2b db2-xdb:rowset="textbk_rowset" db2-xdb:column="pubs_title"/> </xs:complextype> </xs:element> <xs:element name="childrensbook" maxoccurs="unbounded"> <xs:complextype> <xs:sequence> <xs:element name="isbn" type="xs:string" 3a db2-xdb:rowset="childrens_rowset" db2-xdb:column="pubs_isbn"/> SQL XML 115

124 <xs:element name="author" type="xs:string" maxoccurs="unbounded"/> <xs:element name="publicationdate" type="xs:gyear"/> </xs:sequence> <xs:attribute name="title" type="xs:string" use="required" 3b db2-xdb:rowset="childrens_rowset" db2-xdb:column="pubs_title"/> </xs:complextype> </xs:element> </xs:sequence> </xs:complextype> </xs:element> </xs:schema> XML : 1 <xs:annotation> textbk_rowset childrens_rowset XML <db2-xdb:name> (ALLPUBLICATIONS) 2a 2b textbk_rowset <textbook> isbn title <textbook> isbn title ALLPUBLICATIONS 3a 3b childrens_rowset <childrensbook> isbn title <childrensbook> isbn title ALLPUBLICATIONS XML 28. ALLPUBLICATIONS ISBN PUBS TITLE Programming with XML Children's Fables XML : temperature 2 <temperature unit="celsius">49</temperature> <temperature unit="farenheit">49</temperature> unit="celsius" 1 unit="farenheit" 1 XML 116 IBM i: SQL XML

125 <db2-xdb:name>temperature_data</db2-xdb:name> <db2-xdb:rowset>temp_celcius</db2-xdb:rowset> 1 <db2-xdb:rowset>temp_farenheit</db2-xdb:rowset> </db2-xdb:table>... <xs:element name="temperature"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>temp_celcius</db2-xdb:rowset> <db2-xdb:column>col1</db2-xdb:column> </db2-xdb:rowsetmapping> <db2-xdb:rowsetmapping> <db2-xdb:rowset>temp_farenheit</db2-xdb:rowset> <db2-xdb:column>col1</db2-xdb:column> <db2-xdb:expression>cast(myudf_converttocelcius(cast($decomp_content AS FLOAT)) AS FLOAT) </db2-xdb:expression> 2 </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> <xs:complextype> <xs:simplecontent> <xs:extension base="xs:string"> <xs:attribute name="unit" type="xs:string"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> 3 <db2-xdb:rowset>temp_celcius</db2-xdb:rowset> <db2-xdb:condition>$decomp_content = Celsius </db2-xdb:condition> </db2-xdb:rowsetmapping> <db2-xdb:rowsetmapping> <db2-xdb:rowset>temp_farenheit</db2-xdb:rowset> <db2-xdb:condition>$decomp_content = Farenheit </db2-xdb:condition> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:attribute> </xs:extension> </xs:simplecontent> </xs:complextype> </xs:element> XML : 1 <xs:annotation>, temp_celcius temp_farenheit XML 2 temp_farenheit SQL CAST 3 unit="celsius" temp_celcius unit="farenheit" temp_farenheit : XML XML XML XML XML SQL XML 117

126 <publications> <textbook title="programming with XML"> <isbn> </isbn> <author>mary Brown</author> <author>alex Page</author> <publicationdate>2002</publicationdate> <university>university of London</university> </textbook> </publications> XML <textbook> XML <textbook> title DETAILS TITLE DETAILS TEXTBOOKS XML 29. TEXTBOOKS TITLE Programming with XML DETAILS <textbook title="programming with XML"> <isbn> </isbn> <author>mary Brown</author> <author>alex Page</author> <publicationdate>2002</publicationdate> <university>university of London</university> </textbook> <xs:element> <xs:attribute> db2-xdb:rowset db2-xdb:column <db2-xdb:rowsetmapping> <db2-xdb:rowset> <db2-xdb:column> XML 2 <textbook> title <xs:element name="publications"> <xs:complextype> <xs:sequence> <xs:element name="textbook" maxoccurs="unbounded" db2-xdb:rowset="textbooks" db2-xdb:column="details"> <xs:complextype> <xs:sequence> <xs:element name="isbn" type="xs:string"/> <xs:element name="author" type="xs:string" maxoccurs="unbounded"/> <xs:element name="publicationdate" type="xs:gyear"/> <xs:element name="university" type="xs:string" maxoccurs="unbounded"/> </xs:sequence> <xs:attribute name="title" type="xs:string" use="required" db2-xdb:rowset="textbooks" db2-xdb:column="title"/> </xs:complextype> </xs:element> </xs:sequence> </xs:complextype> </xs:element> db2-xdb:rowset db2-xdb:column <textbook> 118 IBM i: SQL XML

127 db2-xdb:contenthandling () db2-xdb:contenthandling serializesubtree XML XML db2-xdb:contenthandling : XML XML 1 rowset 1 1 rowset rowset 1 1 maxoccurs XML rowset 1 (maxoccurs ) XML XML <publications> <textbook title="programming with XML"> <isbn> </isbn> <author>mary Brown</author> <author>alex Page</author> <publicationdate>2002</publicationdate> <university>university of London</university> </textbook> </publications> <isbn> <publicationdate> title TEXTBOOKS XML 30. TEXTBOOKS ISBN TITLE DATE Programming with XML 2002 v <xs:element> <xs:attribute> db2-xdb:rowset db2-xdb:column v <db2-xdb:rowsetmapping> <db2-xdb:rowset> <db2-xdb:column> XML db2-xdb:rowset db2-xdb:column <?xml version="1.0" encoding="utf-8"?> <xs:schema xmlns:xs=" xmlns:db2-xdb=" <xs:annotation> <xs:appinfo> <db2-xdb:defaultsqlschema>"myschema"</db2-xdb:defaultsqlschema> </xs:appinfo> </xs:annotation> SQL XML 119

128 <xs:element name="publications"> <xs:complextype> <xs:sequence> <xs:element name="textbook" maxoccurs="unbounded"> <xs:complextype> <xs:sequence> <xs:element name="isbn" type="xs:string" maxoccurs="1" db2-xdb:rowset="textbooks" db2-xdb:column="isbn"/> <xs:element name="author" type="xs:string" maxoccurs="unbounded"/> <xs:element name="publicationdate" type="xs:gyear" maxoccurs="1" db2-xdb:rowset="textbooks" db2-xdb:column="date"/> <xs:element name="university" type="xs:string" maxoccurs="unbounded"/> </xs:sequence> <xs:attribute name="title" type="xs:string" use="required" db2-xdb:rowset="textbooks" db2-xdb:column="title"/> </xs:complextype> </xs:element> </xs:sequence> </xs:complextype> </xs:element> </xs:schema> maxoccurs 1 TEXTBOOKS 1 1 <textbook> : XML XML 1 rowset 1 rowset rowset 1 1 maxoccurs XML rowset 1 (maxoccurs ) XML XML <textbook title="programming with XML"> <isbn> </isbn> <author>mary Brown</author> <author>alex Page</author> <publicationdate>2002</publicationdate> <university>university of London</university> </textbook> ISBN XML <isbn> <author> TEXTBOOK_AUTH ISBN AUTHNAME 31. TEXTBOOKS_AUTH ISBN AUTHNAME Mary Brown 120 IBM i: SQL XML

129 31. TEXTBOOKS_AUTH () ISBN AUTHNAME Alex Page v <xs:element> <xs:attribute> db2-xdb:rowset db2-xdb:column v <db2-xdb:rowsetmapping> <db2-xdb:rowset> <db2-xdb:column> XML <db2-xdb:rowset> <db2-xdb:column> <?xml version="1.0" encoding="utf-8"?> <xs:schema xmlns:xs=" xmlns:db2-xdb=" <xs:annotation> <xs:appinfo> <db2-xdb:defaultsqlschema>"myschema"</db2-xdb:defaultsqlschema> </xs:appinfo> </xs:annotation> <xs:element name="textbook"> <xs:complextype> <xs:sequence maxoccurs="unbounded"> <xs:element name="isbn" type="xs:string"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>textbooks_auth</db2-xdb:rowset> <db2-xdb:column>isbn</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:element> <xs:element name="author" type="xs:string" maxoccurs="unbounded"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>textbooks_auth</db2-xdb:rowset> <db2-xdb:column>authname</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:element> <xs:element name="publicationdate" type="xs:gyear"/> <xs:element name="university" type="xs:string" maxoccurs="unbounded"/> </xs:sequence> <xs:attribute name="title" type="xs:string" use="required"/> </xs:complextype> </xs:element> </xs:schema> SQL XML 121

130 <isbn> ISBN 2 ISBN <isbn> <author> maxoccurs 1 1 <isbn> <author> : XML XML 2 XML <textbook title="programming with XML"> <isbn> </isbn> <author>mary Brown</author> <author>alex Page</author> <publicationdate>2002</publicationdate> <university>university of London</university> </textbook> 2 ISBN <isbn> 2 XML <db2-xdb:rowsetmapping> <isbn> 32. TEXTBOOKS ISBN TITLE Programming with XML 33. SCHOOLPUBS ISBN SCHOOL University of London XML 2 <isbn> 2 title <university> <?xml version="1.0" encoding="utf-8"?> <xs:schema xmlns:xs=" xmlns:db2-xdb=" <xs:annotation> <xs:appinfo> <db2-xdb:defaultsqlschema>"myschema"</db2-xdb:defaultsqlschema> </xs:appinfo> </xs:annotation> <xs:element name="textbook"> <xs:complextype> <xs:sequence maxoccurs="unbounded"> <xs:element name="isbn" type="xs:string"> 122 IBM i: SQL XML

131 <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>textbooks</db2-xdb:rowset> <db2-xdb:column>isbn</db2-xdb:column> </db2-xdb:rowsetmapping> <db2-xdb:rowsetmapping> <db2-xdb:rowset>schoolpubs</db2-xdb:rowset> <db2-xdb:column>isbn</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:element> <xs:element name="author" type="xs:string" maxoccurs="unbounded"/> <xs:element name="publicationdate" type="xs:gyear"/> <xs:element name="university" type="xs:string" maxoccurs="unbounded"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>schoolpubs</db2-xdb:rowset> <db2-xdb:column>school</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:element> </xs:sequence> <xs:attribute name="title" type="xs:string" use="required"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>textbooks</db2-xdb:rowset> <db2-xdb:column>title</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:attribute> </xs:complextype> </xs:element> : XML rowset rowset 1 XML <publications> <textbook title="programming with XML"> <isbn> </isbn> <author>mary Brown</author> <author>alex Page</author> <publicationdate>2002</publicationdate> <university>university of London</university> </textbook> <childrensbook title="children s Fables"> <isbn> </isbn> <author>bob Carter</author> SQL XML 123

132 <author>melaine Snowe</author> <publicationdate>1999</publicationdate> </childrensbook> </publications> rowset 34. ALLPUBLICATIONS PUBS_ISBN PUBS_TITLE Programming with XML Children's Fables rowset ISBN PUBS_ISBN PUBS_TITLE ISBN rowset 1 XML 2 rowset <textbook> <childrensbook> <?xml version="1.0" encoding="utf-8"?> <xs:schema xmlns:xs=" xmlns:db2-xdb=" <xs:annotation> <xs:appinfo> <db2-xdb:defaultsqlschema>"myschema"</db2-xdb:defaultsqlschema> <db2-xdb:table> <db2-xdb:name>allpublications</db2-xdb:name> <db2-xdb:rowset>testbk_rowset</db2-xdb:rowset> <db2-xdb:rowset>childrens_rowset</db2-xdb:rowset> </db2-xdb:table> </xs:appinfo> </xs:annotation> <xs:element name="publications"> <xs:complextype> <xs:sequence maxoccurs="unbounded"> <xs:element name="textbook"> <xs:complextype> <xs:sequence maxoccurs="unbounded"> <xs:element name="isbn" type="xs:string" db2-xdb:rowset="testbk_rowset" db2-xdb:column="pubs_isbn"/> <xs:element name="author" type="xs:string" maxoccurs="unbounded"/> <xs:element name="publicationdate" type="xs:gyear"/> <xs:element name="university" type="xs:string" maxoccurs="unbounded"/> </xs:sequence> <xs:attribute name="title" type="xs:string" use="required" db2-xdb:rowset="testbk_rowset" db2-xdb:column="pubs_title"/> </xs:complextype> </xs:element> 124 IBM i: SQL XML

133 <xs:element name="childrensbook"> <xs:complextype> <xs:sequence maxoccurs="unbounded"> <xs:element name="isbn" type="xs:string" db2-xdb:rowset="childrens_rowset" db2-xdb:column="pubs_isbn"/> <xs:element name="author" type="xs:string" maxoccurs="unbounded"/> <xs:element name="publicationdate" type="xs:gyear"/> </xs:sequence> <xs:attribute name="title" type="xs:string" use="required" db2-xdb:rowset="childrens_rowset" db2-xdb:column="pubs_title"/> </xs:complextype> </xs:element> </xs:sequence> </xs:complextype> </xs:element> </xs:schema> db2-xdb:rowset rowset rowset <db2-xdb:table> ALLPUBLICATIONS <xs:schema> rowset 1 : XML rowset XML <publications> <textbook title="principles of Mathematics"> <isbn> </isbn> <author>alice Braun</author> <publisher>math Pubs</publisher> <publicationdate>2002</publicationdate> <university>university of London</university> </textbook> </publications> 35. BOOKCONTACTS ISBN CONTACT Alice Braun Math Pubs CONTACT XML (<author> ) (<publisher> ) XML rowset SQL XML 125

134 <?xml version="1.0" encoding="utf-8"?> <xs:schema xmlns:xs=" xmlns:db2-xdb=" <xs:annotation> <xs:appinfo> <db2-xdb:defaultsqlschema>"myschema"</db2-xdb:defaultsqlschema> <db2-xdb:table> <db2-xdb:name>bookcontacts</db2-xdb:name> <db2-xdb:rowset>author_rowset</db2-xdb:rowset> <db2-xdb:rowset>publisher_rowset</db2-xdb:rowset> </db2-xdb:table> </xs:appinfo> </xs:annotation> <xs:element name="publications"> <xs:complextype> <xs:sequence maxoccurs="unbounded"> <xs:element name="textbook" maxoccurs="unbounded"> <xs:complextype> <xs:sequence> <xs:element name="isbn" type="xs:string"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>author_rowset</db2-xdb:rowset> <db2-xdb:column>isbn</db2-xdb:column> </db2-xdb:rowsetmapping> <db2-xdb:rowsetmapping> <db2-xdb:rowset>publisher_rowset</db2-xdb:rowset> <db2-xdb:column>isbn</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:element> <xs:element name="author" type="xs:string" maxoccurs="unbounded"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>author_rowset</db2-xdb:rowset> <db2-xdb:column>contact</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:element> <xs:element name="publisher" type="xs:string"> <xs:annotation> <xs:appinfo> <db2-xdb:rowsetmapping> <db2-xdb:rowset>publisher_rowset</db2-xdb:rowset> <db2-xdb:column>contact</db2-xdb:column> </db2-xdb:rowsetmapping> </xs:appinfo> </xs:annotation> </xs:element> <xs:element name="publicationdate" type="xs:gyear"/> <xs:element name="university" type="xs:string" maxoccurs="unbounded"/> </xs:sequence> <xs:attribute name="title" type="xs:string" use="required"/> </xs:complextype> </xs:element> 126 IBM i: SQL XML

135 </xs:sequence> </xs:complextype> </xs:element> </xs:schema> db2-xdb:rowset rowset rowset <db2-xdb:table> BOOKCONTACTS <xs:schema> XML SQL XML XML SQL XML XML SQL 36. XML SQL SQL T I V M A E R S C C D T T H H A I A A A T M M XML R R E E P stringnormalizedstringtoken 1a* 1a base64binary, 2* 2 hexbinary 2* 2 byteunsigned byte 3* 3 integerpositiveintegernegativeintegernonnegativeinteger 3* 3 nonpositiveinteger intunsignedint 3* 3 longunsignedlong 3* 3 shortunsignedshort 3* 3 decimal 3* 3 float 3* 3 double 3* 3 boolean 3* 3 time 4* 4 10 datetime 4* durationgmonthgyeargdaygmonthdaygyearmonth 4* 4 date 4* 4 14 NameNCNameNOTATIONIDIDREFQName NMTOKENENTITY 1a* 1a ENTITIESNMTOKENSIDREFS 1b* 1b anyuri 6* 6 language 1a* 1a anysimpletype 5* 5 * XML XML SQL XML 127

136 1a 1b XML db2-xdb:truncate true1 XML 1a ( ) 2 XML db2-xdb:truncate true1 XML () 3 db2-xdb:normalization XML db2-xdb:truncate true 1 4 db2-xdb:normalization XML () () db2-xdb:truncate true1 5 XML db2-xdb:truncate true1 6 URI () () db2-xdb:truncate true1uri URI 7 (yyyy-mm-ddmm/dd/yyyy dd.mm.yyyy) 8 (hh.mm.sshh:mm AM PM hh:mm:ss) 9 (yyyy-mm-dd-hh.mm.ss.nnnnnn) 10 XML db2-xdb:truncate true1 XML db2-xdb:truncate true XML XML db2-xdb:truncate true XML db2-xdb:truncate true1 6 db2-xdb:truncate true1 128 IBM i: SQL XML

137 XML db2-xdb:truncate true1 () 37. XML SQL SQL XML C C B B stringnormalizedstringtoken 1a* 1a 1a 1a* base64binary, 3 hexbinary 3 byteunsigned byte 4 integerpositiveintegernegativeintegernonnegativeinteger 4 nonpositiveinteger G R A P H I intunsignedint 4 longunsignedlong 4 shortunsignedshort 4 decimal 4 float 4 double 4 boolean 4 time 5 datetime 5 durationgmonthgyeargdaygmonthdaygyearmonth 5 date 5 NameNCNameNOTATIONIDIDREFQName NMTOKENENTITY 1a* 1a 1a 1a* ENTITIESNMTOKENSIDREFS 1b* 1b 1b 1b* anyuri 6 language 1a anysimpletype 2a* 2a 2a 2a* V A R G R A P H I C L O D B C L O * XML XML 1a 1b 2a XML (2 ) () db2-xdb:truncate true1 XML 1a ( ) XML SQL XML 129

138 db2-xdb:truncate true1 3 XML db2-xdb:truncate true1 XML () 4 db2-xdb:normalization XML () db2-xdb:truncate true1 5 db2-xdb:normalization XML () () db2-xdb:truncate true1 6 URI () () db2-xdb:truncate true1uri URI XML XML SQL 38. XML SQL SQL V A R B B I I N N B A A L R R O XML Y Y B stringnormalizedstringtoken 1a* 1a 1a base64binary, 1a 1a 1a hexbinary 2* 2 2 byteunsigned byte integerpositiveintegernegativeinteger nonnegativeintegernonpositiveinteger intunsignedint longunsignedlong shortunsignedshort decimal float double boolean time datetime durationgmonthgyeargdaygmonthday gyearmonth date NameNCNameNOTATIONIDIDREF 1a* 1a 1a QNameNMTOKENENTITY ENTITIESNMTOKENSIDREFS 1b* 1b 1b anyuri 1a 1a 1a 130 IBM i: SQL XML

139 38. XML SQL () SQL V A R B B I I N N B A A L R R O XML Y Y B language 1a* 1a 1a anysimpletype 3* 3 3 * XML XML 1a 1b XML db2-xdb:truncate true1 1a ( ) 2 XML () () db2-xdb:truncate true1 XML () 3 XML () () db2-xdb:truncate true1 XML XML SQL 39. XML SQL SQL S D M I E A N B D C L T I O F L E G R U L I G I E B O N E N A L A XML T R T L E T stringnormalizedstringtoken base64binary, SQL XML 131

140 39. XML SQL () SQL S M A L L I N I N T E G E XML T R T L E T hexbinary byteunsigned byte integerpositiveintegernegativeinteger nonnegativeintegernonpositiveinteger intunsignedint longunsignedlong shortunsignedshort decimal float double boolean time datetime durationgmonthgyeargdaygmonthday gyearmonth date NameNCNameNOTATIONIDIDREF QNameNMTOKENENTITY ENTITIESNMTOKENSIDREFS anyuri language anysimpletype B I G I N R E A D O U B L D E C F L O A XML 1 v XML v 2-0 XML -0 3 XML SQL -0 XML XML -0 5 INF-INF NaN INF-INFNaN (false )1(true ) 132 IBM i: SQL XML

141 40. XML SQL SQL D N E U C M I E M R A I XML L C stringnormalizedstringtoken 1 1 base64binary, hexbinary byteunsigned byte 2 2 integerpositiveintegernegativeintegernonnegativeintegernonpositiveinteger 4 4 intunsignedint 4 4 longunsignedlong 4 4 shortunsignedshort 2 2 decimal 4 4 float 5 5 double 5 5 boolean 7 7 time datetime durationgmonthgyeargdaygmonthdaygyearmonth date NameNCNameNOTATIONIDIDREFQNameNMTOKENENTITY ENTITIESNMTOKENSIDREFS anyuri language anysimpletype XML 1 v XML v 2-0 XML -0 3 XML SQL -0 XML XML -0 5 INF-INF NaN INF-INFNaN (false )1(true ) SQL XML 133

142 XML XML 41. XML 1 XML 2GB db2-xdb:expression 1024 db2-xdb:condition 1024 db2-xdb:locationpath 128 XML 64 KB db2-xdb:name ()db2-xdb:columndb2- DB2 xdb:defaultsqlschema db2-xdb:sqlschema db2-xdb:rowset 100 db2-xdb:name XML v XML <xs:any> <xs:anyattribute> XML serializesubtree stringvalue db2-xdb:contenthandling <xs:any> <xs:anyattribute> v XML XML v xsi:type XML xsi:type v () XML v v 134 IBM i: SQL XML

143 v NOTATION : v ENTITY : v db2-xdb:expression db2-xdb:condition rowset : rowset db2-xdb:expression db2-xdb:condition XML XML XML XML XML <xs:schema xmlns:xs=" xmlns=" targetnamespace=" elementformdefault="qualified" > <xs:element name="defaultsqlschema" type="xs:string"/> <xs:attribute name="rowset" type="xs:string"/> <xs:attribute name="column" type="xs:string"/> <xs:attribute name="locationpath" type="xs:string"/> <xs:attribute name="truncate" type="xs:boolean"/> <xs:attribute name="contenthandling"> <xs:simpletype> <xs:restriction base="xs:string"> <xs:enumeration value="text"/> <xs:enumeration value="serializesubtree"/> <xs:enumeration value="stringvalue"/> </xs:restriction> </xs:simpletype> </xs:attribute> <xs:attribute name="normalization" > <xs:simpletype> <xs:restriction base="xs:string"> <xs:enumeration value="original"/> <xs:enumeration value="whitespacestrip"/> <xs:enumeration value="canonical"/> </xs:restriction> </xs:simpletype> </xs:attribute> <xs:attribute name="expression" type="xs:string"/> <xs:attribute name="condition" type="xs:string"/> <xs:element name="table"> <xs:complextype> <xs:sequence> <xs:element name="sqlschema" type="xs:string" minoccurs="0"/> <xs:element name="name" type="xs:string"/> <xs:element name="rowset" type="xs:string" maxoccurs="unbounded" form="qualified"/> </xs:sequence> </xs:complextype> </xs:element> <xs:element name="rowsetmapping"> <xs:complextype> <xs:sequence> <xs:element name="rowset" type="xs:string" /> <xs:element name="column" type="xs:string" minoccurs="0"/> <xs:element name="expression" type="xs:string" minoccurs="0" /> <xs:element name="condition" type="xs:string" minoccurs="0"/> </xs:sequence> <xs:attribute ref="truncate" /> <xs:attribute ref="locationpath" /> SQL XML 135

144 <xs:attribute ref="normalization" /> <xs:attribute ref="contenthandling" /> </xs:complextype> </xs:element> <xs:element name= rowsetoperationorder > <xs:complextype> <xs:choice minoccurs= 1 maxoccurs= 1 > <xs:element name= order type= ordertype minoccurs= 1 maxoccurs= unbounded /> </xs:choice> </xs:complextype> </xs:element> <xs:complextype name= ordertype > <xs:sequence> <xs:element name= rowset type= xsd:string minoccurs= 2 maxoccurs= unbounded /> </xs:sequence> </xs:complextype> </xs:schema> XML XML XPath 2.0 XQuery XML XPath XPath XML XPATH XPath 0 v 36 v <dog/> v (2, 3, 4) v (36, <dog/>, "cat") v () v XML 1 2 (2, 3) (3, 5, 6) (3, 5, 6, 2, 3) (3, 5, 6, (2, 3)) 1 (2) IBM i: SQL XML

145 0 XML 10 v "this is a string" v 45 v 1.44 "12.34" xs:decimal xs:decimal("12.34") XPath XML product description product pid ( ID) description namedetailsprice weight <product xmlns=" pid=" "> <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> ID ID 2 ID ( 7 ) SQL XML 137

146 XML v 1 v v v XPath v v v 1 (QName ) parent () children 0 target NCName ( ) content 138 IBM i: SQL XML

147 XML () ( ) () 1 v children v v xs:untypedatomic <product xmlns=" pid=" "> <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> 42. children "Snow Shovel, Deluxe 24"A Deluxe Snow Shovel, 24 inches wide, ergonomic curved handle with D-Grip kg" xs:string "Snow Shovel, Deluxe 24"A Deluxe Snow Shovel, 24 inches wide, ergonomic curved handle with D-Grip kg" xs:untypedatomic XML 1 1 QName v v parent v (DB2 xs:untyped ) SQL XML 139

148 v children v v v v "" v NULL () v () v xs:untypedatomic <product xmlns=" pid=" "> <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> 43. parent xs:untyped children pid "Snow Shovel, Deluxe 24"A Deluxe Snow Shovel, 24 inches wide, ergonomic curved handle with D-Grip kg" xs:string "Snow Shovel, Deluxe 24"A Deluxe Snow Shovel, 24 inches wide, ergonomic curved handle with D-Grip kg" in-scope namespaces (default, xs:untypedatomic 44. name parent xs:untyped children "Snow Shovel, Deluxe 24" " 140 IBM i: SQL XML

149 44. () "Snow Shovel, Deluxe 24" " xs:string "Snow Shovel, Deluxe 24" " xs:untypedatomic in-scope namespaces (default, XML 0 1 v v parent v (DB2 xs:untypedatomic ) v v xs:untypedatomic <product xmlns=" pid=" "> <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> pid 45. pid pid parent xs:untypedatomic " " xs:string " xs:untypedatomic XML 0 1 SQL XML 141

150 v content v parent <product xmlns=" pid=" "> <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> 46. content Snow Shovel, Deluxe 24" parent name Snow Shovel, Deluxe 24" xs:untypedatomic XML 0 1 NCName ( ) v target v content v parent <?xml-stylesheet href="book.css" type="text/css"?> 47. target content parent xml-stylesheet href="book.css" type="text/css" 142 IBM i: SQL XML

151 href="book.css" type="text/css" xs:string XML 1 v content v parent <ID> <!-- This element contains an ID number. --> 101 </ID> 48. content parent This element contains an ID number. ID This element contains an ID number. xs:string XPath XML XML XML XML XMLELEMENT XMLATTRIBUTES SQL XML XML XML XML XPath XML v XML XML XPath XML XPATH version xs:decimal 10 : XML DB2 xs:untypedatomic SQL XML 143

152 xs:untyped xs:untypedatomic v DB2 (xs:untyped xs:untypedatomic) (XPATH ) (17 ) SQL XML XQuery/XPath 2.0 DB2 for i SQL XML XML XML 2008 Database Language SQL Part 14 - XML-Related Specifications (ISO ) XML(CONTENT) XML XML(CONTENT) SQL XML XML XML XML 1.0 ( 1 ) XMLCONCAT(XMLTEXT( text node one ), XMLTEXT( text node two )) XML text node one text node two XML (DB2 for z/os DB2 for LUW ) SQL XML XQuery/XPath 2.0 XML(SEQUENCE) XML(CONTENT) XML(SEQUENCE) SQL XMLCONCAT 2 ('text node one', 'text node two') 144 IBM i: SQL XML

153 XML(SEQUENCE) XML(CONTENT) XML(CONTENT) XML XML XMLPARSE XML XML(SEQUENCE) XML XML(CONTENT) XML SQL XPath : select XMLSERIALIZE(OUTPUT_COL AS VARCHAR(100)) from XMLTABLE( $d_or_e/root/child passing XMLELEMENT(NAME "root", XMLELEMENT(NAME "child", XMLTEXT( hello world ))) as "d_or_e" ) X(OUTPUT_COL); DB2 for i XML $d_or_e <child>hello world</child> "root" DB2 for LUW DB2 for z/os $d_or_e "root" "root" "root" select XMLSERIALIZE(OUTPUT_COL AS VARCHAR(100)) from XMLTABLE( $d_or_e/root/child passing XMLDOCUMENT( XMLELEMENT(NAME "root", XMLELEMENT(NAME "child", XMLTEXT( hello world ))) ) as "d_or_e" ) X(OUTPUT_COL); XML(CONTENT) XML v v DB2 v XML XMLPARSE XPath XPath XQuery 1.0 XPath 2.0 XML World Wide Web Consortium (W3C) XQuery XML World Wide Web Consortium (W3C) DB2 XPath XPath 2.0 SQL XML 145

154 XPath DB2 DB2 XPath XML XMLTABLE SQL XPath XPath DB2 XPath XML v 1 v v v 2 v XPath 1=1 2=2 1=1and2=2 XPath XPath XPath 2 XPath XML XPath XPath XPath 2.0 XQuery 1.0 XPath 2.0 XQuery 1.0 XML XML () 1 XPath XPath 2.0 XQuery 1.0 XPath 2.0 XQuery IBM i: SQL XML

155 DB2 XPath DB2 XPath v xs:integer v xs:decimal v xs:double v xs:string v xs:boolean v xs:untypedatomic v xs:date v xs:datetime v xs:time v xs:duration v xs:yearmonthduration v xs:daytimeduration DB2 (+) 2 XPath yearmonthduration daytimeduration DB2 XPath XML v v v / v v fn URI xpath-functions fn XPath DB2 XPath / XPath / XPath XPath SQL XML 147

156 DB2 XPath XPath (U+0020) (U+000D) (U+000A) (U+0009) XPath v v 2 1 v one- two one- QName () v one -two one QName (-) two QName v one-two QName (-) QName v 5 div2 5div2 5div 2 DB2 XPath XPath (: :) XPath (: This is a comment. It makes code easier to understand. :) DB2 XPath v XPath v (: :) v (: is this a comment? ::) v (: is this a comment? ::) or an error? :) (: :) v (: commenting out a (: comment :) may be confusing, but often helpful :) v "this is just a string :)" 148 IBM i: SQL XML

157 v (: "this is just a string :)" :) "this is another string (:" (: "this is another string (:" :) DB2 XPath UTF-8 DB2 XML UTF-8 UTF-8 CCSID DB2 XPath XPath XMLTABLE SQL DB2 XPath ICU (UNICODE ) (*HEX) Unicode DB2 XPath XML DB2 XPath XML XML URI XPath XPath QName () W3C Namespaces in XML QName URI URI DB2 XPath URI QName QName URI 2 QName URI 2 QName URI XPath QName DTD XML XML pfx1 URI pfx2 URI c 3 <a xmlns:pfx1="uri1" xmlns:pfx2="uri2"> <b> <pfx1:c>c</pfx1:c> <pfx2:c>b</pfx2:c> <c>a</c> </b> </a> c pfx1:c pfx1 (uri1) URI c pfx2:c pfx2 (uri2) URI c c URI NCName () XML NCName NCName XML Name SQL XML 149

158 XPath DB2 XPath URI xs XML xsi XML fn xdt XPath db2-fn DB2 v URI XPath QName - XPath - XMLELEMENTXMLFOREST XMLTABLE DB2 XMLNAMESPACES DB2 QName XPath declare default element namespace XPath XPath DB2 XPath DB2 XPath XML XPath XML xs xs:integer xs:string DB2 XPath 150 IBM i: SQL XML

159 XML prefix:type(value) prefix value value value prefix:type value : xs:integer(100) xs:integer("100") xs:integer xs:anytype: xs:anytype XML xs:anysimpletype: xs:anysimpletype XML xs:anysimpletype ( ) xs:anysimpletype xs:anytype xs:anysimpletype xs:anysimpletype SQL XML 151

160 xs:anysimpletype xs:anyatomictype: xs:anyatomictype XML xs:anyatomictype xs:untypedatomic ( ) xs:anyatomictype xs:anysimpletype xs:anyatomictype xs:untyped xs:untypedatomic xs:untyped: xs:untyped XML xs:untyped ( ) xs:untyped xs:anytype xs:untyped xs:untyped DB2 for i XML xs:untyped xs:untypedatomic: xs:untypedatomic XML xs:untypedatomic xs:untypedatomic () xs:untypedatomic xs:anyatomictype DB2 for i XML XML XML xs:untypedatomic xs:untypedatomic xs:string xs:string XML xs:string 152 IBM i: SQL XML

161 xs:string XML xs:string xs:string(value) value value xs:decimalxs:double xs:integer xs:decimal: xs:decimal 10 xs:decimal 10 (0 9) (+) 10 v v v v 210. xs:decimal xs:decimal(value) value value xs:double: xs:double DB2 XPath IEEE SQL XML 153

162 xs:double E e 10 xs:integer xs:decimal E e 0 () INF-INF NaN double v -1E4 v E12 v 12.78e-2 v 12 v -0 v 0 v INF xs:double xs:double(value) value value xs:integer: xs:integer 10 xs:integer xs:decimal xs:integer () 10 (0 9) (+) v -1 v 0 v v IBM i: SQL XML

163 xs:integer xs:integer(value) value value : DB2 XPath XPath SQL 49. XML DB2 XML SQL xs:double 34 10**-6143 DECFLOAT 10**+6144 xs:decimal **34 10**34-1 xs:integer DECIMAL 34 BIGINT xs:boolean xs:boolean 2 (true false) xs:boolean truefalse1 0 xs:boolean xs:boolean(value) value value xs:datexs:time xs:datetime SQL XML 155

164 xs:date: xs:date 1 xs:date yyyy-mm-ddzzzzzz yyyy 4 (-) (+) 0001 (AD ) mm dd 2 2 zzzzzz v hh:mm (+) (-) hh mm 2 () hh mm UTC - hh mm UTC v Z UTC (Z UTC ) Z +00:00-00:00 (EST) : T05:00:00Z UTC xs:time: xs:time 156 IBM i: SQL XML

165 xs:time hh:mm:ss.sssssssssssszzzzzz xs:time hh:mm:ss.sssssszzzzzz hh 2 () : mm ss 2 2.ssssssssssssssssss 1 12 zzzzzz 1 20 ( (UTC) 5 ) 13:20:00-05:00 xs:datetime: xs:datetime xs:datetime v year v month v day v hour v minute v second v time zone () yearmonthdayhour minute seconds 10 time zone xs:datetime yyyy-mm-ddthh:mm:ss.sssssssssssszzzzzz yyyy 4 (-) (+) 0001 (AD ) SQL XML 157

166 - mm dd T hh () : mm ss 2 2.ssssssssssss 1 12 zzzzzz datetime UTC () ( ) 29 v hh:mm (+) (-) hh mm 2 () hh mm UTC - hh mm UTC v Z UTC (Z UTC ) Z +00:00-00:00 (EST) T12:00:00-05:00 UTC T17:00:00Z xs:duration: xs:duration xs:duration xs:anyatomictype 158 IBM i: SQL XML

167 -P Y3M DT1H46M S P Y3M DT1H46M S ( ) xs:duration ISO 8601 PnYnMnDTnHnMnS P ny nm nd T nh nm ns n n n n n n P1Y2M3DT10H30M 120 -P120D (-) v 1 v 2 v T T v P P1347Y P1347M P1Y2MT2H P0Y1347M P0Y1347M0D P1Y2MT P-1347M -P1347M DB2 xs:duration DB SQL XML 159

168 XPath xs:duration("p2m63dt55h91m") DB duration P2M65DT8H31M xs:daytimeduration: xs:daytimeduration xs:daytimeduration xs:duration -P DT23H163M S P DT23H163M S ( ) xs:daytimeduration PnDTnHnMnS ISO 8601 P nd T nh nm ns n n n n P3DT10H30M 120 -P120D (-) v 1 v 2 v T T P P13D PT47H P3DT2H -PT35.89S P4DT251M 160 IBM i: SQL XML

169 P-134D -P1347D DB2 xs:daytimeduration DB XPath daytimeduration xs:daytimeduration("p63dt55h81s") DB daytimeduration P65DT7H1M21S xs:yearmonthduration: xs:yearmonthduration xs:yearmonthduration xs:duration -P Y3M P Y3M ( ) xs:yearmonthduration PnYnM ISO 8601 P ny nm n n (-) 1 2 P1Y2M 13 -P13M v P v 0 1 (Y M) P1347Y P1347M P-1347M -P1347M P24YM M 1 PY43M Y 1 DB2 xs:yearmonthduration 12 DB XPath yearmonthduration SQL XML 161

170 xs:yearmonthduration("p20y30m") DB yearmonthduration P22Y6M XML 50. xs:string v xs:boolean truefalse v v xs:integer XML xs:decimal : xs:integer XML v xs:double : <=value<= xs:decimal xs:decimal value=+0 value=-0 '0' XML v v xs:durationxs:yearmonthduration xs:daytimeduration xs:datexs:datetime xs:time +00:00-00:00 UTC Z 162 IBM i: SQL XML

171 50. () xs:boolean xs:untypedatomic xs:string xs:boolean xs:double xs:decimal xs:integer v numeric 0 NaN xs:boolean false xs:boolean true xs:decimal xs:double xs:integer xs:date xs:time xs:datetime xs:untypedatomic xs:string xs:boolean xs:untypedatomic xs:string xs:boolean xs:untypedatomic xs:string xs:boolean xs:datetimexs:untypedatomic xs:string xs:datetimexs:untypedatomic xs:string xs:datexs:untypedatomic xs:string v xs:string xs:untypedatomic "true" "1" xs:boolean true "false" "0" xs:boolean false xs:decimal 2 +INF-INFNaN xs:decimal xs:boolean true 1.0 false 0.0 xs:decimal xs:integer xs:double(sv cast as xs:string) SV xs:boolean true 1.0E0 false 0.0E0 xs:integer xs:boolean true 1 false 0 xs:date SQL XML 163

172 50. () xs:duration xs:daytimeduration xs:yearmonthduration xs:untypedatomicxs:string v xs:daytimeduration 0 xs:daytimeduration xs:yearmonthduration xs:durationxs:untypedatomic xs:string xs:durationxs:untypedatomic xs:string v xs:yearmonthduration 0 xs:duration xs:daytimeduration xs:duration xs:yearmonthduration xs:daytimeduration xs:duration xs:yearmonthduration xs:duration xs:yearmonthduration xs:daytimeduration XPath xs:integer 1 declare namespace ipo=" /ipo:purchaseorder[items/item/quantity/xs:integer(.) > 1] xs:untypedatomic DB2 xs:double xs:integer XPath DB2 XPath XPath XPath XPath XPath prolog expression 164 IBM i: SQL XML

173 : primary-expression logical-expression comparison-expression arithmetic-expression path-expression : namespace-declaration default-namespace-declaration DB2 XPath ipo URI XML XMLPO ipo:purchaseorder 1 shipto JanebillTo Jason SELECT X.* FROM T1, XMLTABLE ( declare namespace ipo=" /ipo:purchaseorder[shipto/@name = "Jane" and billto/@name = "Jason"] PASSING T1.XMLPO) X; 1. DB2 XPath XPath (;) XPath 1 : XPath URI URI XPath : xmlns xml SQL XML 165

174 namespace-declaration declare namespace prefix = stringliteral ; prefix URI (QName) stringliteral URI URI xml URI ns1 URI declare namespace ns1 = " /ns1:purchaseorder[shipto/name = "Jane" and billto/name = "Jason"] ns1 URI purchaseorder : XPath QName () XPath URI XPath 1 default-namespace-declaration declare default element namespace stringliteral ; 166 IBM i: SQL XML

175 namespace URI URI namespace URI declare default element namespace " (purchaseorder shiptobillto name) URI declare default element namespace " /purchaseorder[shipto/name = "Jane" and billto/name = "Jason"] URI : v v XML DB2 for i xs:untypedatomic fn:data ("Some text", <anelement>more text</anelement>, 1001) ("Some text", "More text", 1001) XPath v SQL XML 167

176 v v : XPath XPath v v : xs:decimal (xs:decimal ) xs:double xs:double 13.54e-2 xs:decimal 100 xs:decimal xs:double xs:double xs:double(13.54e-2) + xs:decimal(100) : xs:decimal xs:integer xs:integer xs:daytimeduration fn:hours-from-duration xs:duration fn:hours-from-duration(xs:daytimeduration("pt2h")) primary-expression: literal variable-reference parenthesized-expression context-item-expression function-call 168 IBM i: SQL XML

177 : DB2 XPath 2 xs:integer xs:decimal xs:double (.) e E xs:integer (.) e E xs:decimal e E xs:double XML xs:string 2 2 XML XPath XPath : E+77 XPath : SELECT X.* FROM X1, XMLTABLE( $inp/purchaseorder[contains(., "string literal double-quote "" in the middle")] PASSING X1.XMLPO as "inp") X; : DB2 XPath (&) (;) DB2 XPath 51. DB2 XPath < < > > & & " " &apos; ' SQL XML 169

178 : Unicode XML &#x &# (;) &#x (;) ISO/IEC &# (;) 10 DB2 XPath : ($) QName XPath XPath XPath SQL XMLTABLE 2 URI DB2 for i XMLTABLE :IHV $PARTNUMBER C1 $QTY SELECT X.* FROM T1, XMLTABLE( //item[@partnum = $PARTNUMBER and quantity=$qty] PASSING T1.XMLPO, :IHV AS PARTNUMBER, T1.C1 AS QTY) X; : parenthesized-expression: ( ) expression ((5+5) * (6+4)) div IBM i: SQL XML

179 20 : 1 (.). () products Nylon pants declare namespace ipo=" /ipo:products/product/name[. = "Nylon pants"] : DB2 XPath XPath XPath DB2 XPath 1. DB2 XPath 2. 0 DB2 XPath a. b. xs:untypedatomic numeric xs:untypedatomic xs:double c. xs:integerxs:decimal xs:double 3. pid 3 declare namespace pos=" fn:substring(/pos:product/@pid, 1, 3) XML DB2 XPath XPath 2.0 (/) 2 (//) 1 (/)2 (//) SQL XML 171

180 (/) 2 (//) /descendant-or-self::node()/ (/) description/name description description name name 1 ID XPath DB2 (/) : /*5 (/)*5 DB2 (/*) 5 2 (//) path-expression: / // step / step // step step: filter-expression axis-step 172 IBM i: SQL XML

181 1 //stock[bid/xs:double(price) > : 3 XML XML axis-step: axis xmlname-test xmlkind-test predicate-list : child attribute :: descendant :: self :: descendant-or-self :: parent :: xmlname-test: xml-qualified-name xml-wildcard xml-wildcard: * xml-nsprefix:* *:xml-ncame SQL XML 173

182 xmlkind-test: document-node ( ) element ( name ) * element ( ) name * attribute ( ) name * processing instruction ( xml-ncname ) processing-instruction-literal-string comment ( ) text ( ) node ( ) predicate-list: [ expression ] child price child::price price : XML 52 DB2 XPath 52. DB2 XPath child child descendant () attribute 174 IBM i: SQL XML

183 52. DB2 XPath () self descendant-or-self parent DB2 XPath W3 DB2 XPath childdescendantattributeself descendant-or-self parent 1 1 : true QName QName attribute QName QName QName () 2 QName URI () : 1 QName URI QName URI DB2 XPath SQL XML 175

184 53. DB2 XPath QName QName QName () attribute QName QName child::para para para * child::* * child 54 DB2 XPath 54. DB2 XPath node() child::node() node() text() comment() processing-instruction(ncname) processing-instruction(stringliteral) element() NCName (XML "PITarget") ( ) ( ) XPath 1.0 child::text() text() child::comment() comment() child::processing-instruction (xml-stylesheet) processing-instruction(xmlstylesheet) PITarget xml-stylesheet child child::processing-instruction ("xml-stylesheet") processing-instruction("xmlstylesheet") PITarget xml-stylesheet child child::element() element() child 176 IBM i: SQL XML

185 54. DB2 XPath () element(qname) () child::element("price") element("price") price child element(*) attribute() attribute(qname) attribute(*) document-node() document-node(element(qname)) document-node(element(*)) ( ) 1 child::element(*) element(*) child::attribute() attribute() child child::attribute("price") attribute("price") price child child::attribute(*) attribute(*) self::document-node() document-node() self self::document-node(element ("price")) document-node(element("price")) self price self::document-node(element(*)) document-node (element(*)) self : ([]) () 1 xs:boolean true false singleton singleton singleton true child::employee/child::address[2] SQL XML 177

186 child::employee/child::address[fn:position() = 2] employee 2 singleton false false true v descendant::phone[attribute::type = "work"] phone work v child::address[prov-state][pcode-zip] prov-state pcode-zip address : DB2 XPath attribute() child:: attribute:: // /descendant-or-self::node()/.. parent::node() child::para para para 178 IBM i: SQL XML

187 56. () child::* * child::text() text() child::node() node() name child::para[attribute::type="warning"] para[@type="warning"] warning para child::chapter[child::title="introduction"] chapter[title="introduction"] Introduction 1 title chapter child::chapter[child::title] chapter[title] 1 title chapter 0 1 true filter-expression primary-expression predicate-list $x customerinfo $x SQL XML 179

188 declare default element namespace " $x[.//customerinfo] DB2 XPath -()+ ( ) *dividiv mod +, - : a-b a -b a-b v v v v v DB2 XPath v v v (xs:untypedatomic) DB2 XPath xs:double DB2 XPath 3. DB2 XPath ( xs:integer ) 4. DB2 XPath A 1 B 2 xs:integerxs:decimalxs:double (xs:integerxs:decimalxs:double) 180 IBM i: SQL XML

189 58. A B A+B xs:date xs:yearmonthduration xs:date xs:yearmonthduration xs:date xs:date xs:date xs:daytimeduration xs:date xs:daytimeduration xs:date xs:date xs:time xs:daytimeduration xs:time xs:daytimeduration xs:time xs:time xs:datetime xs:yearmonthduration xs:datetime xs:yearmonthduration xs:datetime xs:datetime xs:datetime xs:daytimeduration xs:datetime xs:daytimeduration xs:datetime xs:datetime xs:yearmonthduration xs:yearmonthduration xs:yearmonthduration xs:daytimeduration xs:daytimeduration xs:daytimeduration A-B xs:date xs:date xs:daytimeduration xs:date xs:yearmonthduration xs:date xs:date xs:daytimeduration xs:date xs:time xs:time xs:daytimeduration xs:time xs:daytimeduration xs:time xs:datetime xs:datetime xs:daytimeduration xs:datetime xs:yearmonthduration xs:datetime xs:datetime xs:daytimeduration xs:datetime xs:yearmonthduration xs:yearmonthduration xs:yearmonthduration xs:daytimeduration xs:daytimeduration xs:daytimeduration A*B xs:yearmonthduration xs:yearmonthduration xs:yearmonthduration xs:yearmonthduration xs:daytimeduration xs:daytimeduration xs:daytimeduration xs:daytimeduration A idiv B xs:integer A div B ( xs:integer xs:decimal) xs:yearmonthduration xs:yearmonthduration xs:daytimeduration xs:daytimeduration xs:yearmonthduration xs:yearmonthduration xs:decimal xs:daytimeduration xs:daytimeduration xs:decimal A mod B SQL XML 181

190 arithmetic expression multiplicative-expression + multiplicative-expression - multiplicative-expression multiplicative expression + - path_expression * path_expression div + idiv - mod (8.25% ) 1 SELECT X.* FROM T1, XMLTABLE( declare namespace pos=" /pos:product/description[price *.0825 > 1] PASSING T1.DESCRIPTION) X; 2 xs:date xs:yearmonthduration P8559D SELECT * FROM XMLTABLE( xs:date(" ") - xs:date(" ") ) X; 2 DB2 XPath 1 () : 2 1 true =!=<<=> >= 59. XPath = 1 2 true!= 1 2 true 182 IBM i: SQL XML

191 59. XPath () < 1 2 true <= 1 2 true > 1 2 true >= 1 2 true DB2 XPath v xs:untypedatomic (xs:integerxs:decimal xs:double) xs:double v xs:untypedatomic xs:untypedatomic xs:string xs:untypedatomic xs:string v xs:untypedatomic xs:stringxs:untypedatomic xs:untypedatomic true comparison expression arithmetic-expression = arithmetic-expression!= < > <= >= 20 declare namespace pos=" /pos:product/description[price < 20] SQL XML 183

192 2 (true) 2 (true) true and or 60. XPath and true true true true DB2 XPath 1. (EBV) EBV 61. (EBV) 1 EBV 2 EBV true and true true true and false false false and true false false and false false true and and true false and false and false false and true true true false false false true false true false true true true true true true false false 184 IBM i: SQL XML

193 expression and or expression declare namespace pos=" /pos:product/description[name = "Snow Shovel, Deluxe 24" or name = "Snow Shovel, Basic 22"] fn:matches fn:replace fn:tokenize character ^.? $ character-class-escape * [ character-group ] + ( regular-expression ) character-group character ^ character-class-escape character character-class-escape character XML (\) (.) (\n) (^) SQL XML 185

194 m ($) m (?) 0 1 (*) 0 (+) 1 ( ) () () ([) (]) [aeiou] v [a-z] v [a-p] a p v [0-9] (() ()) ({) (}) character-class-escape (\) 62. n #x0a r #x0d t #x09 \\ \ \ \ IBM i: SQL XML

195 62. () \?? (?) \* * \+ + \( ( \) ) \{ { \} } \$ $ \- - \[ [ \] ] ^ ^ character-group v "hello[0-9]world" "hello3world" "hello world" v "^hello" hello world "^hello" world hello v "hello$" world hello "hello$" hello world v "(ca) (bd)" "arcade" "abdicate" v "^((ca) (bd))" "arcade" "abdicate" v "w?s" "ws" "s" v "w.*s" "was" "waters" v "be+t" "beet" "bet" v "\[n\]" "[n]" XPath DB2 XPath XPath 2.0 XQuery 1.0 SQL XML 187

196 DB2 for i XPath XPath 63. fn:compare fn:compare fn:concat fn:contains fn:concat 199 fn:contains fn:lower-case 211 fn:lower-case fn:matches 212 fn:matches fn:max 213 fn:max fn:min 214 fn:min fn:normalize-space 220 fn:normalize-space fn:replace fn:starts-with 222 fn:replace 226 fn:starts-with fn:string 226 fn:string fn:string-length 227 fn:string-length fn:substring 227 fn:substring fn:tokenize 231 fn:tokenize fn:translate 232 fn:translate fn:upper-case 233 fn:upper-case 64. fn:abs 191 fn:abs fn:max 213 fn:max fn:min 214 fn:min fn:round 223 fn:round fn:sum 228 fn:sum 188 IBM i: SQL XML

197 65. fn:boolean 197 fn:boolean fn:exists false true 206 fn:exists fn:not true false falsetrue 220 fn:not 66. fn:adjust-date-to-timezone xs:date 192 fn:adjust-date-to-timezone fn:adjust-datetime-to-timezone fn:adjust-time-to-timezone fn:current-date fn:current-datetime xs:datetime xs:time UTC UTC 194 fn:adjust-datetime-to-timezone 195 fn:adjust-time-to-timezone 200 fn:current-date 200 fn:current-datetime db2-fn:current-local-date 201 db2-fn:current-local-date db2-fn:current-local-datetime 201 db2-fn:current-local-datetime db2-fn:current-local-time 201 db2-fn:current-local-time fn:current-time fn:datetime UTC xs:date xs:time xs:datetime 202 fn:current-time 203 fn:datetime fn:day-from-date xs:date 203 fn:day-from-date fn:day-from-datetime xs:datetime 204 fn:day-from-datetime fn:days-from-duration 204 fn:days-from-duration SQL XML 189

198 66. () fn:hours-from-datetime xs:datetime 206 fn:hours-from-datetime fn:hours-from-duration 207 fn:hours-from-duration fn:hours-from-time xs:time 208 fn:hours-from-time function fn:implicit-timezone UTC PT0S 208 fn:implicit-timezone db2-fn:local-timezone 211 db2-fn:local-timezone fn:max 213 fn:max fn:min 214 fn:min fn:minutes-from-datetime xs:datetime 215 fn:minutes-from-datetime fn:minutes-from-duration 215 fn:minutes-from-duration fn:minutes-from-time xs:time 216 fn:minutes-from-time fn:month-from-date xs:date 217 fn:month-from-date fn:month-from-datetime xs:datetime 217 fn:month-from-datetime fn:months-from-duration 218 fn:months-from-duration fn:seconds-from-datetime xs:datetime 224 fn:seconds-from-datetime fn:seconds-from-duration 224 fn:seconds-from-duration fn:seconds-from-time xs:time 225 fn:seconds-from-time fn:sum 228 fn:sum fn:timezone-from-date xs:date 229 fn:timezone-from-date fn:timezone-from-datetime xs:datetime 229 fn:timezone-from-datetime 190 IBM i: SQL XML

199 66. () fn:timezone-from-time xs:time 230 fn:timezone-from-time fn:year-from-date xs:date 234 fn:year-from-date fn:year-from-datetime xs:datetime 234 fn:year-from-datetime fn:years-from-duration 235 fn:years-from-duration 67. fn:count 199 fn:count fn:data 202 fn:data fn:distinct-values 205 fn:distinct-values fn:last fn:position 209 fn:last 221 fn:position 68. fn:local-name 210 fn:local-name fn:name 218 fn:name fn:abs fn:abs fn:abs(numeric-value) numeric-value numeric-value v xs:double v xs:decimal v xs:integer v v xs:untypedatomic SQL XML 191

200 numeric-value xs:untypedatomic xs:double numeric-value numeric-value numeric-value fn:abs numeric-value v numeric-value xs:doublexs:decimal xs:integer numeric-value v numeric-value xs:doublexs:decimal xs:integer numeric-value v numeric-value xs:untypedatomic xs:double fn:abs(-10.5) 10.5 fn:adjust-date-to-timezone fn:adjust-date-to-timezone xs:date xs:date fn:adjust-date-to-timezone(date-value ),timezone-value date-value date-value xs:date timezone-value date-value timezone-value -PT14H PT14H () xs:daytimeduration () timezone-value PT0H UTC xs:date date-value xs:date 192 IBM i: SQL XML

201 69. fn:adjust-date-to-timezone date-value timezone-value date-value date-value date-value date-value ( PT0H) ( PT0H) timezone-value date-value date-value date-value timezone-value date-value date-value date-value 00:00:00 datetime timezone-value xs:date(fn:adjust-datetime-to-timezone(xs:datetime(date-value),timezone-value)) $tz -10 xs:daytimeduration("-pt10h") UTC timezone-value -PT10H fn:adjust-date-to-timezone(xs:date(" :00"), $tz) :00 UTC timezone-value -PT10H fn:adjust-date-to-timezone(xs:date(" "), $tz) :00 UTC timezone-value timezone-value PT0H fn:adjust-date-to-timezone(xs:date(" :00")) Z UTC UTC timezone-value fn:adjust-date-to-timezone(xs:date(" :00"), ()) SQL XML 193

202 fn:adjust-datetime-to-timezone fn:adjust-datetime-to-timezone xs:datetime xs:datetime fn:adjust-datetime-to-timezone(datetime-value ),timezone-value datetime-value datetime datetime-value xs:datetime timezone-value datetime-value timezone-value -PT14H PT14H () xs:daytimeduration () timezone-value PT0H UTC xs:datetime datetime-value xs:datetime 70. fn:adjust-datetime-to-timezone datetime-value timezone-value datetime-value datetime-value datetime-value datetime-value ( PT0H) ( PT0H) datetime-value timezone-value timezone-value datetime-value datetime-value timezone-value datetime-value $tz -10 xs:daytimeduration("-pt10h") 194 IBM i: SQL XML

203 UTC datetime -PT10H time zone-value fn:adjust-datetime-to-timezone(xs:datetime(" t10:00:00-07:00"), $tz) datetime T07:00:00-10: datetime datetime-value -PT10H timezone-value fn:adjust-datetime-to-timezone(xs:datetime(" t10:00:00"), $tz) datetime T10:00:00-10:00 UTC datetime timezone-value PT0H fn:adjust-datetime-to-timezone(xs:datetime(" t10:00:00-07:00")) datetime T17:00:00Z UTC datetime UTC datetime timezone-value fn:adjust-datetime-to-timezone(xs:datetime(" t10:00:00-07:00"), ()) datetime T10:00:00 fn:adjust-time-to-timezone fn:adjust-time-to-timezone xs:time xs:time fn:adjust-time-to-timezone(time-value ),timezone-value time-value time-value xs:time timezone-value time-value timezone-value -PT14H PT14H () xs:daytimeduration () timezone-value PT0H UTC xs:time time-value xs:time SQL XML 195

204 71. fn:adjust-time-to-timezone date-value timezone-value time-value time-value time-value time-value ( PT0H) ( PT0H) timezone-value time-value timezone-value 12 time-value time-value timezone-value time-value $tz -10 xs:daytimeduration("-pt10h") UTC-7 10:00 -PT10H timezone-value fn:adjust-time-to-timezone(xs:time("10:00:00-07:00"), $tz) 7:00:00-10:00 -PT10H 1:00 fn:adjust-time-to-timezone(xs:time("13:00:00"), $tz) 13:00:00-10:00 -PT10H UTC-7 10:00 timezone-value PT0H fn:adjust-time-to-timezone(xs:time("10:00:00-07:00")) 17:00:00Z UTC UTC-7 8:00 timezone-value fn:adjust-time-to-timezone(xs:time("08:00:00-07:00"), ()) 8:00: IBM i: SQL XML

205 2 12 fn:adjust-time-to-timezone fn:adjust-time-to-timezone(xs:time("01:00:00+14:00"), $tz) = xs:time("01:00:00-10:00") true fn:boolean fn:boolean fn:boolean(sequence-expression) sequence-expression (EBV) sequence-expression 72. EBV EBV false 1 true xs:boolean ( xs:boolean false - xs:boolean false ) true - xs:boolean true xs:string xs:untypedatomic ( false - ) true - () false - NaN true - : 1 1 : 0 v fn:boolean(0) false fn:compare fn:compare 2 fn:compare(string-1,string-2) SQL XML 197

206 string-1 string-2 xs:string DB2 Unicode UTF-8 string-1 string-2 xs:integer -1 string-1 string-2 0 string-1 string-2 1 string-1 string-2 2 string-1 string-2 0 string-1 string-2 () string-1 string-2 string-2 string-1 string-1 string-2 string-1 string-2 'ABC' 'ABD' fn:compare( ABC, ABD ) 'ABC' 'ABD' -1 fn:concat fn:concat 1 fn:concat(string-value,string-value ),, string-value string-value xs:string string-value string-value xs:string 198 IBM i: SQL XML

207 'ABC''ABD' 'ABE' fn:concat( ABC, ABD, (), ABE ) 'ABCABDABE' fn:contains fn:contains fn:contains(string,substring) string substring string xs:string string string substring string substring xs:string string substring v string substring substring string true substring string false v string substring true v substring true 'Test literal' 'lite' fn:contains( Test literal, lite ) true fn:count fn:count fn:count(sequence-expression) sequence-expression SQL XML 199

208 sequence-expression sequence-expression xs:integer sequence-expression 0 1 fn:count(5) ID K55 fn:count(//company/emp[dept/@id="k55"]) fn:current-date fn:current-date UTC fn:current-date() xs:date fn:current-date() Z fn:current-datetime fn:current-datetime UTC fn:current-datetime() xs:datetime fn:current-datetime() ( -PT5H) : T11:25: Z 200 IBM i: SQL XML

209 db2-fn:current-local-date db2-fn:current-local-date db2-fn:current-local-date() xs:date db2-fn:current-local-date() (GMT) :00 (-PT5H) db2-fn:current-local-datetime db2-fn:current-local-datetime db2-fn:current-local-datetime() xs:datetime db2-fn:current-local-datetime() : T06:25: db2-fn:current-local-time db2-fn:current-local-time db2-fn:current-local-time() SQL XML 201

210 xs:time db2-fn:current-local-time() (GMT) 6:31 (-PT5H) 01:31: fn:current-time fn:current-time UTC fn:current-time() xs:time fn:current-time() 6:31 06:31: Z fn:data fn:data fn:data(sequence) sequence () xs:anyatomictype v v 202 IBM i: SQL XML

211 billto fn:data(//billto/name) fn:datetime fn:datetime xs:date xs:time xs:datetime fn:datetime(date-value,time-value) date-value xs:date time-value xs:time date-value time-value xs:datetime v v v 2 xs:date xs:time xs:datetime fn:datetime((xs:date(" ")), (xs:time("12:30:59"))) xs:datetime T12:30:59 fn:day-from-date fn:day-from-date xs:date fn:day-from-date(date-value) date-value date-value xs:date date-value xs:date xs:integer 1 31 date-value SQL XML 203

212 date-value fn:day-from-date(xs:date(" ")) 1 fn:day-from-datetime fn:day-from-datetime xs:datetime fn:day-from-datetime(datetime-value) datetime-value datetime datetime-value xs:datetime datetime-value xs:datetime xs:integer 1 31 datetime-value datetime-value UTC :00 datetime fn:day-from-datetime(xs:datetime(" t20:00:00+04:00")) 31 fn:days-from-duration fn:days-from-duration fn:days-from-duration(duration-value) duration-value duration-value xs:daytimeduration xs:durationxs:yearmonthduration duration-value 204 IBM i: SQL XML

213 v duration-value xs:daytimeduration xs:duration xs:integer xs:daytimeduration duration-value duration-value v duration-value xs:yearmonthduration 0 v duration-value xs:daytimeduration duration-value (S idiv 86400) () S xs:daytimeduration duration-value fn:days-from-duration(xs:daytimeduration("-p10dt00h")) fn:days-from-duration(xs:daytimeduration("p3dt55h")) P5D7H 5 fn:distinct-values fn:distinct-values fn:distinct-values(sequence-expression) sequence-expression v v v sequence-expression sequence-expression 2 XPath v 2 v xs:untypedatomic xs:string v v v sequence-expression SQL XML 205

214 v xs:double v sequence-expression NaN NaN b <x xmlns=" <b>1</b><b>a</b><b>1.0</b><b>a</b><b>1</b> </x> declare default element namespace " fn:distinct-values($d/x/b) ("1", "a", "1.0", "A") fn:exists fn:exists ()XML () fn:exists false fn:exists true fn:exists(sequence-expression) sequence-expression sequence-expression true sequence-expression false 1: phone customer fn:exists true fn:exists($info/customer/phone) 2: Cid customer fn:exists true fn:exists($info/customer/@cid) 3: comment comment fn:exists false comment fn:exists false fn:exists($info/comment/text()) fn:hours-from-datetime fn:hours-from-datetime xs:datetime 206 IBM i: SQL XML

215 fn:hours-from-datetime(datetime-value) datetime-value datetime datetime-value xs:datetime datetime-value xs:datetime xs:integer 0 23 datetime-value datetime-value UTC :00 datetime fn:hours-from-datetime(xs:datetime(" t14:00:00-08:00")) 14 fn:hours-from-duration fn:hours-from-duration fn:hours-from-duration(duration-value) duration-value duration-value xs:daytimedurationxs:duration xs:yearmonthduration duration-value v duration-value xs:daytimeduration xs:duration xs:integer () xs:daytimeduration duration-value duration-value v duration-value xs:yearmonthduration xs:integer 0 v duration-value xs:daytimeduration duration-value ((S mod 86400) idiv 3600) () S xs:daytimeduration duration-value SQL XML 207

216 126 fn:hours-from-duration(xs:daytimeduration("pt126h")) P5DT6H 6 fn:hours-from-time function fn:hours-from-time xs:time fn:hours-from-time(time-value) time-value time-value xs:time time-value xs:integer 0 23 time-value time-value UTC-8 9:30 fn:hours-from-time(xs:time("09:30:00-08:00")) 9 fn:implicit-timezone fn:implicit-timezone datetime PT0S fn:implicit-timezone() xs:daytimeduration xs:daytimeduration("pt0s") fn:implicit-timezone() 208 IBM i: SQL XML

217 fn:last fn:last fn:last() xs:integer v fn:last "/" "//" /a/b/c/fn:last /a/b/[c/fn:last=3] v descendant descendant-or-self /a/b/descendant::c[fn:last()=1] v descendant descendant-or-self /a/(b/descendant::c)[fn:last()=1] CUSTOMER 1003 <customerinfo xmlns=" Cid="1003"> <name>robert Shoemaker</name> <addr country="canada"> <street>1596 Baseline</street> <city>aurora</city> <prov-state>ontario</prov-state> <pcode-zip>n8x-7f8</pcode-zip> </addr> <phone type="work"> </phone> <phone type="home"> </phone> <phone type="cell"> </phone> <phone type="cottage"> </phone> </customerinfo> xml 1 fn:last fn:last SELECT X.* FROM CUSTOMER, XMLTABLE ( declare default element namespace " $D/customerinfo/phone[fn:last()] PASSING CUSTOMER.INFO AS "D") X WHERE CID=1003 SQL XML 209

218 <phone type="cottage"> </phone> XML fn:local-name fn:local-name fn:local-name( ) node node fn:local-name xs:string node node v node v node node node v node v node xs:string b SELECT * FROM XMLTABLE( fn:local-name($d/x/b) PASSING XMLPARSE(DOCUMENT <x><b><c></c></b></x> ) AS "d" COLUMNS RESULTNAME VARCHAR(100) PATH ) X "b" fn:localname() CUSTOMER 1001 <customerinfo xmlns=" Cid="1001"> <name>kathy Smith</name> <addr country="canada"> <street>25 EastCreek</street> <city>markham</city> <prov-state>ontario</prov-state> <pcode-zip>n9c 3T6</pcode-zip> </addr> <phone type="work"> </phone> </customerinfo> 210 IBM i: SQL XML

219 SELECT XV.* FROM CUSTOMER, XMLTABLE(XMLNAMESPACES(DEFAULT ), $X/customerinfo/*[fn:last()]/fn:local-name() PASSING CUSTOMER.INFO AS "X") COLUMNS LOCALNAME CLOB(1K) PATH. ) XV WHERE CID=1001 "phone" db2-fn:local-timezone db2-fn:local-timezone db2-fn:local-timezone() (UTC) xs:daytimeduration db2-fn:local-timezone() -PT5HI fn:lower-case fn:lower-case fn:lower-case(source-string) source-string source-string xs:string source-string source-string xs:string source-string "Wireless Router TB2561" fn:lower-case("wireless Router TB2561") "wireless router tb2561" SQL XML 211

220 fn:matches fn:matches fn:matches(source-string,pattern ),flags source-string source-string xs:string XPath pattern source-string 1 pattern flags pattern source-string : s m i x (.) s (.) (#x0a) (^) () ($) () m (^) ($) "a" "z" "A" "Z" / i / pattern (#x09#x0a#x0d #x20) x source-string source-string pattern true xs:boolean source-string pattern false v pattern (^) ($) source-string pattern source-string pattern v pattern (^) source-string source-string source-string 1 pattern source-string pattern 212 IBM i: SQL XML

221 v pattern ($) source-string source-string source-string 1 pattern source-string pattern v m source-string source-string pattern source-string pattern : "ac" "bd" "abbcacadbdcd" fn:matches("abbcacadbdcd","(ac) (bd)") true : "ac" "bd" "bd" (^) ($) fn:matches("bd","^(ac) (bd)$") true fn:max fn:max fn:max(sequence-expression) sequence-expression v v String v xs:date v xs:datetime v xs:time v xs:daytimeduration v xs:yearmonthduration xs:untypedatomic xs:double SQL XML 213

222 sequence-expression sequence-expression xs:anyatomictype sequence-expression sequence-expression sequence-expression 1 sequence-expression NaN NaN (500, 1.0E2, 40.5) double SELECT * FROM XMLTABLE (XMLNAMESPACES (DEFAULT ), $d PASSING XMLPARSE(DOCUMENT <x xmlns=" <b>500</b><b>1.0e2</b><b>40.5</b></x> ) AS "d" COLUMNS RES DOUBLE PATH fn:max(x/b) ) X xs:double 5.0E2 xs:double SQL double fn:min fn:min fn:min(sequence-expression) sequence-expression v v String v xs:date v xs:datetime v xs:time v xs:daytimeduration v xs:yearmonthduration xs:untypedatomic xs:double sequence-expression sequence-expression xs:anyatomictype sequence-expression sequence-expression 214 IBM i: SQL XML

223 sequence-expression 1 sequence-expression NaN NaN (500, 1.0E2, 40.5) SELECT * FROM XMLTABLE (XMLNAMESPACES (DEFAULT ), $d PASSING XMLPARSE(DOCUMENT <x xmlns=" <b>500</b><b>1.0e2</b><b>40.5</b></x> ) AS "d" COLUMNS RES DOUBLE PATH fn:min(x/b) ) X xs:double 4.05E1 xs:double SQL double fn:minutes-from-datetime fn:minutes-from-datetime xs:datetime fn:minutes-from-datetime(datetime-value) datetime-value datetime datetime-value xs:datetime datetime-value xs:datetime xs:integer 0 59 datetime-value datetime-value UTC :42 datetime fn:minutes-from-datetime(xs:datetime(" t09:42:00-08:00")) 42 fn:minutes-from-duration fn:minutes-from-duration fn:minutes-from-duration(duration-value) duration-value SQL XML 215

224 duration-value xs:daytimeduration xs:durationxs:yearmonthduration duration-value v duration-value xs:daytimeduration xs:duration xs:integer () xs:daytimeduration duration-value duration-value v duration-value xs:yearmonthduration 0 v duration-value xs:daytimeduration duration-value ((S mod 3600) idiv 60) () S xs:daytimeduration duration-value fn:minutes-from-duration(xs:daytimeduration("p2dt16h93m")) P2DT17H33M fn:minutes-from-time fn:minutes-from-time xs:time fn:minutes-from-time(time-value) time-value time-value xs:time time-value xs:time xs:integer 0 59 time-value time-value UTC-8 8:59 fn:minutes-from-time(xs:time("08:59:00-08:00")) IBM i: SQL XML

225 fn:month-from-date fn:month-from-date xs:date fn:month-from-date(date-value) date-value date-value xs:date date-value xs:date xs:integer 1 12 date-value date-value fn:month-from-date(xs:date(" ")) 12 fn:month-from-datetime fn:month-from-datetime xs:datetime fn:month-from-datetime(datetime-value) datetime-value datetime datetime-value xs:datetime datetime-value xs:datetime xs:integer 1 12 datetime-value datetime-value UTC :15 datetime fn:month-from-datetime(xs:datetime(" t08:15:00-08:00")) 10 SQL XML 217

226 fn:months-from-duration fn:months-from-duration fn:months-from-duration(duration-value) duration-value duration-value xs:daytimeduration xs:durationxs:yearmonthduration duration-value v duration-value xs:duration xs:yearmonthduration xs:integer () xs:yearmonthduration duration-value duration-value v duration-value xs:daytimeduration 0 v duration-value xs:yearmonthduration duration-value duration-value 12 () 20 5 fn:months-from-duration(xs:duration("p20y5m")) 5 yearmonthduration fn:months-from-duration(xs:yearmonthduration("-p9y13m")) P10Y1M xquery fn:months-from-duration(xs:duration("p14y11m40dt13h")) 11 fn:name fn:name 218 IBM i: SQL XML

227 fn:name( ) node node fn:name xs:string node v node node node v node v node () xs:string b CLOB 1 SELECT * FROM XMLTABLE ( declare namespace ns1=" $d/x/ns1:b/fn:name(.) PASSING XMLPARSE(DOCUMENT <x xmlns:n=" <n:b><n:c></n:c></n:b></x> ) AS "d") COLUMNS COL1 CLOB(1K) PATH(.) ) X "n:b" fn:name() CUSTOMER 1001 <customerinfo xmlns=" Cid="1001"> <name>kathy Smith</name> <addr country="canada"> <street>25 EastCreek</street> <city>markham</city> <prov-state>ontario</prov-state> <pcode-zip>n9c 3T6</pcode-zip> </addr> <phone type="work"> </phone> </customerinfo> SQL XML 219

228 SELECT * FROM CUSTOMER, XMLTABLE( declare default element namespace " $X/customerinfo/phone/fn:name() PASSING INFO AS "X") COLUMNS RESULT1 CLOB(1K) PATH. ) X WHERE CID=1001 "phone" fn:normalize-space fn:normalize-space fn:normalize-space( ) source-string source-string source-string xs:string source-string fn:normalize-space fn:string xs:string source-string xs:string v v 1 (U+0020) (U+0020) (U+000D) (U+000A) (U+0009) source-string "a b c d " fn:normalize-space(" abcd") "abcd" fn:not fn:not true false false fn:not true fn:not(sequence-expression) 220 IBM i: SQL XML

229 sequence-expression xs:boolean sequence-expression false true sequence-expression true false true fn:not("a"="b") false fn:not("false") fn:position fn:position position fn:position() xs:integer 1 position v descendant descendant-or-self v v fn:position I $x/a[$y/c/fn:position()] $x/a[(b/c)[2]] <c> 2 XML 1 <x xmlns=" SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT ), $d/x/b/c[fn:position()=2] PASSING XMLPARSE(DOCUMENT <x xmlns=" <b><c>x</c><c>y</c><c>z</c></b></x> ) AS "d" COLUMNS RESULT XML PATH. ) X <c>y</c> SQL XML 221

230 <a><b><c> XML SELECT * FROM XMLTABLE(. PASSING XMLPARSE(DOCUMENT <a><b><c>c1</c></b><b><c>c2</c><c>c3</c></b></a> ) COLUMNS RESULT_POS XML PATH /a/b/c/fn:position() ) X "123" fn:replace fn:replace fn:replace(source-string,pattern,replacement-string ),flags source-string source-string xs:string Xpath pattern source-string 1 pattern replacement-string source-string pattern replacement-string xs:string $ $ flags pattern source-string : s m i x (.) s (.) (#x0a) (^) () ($) () m (^) ($) "a" "z" "A" "Z" / i / pattern (#x09#x0a#x0d #x20) x 222 IBM i: SQL XML

231 source-string source-string xs:string v source-string pattern source-string 2 pattern source-string pattern pattern source-string source-string pattern pattern v pattern source-string replacement-string pattern source-string source-string pattern source-string "a" "b" "*@" fn:replace("abbcacadbdcd","(a(.)) (b(.))","*@") "*@*@*@*@*@cd" fn:round fn:round fn:round(numeric-value) numeric-value numeric-value v xs:double v xs:decimal v xs:integer v numeric-value numeric-value numeric-value v numeric-value xs:doublexs:decimal xs:integer numeric-value v numeric-value xs:doublexs:decimal xs:integer numeric-value SQL XML 223

232 numeric-value : 0.5 fn:round(0.5) 1 : (-1.5) fn:round(-1.5) -1 fn:seconds-from-datetime fn:seconds-from-datetime xs:datetime fn:seconds-from-datetime(datetime-value) datetime-value datetime datetime-value xs:datetime datetime-value xs:datetime xs:decimal 0 60 datetime-value () datetime-value UTC :16:23 datetime fn:seconds-from-datetime(xs:datetime(" t14:16:23-08:00")) 23 UTC :16:20.43 datetime fn:seconds-from-datetime(xs:datetime(" t09:16:20.43z")) fn:seconds-from-duration fn:seconds-from-duration 224 IBM i: SQL XML

233 fn:seconds-from-duration(duration-value) duration-value duration-value xs:daytimeduration xs:durationxs:yearmonthduration duration-value v duration-value xs:daytimeduration xs:duration xs:decimal xs:daytimeduration duration-value () duration-value v duration-value xs:yearmonthduration xs:integer 0 v duration-value xs:daytimeduration duration-value () (S mod 60) S xs:daytimeduration duration-value () fn:seconds-from-duration(xs:daytimeduration("pt150.5s")) PT2M30.5S 30.5 fn:seconds-from-time fn:seconds-from-time xs:time fn:seconds-from-time(time-value) time-value time-value xs:time time-value xs:time xs:decimal 0 60 time-value () time-value SQL XML 225

234 UTC-8 08:59:59 fn:seconds-from-time(xs:time("08:59:59-08:00")) 59 fn:starts-with fn:starts-with fn:starts-with(string,substring) string substring string xs:string string string substring substring xs:string xs:boolean true v substring string v substring false 'Test literal' 'lite' fn:starts-with( Test literal, lite ) false fn:string fn:string fn:string( ) value value value value fn:string 226 IBM i: SQL XML

235 value xs:string v value value v value value xs:string value 123 fn:string(xs:integer(123)) '123' fn:string-length fn:string-length fn:string-length( ) source-string source-string source-string xs:string source-string source-string xs:integer source-string xs:integer 0 source-string fn:string-length "Test literal" fn:string-length("test literal") 12 fn:substring fn:substring fn:substring(source-string,start ),length SQL XML 227

236 source-string source-string xs:string start length source-string source-string 1 start<=0 start 1 start xs:double length source-string start+length-1 source-string length (source-string )-start+1 length xs:double source-string start length source-string xs:string source-string 'Test literal' 6 7 fn:substring( Test literal,6,7) 'literal' fn:sum fn:sum fn:sum(sequence-expression) sequence-expression v xs:double v xs:decimal v xs:integer v v xs:daytimeduration v xs:yearmonthduration v xs:untypedatomic xs:untypedatomic xs:double 228 IBM i: SQL XML

237 sequence-expression sequence-expression sequence-expression sequence-expression sequence-expression fn:sum 0.0E0 (500, 1.0E2, 40.5) fn:sum((500, 1.0E2, 40.5)) xs:double 6.405E2 fn:timezone-from-date fn:timezone-from-date xs:date fn:timezone-from-date(date-value) date-value date-value xs:date date-value xs:date xs:daytimeduration -PT14H PT14H () date-value UTC date-value UTC fn:timezone-from-date(xs:date(" :00")) -PT8H fn:timezone-from-datetime fn:timezone-from-datetime xs:datetime fn:timezone-from-datetime(datetime-value) datetime-value datetime SQL XML 229

238 datetime-value xs:datetime datetime-value xs:datetime xs:daytimeduration -PT14H PT14H () datetime-value UTC datetime-value UTC :30 datetime fn:timezone-from-datetime(xs:datetime(" t07:30:00-08:00")) -PT8H UTC+10: :30 datetime fn:timezone-from-datetime(xs:datetime(" t14:30:00+10:30")) PT10H30M fn:timezone-from-time fn:timezone-from-time xs:time fn:timezone-from-time(time-value) time-value time-value xs:time time-value xs:time xs:daytimeduration -PT14H PT14H () time-value UTC time-value UTC-5 12 fn:timezone-from-time(xs:time("12:00:00-05:00")) -PT5H 230 IBM i: SQL XML

239 1:00 fn:timezone-from-time(xs:time("13:00:00")) fn:tokenize fn:tokenize fn:tokenize( source-string, pattern ), flags source-string source-string xs:string XPath pattern source-string pattern 1 flags pattern source-string : s m i x (.) s (.) (#x0a) (^) () ($) () m (^) ($) "a" "z" "A" "Z" / i / pattern (#x09#x0a#x0d #x20) x source-string source-string xs:string v source-string pattern v pattern source-string source-string pattern pattern SQL XML 231

240 v pattern v pattern source-string v source-string pattern 2 v pattern source-string pattern source-string source-string pattern source-string (?) "?A?B?C?D?" fn:tokenize("?a?b?c?d?","\?") ("", "A", "B", "C", "D", "") fn:translate fn:translate fn:translate(source-string,original-string,replacement-string) source-string source-string xs:string original-string original-string xs:string replacement-string original-string replacement-string xs:string replacement-string original-string replacement-string source-string xs:string 232 IBM i: SQL XML

241 v original-string source-string source-string original-string replacement-string original-string replacement-string original-string original-string replacement-string source-string original-string original-string replacement-string v original-string source-string source-string "aaa" a A - fn:translate("---aaa---","a-","a") "AAA" fn:upper-case fn:upper-case fn:upper-case(source-string) source-string source-string xs:string source-string source-string xs:string source-string 'Test literal 1' fn:upper-case("test literal 1") "TEST LITERAL 1" "ii" fn:upper-case("ıi") "II" SQL XML 233

242 fn:year-from-date fn:year-from-date xs:date fn:year-from-date(date-value) date-value date-value xs:date date-value xs:date xs:integer date-value date-value fn:year-from-date(xs:date(" ")) 2009 fn:year-from-datetime fn:year-from-datetime xs:datetime fn:year-from-datetime(datetime-value) datetime-value datetime datetime-value xs:datetime datetime-value xs:datetime xs:integer datetime-value datetime-value UTC :00 datetime fn:year-from-datetime(xs:datetime(" t08:00:00-08:00")) IBM i: SQL XML

243 fn:years-from-duration fn:years-from-duration fn:years-from-duration(duration-value) duration-value duration-value xs:daytimeduration xs:durationxs:yearmonthduration duration-value v duration-value xs:yearmonthduration xs:duration xs:integer xs:yearmonthduration duration-value duration-value v duration-value xs:daytimeduration xs:integer 0 v duration-value xs:yearmonthduration duration-value () xs:yearmonthduration duration-value fn:years-from-duration(xs:duration("-p4y11m320d")) fn:years-from-duration(xs:yearmonthduration("p9y13m")) P10Y1M IBM IBM SQL XML 235

244 IBM IBM IBM IBM i: SQL XML

245 IBM IBM IBM IBM IBM IBM IBM () IBM IBM IBM Web Web Web IBM Web IBM (i) ( ) (ii) IBM Corporation Software Interoperability Coordinator, Department YBWA 3605 Highway 52 N Rochester, MN U.S.A. Copyright IBM Corp. 2012,

246 IBM IBM IBM IBM IBM IBM IBM : IBM IBM IBM () (). IBM Corp. Copyright IBM Corp IBM i: SQL XML

247 SQL IBM i IBMIBM ibm.com International Business Machines Corporation IBM IBM AdobeAdobe PostScriptPostScript Adobe Systems Incorporated Linux Linus Torvalds MicrosoftWindowsWindows NT Windows Microsoft Corporation UNIX The Open Group IBM : IBM () ( ) : IBM IBM IBM IBM 239

248 240 IBM i: SQL XML

249 ,,,, XML XML db2-xdb:column 84 db2-xdb:condition 92 db2-xdb:contenthandling 95 db2-xdb:defaultsqlschema 76 db2-xdb:expression 89 db2-xdb:locationpath 86 db2-xdb:normalization 99 db2-xdb:order 102 db2-xdb:rowset 78 db2-xdb:rowsetmapping 105 db2-xdb:rowsetoperationorder 108 db2-xdb:table 82 db2-xdb:truncate , 119, 120, 122, 123, 125 CDATA 110 NULL 110 XML XML abs 191 adjust-datetime-to-timezone 194 adjust-date-to-timezone 192 adjust-time-to-timezone 195 boolean 197 compare 197 concat 198 contains 199 count 199 current-date 200 current-datetime 200 current-local-date 201 current-local-datetime 201 current-local-time 201 current-time 202 datetime 203 days-from-duration 204 day-from-date 203 day-from-datetime 204 distinct-values 205 exists 206 hours-from-datetime 207 hours-from-duration 207 hours-from-time 208 implicit-timezone 208 last 209 local-name 210 local-timezone 211 lower-case 211 matches 212 max 213 min 214 minutes-from-datetime 215 minutes-from-duration 215 minutes-from-time 216 months-from-duration 218 month-from-date 217 month-from-datetime 217 name 219 normalize-space 220 not 220 position 221 round 223 seconds-from-datetime 224 seconds-from-duration 225 seconds-from-time 225 starts-with 226 () string-length 227 substring 227 sum 228 timezone-from-date 229 timezone-from-datetime 229 timezone-from-time 230 tokenize 231 translate 232 upper-case 233 years-from-duration 235 year-from-date 234 year-from-datetime 234 XPath 171 XML 16 XPath XML 40 XML 40 XML 16 XML XPath Copyright IBM Corp. 2012,

250 () attribute 174 child 174 descendant 174 descendant-or-self 174 parent 174 self 174 (QName) XPath XPath xs:anyatomictype 152 xs:anysimpletype 151 xs:anytype 151 xs:boolean 155 xs:date 156 xs:datetime 157 xs:daytimeduration 160 xs:decimal 153 xs:double 154 xs:duration 159 xs:integer 154 xs:string 153 () xs:time 157 xs:untyped 152 xs:untypedatomic 152 xs:yearmonthduration 161 XPath 168 / XPath 147 XML 7 8 XML 9 XML 8 XML 10 XSLT CCSID 72 XML 32 XML XML 67, XML 127 xs:anyatomictype 152 xs:anysimpletype 151 xs:anytype 151 xs:boolean 155 xs:date 156 xs:datetime 157 xs:daytimeduration 160 xs:decimal 153 xs:double 154 xs:duration 159 xs:integer 154 xs:string 153 xs:time 157 xs:untyped 152 xs:untypedatomic 152 xs:yearmonthduration XML 136 XPath XPath 166 XML JDBC SQLJ 63 XML XSLT text 141 ID XML 62 XML SQL/XML XPath 182 XML XPath 165 CDATA IBM i: SQL XML

251 XPath 170 XML 117 XML XML 62 XML , XML 117 A abs 191 adjust-datetime-to-timezone 194 adjust-date-to-timezone 192 adjust-time-to-timezone 195 anyatomictype 152 anysimpletype 151 anytype 151 attribute 174 axis step 173 B boolean 197 boolean 155 C child 174 compare 197 concat 198 contains 199 count 199 current-date 200 current-datetime 200 current-local-date 201 current-local-datetime 201 current-local-time 201 current-time 202 D data 202 date 156 datetime 203 datetime 157 days-from-duration 204 daytimeduration 160 day-from-date 203 day-from-datetime 204 DB2 XPath timezone-from-datetime 229 decimal 153 DECOMP_CONTENT 109 DECOMP_DOCUMENTID 109 DECOMP_ELEMENTID 109 descendant 174 descendant-or-self 174 distinct-values 205 double 154 duration 159 E exists 206 F filter expression 179 H hours-from-datetime 207 hours-from-duration 207 hours-from-time 208 I ID 137 implicit-timezone 208 integer 154 J Java Database Connectivity (JDBC) XML 63 L last 209 local-name 210 local-timezone 211 lower-case 211 M matches 212 max 213 min 214 minutes-from-datetime 215 minutes-from-duration 215 minutes-from-time 216 months-from-duration 218 month-from-date 217 month-from-datetime 217 N name 219 normalize-space 220 not 220 NULL SQL 110 P parent 174 position 221 R replace 222 round 223 S seconds-from-datetime 224 seconds-from-duration 225 seconds-from-time 225 self

252 SQLJ XML 63 starts-with 226 string 226 string 153 string-length 227 substring 227 sum 228 T time 157 timezone-from-date 229 timezone-from-datetime 229 timezone-from-time 230 tokenize 231 translate 232 U untyped 152 untypedatomic 152 upper-case 233 X XML XML 9 XML 8 XML 10 XSLT XML () () XSLTRANSFORM 21, 23, 25, 29 CCSID SQL/XML 18 XML XML (XSR) 41 XML 7 8 XML 9 XML 8 XML 10 XSLT 12 XML XML 15 XMLTABLE 33, 34, 35, 37, 40 XML 144 XML 62 JDBC SQLJ 63 XML 62 XML XML 41 XML () () Uniform Resource Identifier (URI) 41 XML (XML schema) 162 XML (list) 162 XML 62 CCSID 72 CCSID XML 32 XML CCSID XML 136 XML 149 XML 33 XML XML 72 XML db2-xdb:column 84 db2-xdb:condition 92 db2-xdb:contenthandling 95 db2-xdb:defaultsqlschema 76 db2-xdb:expression 89 db2-xdb:locationpath 86 db2-xdb:normalization 99 db2-xdb:order 102 db2-xdb:rowset 78 db2-xdb:rowsetmapping 105 db2-xdb:rowsetoperationorder 108 db2-xdb:table IBM i: SQL XML

253 XML () () db2-xdb:truncate 103 XML SQL XML 117 CDATA 110 NULL 110 XML XML XML XML 73 XML 170 XML XMLAGG XML 18 XMLATTRIBUTES XML 18 XMLCOMMENT XML 18 XMLDOCUMENT XML 18 XMLELEMENT XML 18 XMLFOREST XML 18 XMLGROUP XML 18 XMLNAMESPACES XML 18 XMLPARSE 16 XMLPI XML 18 XMLROW XML 18 XMLSERIALIZE 30 XMLTABLE 33, 34, 35, 37, 40 XMLTEXT XML 18 XPath XML 149 XPath abs 191 adjust-datetime-to-timezone 194 adjust-date-to-timezone 192 adjust-time-to-timezone 195 boolean 197 compare 197 concat 198 contains 199 count 199 current-date 200 current-datetime 200 current-local-date 201 current-local-datetime 201 current-local-time 201 current-time 202 datetime 203 days-from-duration 204 day-from-date 203 day-from-datetime 204 distinct-values 205 exists 206 hours-from-datetime 207 XPath () hours-from-duration 207 hours-from-time 208 implicit-timezone 208 last 209 local-name 210 local-timezone 211 lower-case 211 matches 212 max 213 min 214 minutes-from-datetime 215 minutes-from-duration 215 minutes-from-time 216 months-from-duration 218 month-from-date 217 month-from-datetime 217 name 219 normalize-space 220 not 220 position 221 round 223 seconds-from-datetime 224 seconds-from-duration 225 seconds-from-time 225 starts-with 226 string-length 227 substring 227 sum 228 timezone-from-date 229 timezone-from-datetime 229 timezone-from-time 230 tokenize 231 translate 232 upper-case 233 years-from-duration 235 year-from-date 234 year-from-datetime 234 XPath 171 XPath XPath 177 XPath 151 XPath 165 XSLT , 25, 26 XSLTRANSFORM XML 18 xs:anyatomictype 152 xs:anysimpletype 151 xs:anytype 151 xs:boolean

254 xs:date 156 xs:datetime 157 xs:daytimeduration 160 xs:decimal 153 xs:double 154 xs:duration 159 xs:integer 154 xs:string 153 xs:time 157 xs:untyped 152 xs:untypedatomic 152 xs:yearmonthduration 161 Y yearmonthduration 161 years-from-duration 235 year-from-date 234 year-from-datetime IBM i: SQL XML

255

256 : 5770-SS1 Printed in Japan