®
IBM Software Group
© 2006 IBM Corporation
The XMLTABLE Function
Matthias Nicola, Li Chen, Jinfeng NiIBM Silicon Valley Lab, San Jose, USA
IBM Software Group | DB2 Information Management Software
2
Topics
� Overview
� XMLTABLE Basics
� Corner Cases and Best Practices
� Splitting XML Documents
� Reassembling XML Documents
� Using XMLTABLE with Namespaces, Cursors
For detailed written explanations of XMLTABLE queries, see:�http://tinyurl.com/pureXML�http://www.ibm.com/developerworks/db2/library/techarticle/dm-0708nicola/�http://www.ibm.com/developerworks/db2/library/techarticle/dm-0709nicola/
IBM Software Group | DB2 Information Management Software
3
The SQL/XML Standard defines:
� XML Data Type (its semantics, not its storage)
� XML publishing functions (relational data → XML)
�XMLELEMENT, XMLATTRIBUTES, XMLAGG, …
� Conversion: XML type ↔ char/varchar/clob
�XMLSERIALIZE, XMLPARSE, XMLCAST
� Integration of SQL and XQuery/XPath languages
�XMLQUERY, XMLEXISTS, XMLTABLE
� Other functions, for schema validation, etc.
�XMLVALIDATE
IBM Software Group | DB2 Information Management Software
4
Sample Scenariocreate table dept(deptID char(8), deptdoc xml)
……
……
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
…
deptdoc XMLdeptID char(8)
IBM Software Group | DB2 Information Management Software
5
XMLTABLE: A first example
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
SELECT X.* FROM dept,XMLTABLE (‘$DEPTDOC/dept/employee’
COLUMNS empID INTEGER PATH ‘@id’,firstname VARCHAR(30) PATH ‘name/first’,lastname VARCHAR(30) PATH ‘name/last’,office INTEGER PATH ‘office’) AS X
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
relational result set
XML input
IBM Software Group | DB2 Information Management Software
6
XMLTABLE: A first example
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
SELECT X.* FROM dept,XMLTABLE (‘$DEPTDOC/dept/employee’
COLUMNS empID INTEGER PATH ‘@id’,firstname VARCHAR(30) PATH ‘name/first’,lastname VARCHAR(30) PATH ‘name/last’,office INTEGER PATH ‘office’) AS X
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
relational result set
Row-generating expression
Columndefinitions
XML input
IBM Software Group | DB2 Information Management Software
7
Two ways to specify the input column
SELECT X.* FROM dept,XMLTABLE (‘$DEPTDOC/dept/employee’
COLUMNS empID INTEGER PATH ‘@id’,firstname VARCHAR(30) PATH ‘name/first’,lastname VARCHAR(30) PATH ‘name/last’,office INTEGER PATH ‘office’) AS X
SELECT X.* FROM dept,XMLTABLE (‘$d/dept/employee’ passing deptdoc as "d"
COLUMNS empID INTEGER PATH ‘@id’,firstname VARCHAR(30) PATH ‘name/first’,lastname VARCHAR(30) PATH ‘name/last’,office INTEGER PATH ‘office’) AS X
DB2 9.5+ for LUW
DB2 9 for z/OS, DB2 9.x for LUW
IBM Software Group | DB2 Information Management Software
8
XMLTABLE: Return XML in tabular format
SELECT X.* FROM dept,XMLTABLE (‘$d/dept/employee’ passing deptdoc as “d”
COLUMNS empID INTEGER PATH ‘@id’,firstname VARCHAR(30) PATH ‘name/first’,lastname VARCHAR(30) PATH ‘name/last’,office INTEGER PATH ‘office’) AS X
� The XMLTABLE function is invoked once for each row.
� For each row (document):
� XMLTABLE produces 0, 1, or multiple results rows.
� The embedded XPath (green) is evaluated, and returns asequence of 0 to n items
� For each item the COLUMNS clause generates column values
� XQueries in the PATH definitions must not return more than 1 item !
IBM Software Group | DB2 Information Management Software
9
XMLTABLE: Multiple values per cell?
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
DID officelastname
SELECT X.* FROM dept,XMLTABLE (‘$DEPTDOC/dept’COLUMNS
DID INTEGER PATH ‘@DID’,lastname VARCHAR(30) PATH ‘employee/name/last’,office INTEGER PATH ‘employee/office’) AS X
Error.
SQL16003N An expression of data type "( item(), item()+ )" cannot be usedwhen the data type "INTEGER" is expected in the context.
Solution: Iterate over /dept/employee, as in the previous examples!
IBM Software Group | DB2 Information Management Software
10
XMLTABLE: Single vs Multi Occurrences
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
101
101
DID
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
SELECT X.* FROM dept,XMLTABLE (‘$DEPTDOC/dept/employee’
COLUMNS DID INTEGER PATH ‘../@DID’,empID INTEGER PATH ‘@id’,firstname VARCHAR(30) PATH ‘name/first’,lastname VARCHAR(30) PATH ‘name/last’,office INTEGER PATH ‘office’) AS X
The "DID" gets repeated for every employee.
IBM Software Group | DB2 Information Management Software
11
XMLTABLE: Missing elements
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
</employee>
<employee id="902">
<name>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
101
101
DID
216PanNULL902
NULLDoeJohn901
officelastnamefirstnameempID
SELECT X.* FROM dept,XMLTABLE (‘$DEPTDOC/dept/employee’
COLUMNS DID INTEGER PATH ‘../@DID’,empID INTEGER PATH ‘@id’,firstname VARCHAR(30) PATH ‘name/first’,lastname VARCHAR(30) PATH ‘name/last’,office INTEGER PATH ‘office’) AS X
Missing elements lead to NULLs in the result.Can use DEFAULT clause too.
IBM Software Group | DB2 Information Management Software
12
XMLTABLE: Apply SQL aggregation andGROUPing to XML data:
<dept DID="101">
<employee id="901">
<name>John Doe</name>
<office>344</office>
</employee>
<employee id="902">
<name>Peter Pan</name>
<office>216</office>
</employee>
<employee id="901">
<name>Mary Poppins</name>
<office>216</office>
</employee>
</dept>
2216
1344
cntoffice
SELECT X.office, COUNT(X.empID) as cntFROM dept,
XMLTABLE (‘$DEPTDOC/dept/employee’COLUMNS empID INTEGER PATH ‘@id’,office INTEGER PATH ‘office’) AS X
GROUP BY office;
dept
IBM Software Group | DB2 Information Management Software
13
XMLTABLE: 2 ways to add row-filtering predicates
SELECT X.* FROM dept,XMLTABLE (‘$d/dept[@DID = 101]/employee’ passing deptdoc as “d”
COLUMNS empID INTEGER PATH ‘@id’,office INTEGER PATH ‘office’) AS X;
SELECT X.* FROM dept,XMLTABLE (‘$d/dept/employee’ passing deptdoc as “d”
COLUMNS empID INTEGER PATH ‘@id’,office INTEGER PATH ‘office’) AS X
WHERE XMLEXISTS(‘$d/dept[@DID = 101]’ passing deptdoc as “d“);
Predicate in the row-generating expression:
Predicate in XMLEXISTS:
IBM Software Group | DB2 Information Management Software
14
XMLTABLE: 2 ways to add row-filtering predicates
SELECT X.* FROM dept,XMLTABLE (‘$d/dept/employee[@id = 901]’ passing deptdoc as “d”
COLUMNS empID INTEGER PATH ‘@id’,office INTEGER PATH ‘office’) AS X;
SELECT X.* FROM dept,XMLTABLE (‘$d/dept/employee’ passing deptdoc as “d”
COLUMNS empID INTEGER PATH ‘@id’,office INTEGER PATH ‘office’) AS X
WHERE XMLEXISTS(‘$d/dept/employee[id = 901]’ passing deptdoc as “d“);
<dept DID="101">
<employee id="901">
<name>John Doe</name>
<office>344</office>
</employee>
<employee id="902">
<name>Peter Pan</name>
<office>216</office>
</employee>
</dept>
344DoeJohn901
officelastnamefirstnameempID
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
!
This query filters across documents, but not within documents!
This query filters across documents and within documents!
IBM Software Group | DB2 Information Management Software
15
SQL Access to XML Data
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
CREATE VIEW empview(empid, firstname, lastname, office)
AS SELECT X.* FROM dept,
XMLTABLE ('$DEPTDOC/dept/employee' COLUMNS
empid INTEGER PATH '@id',
firstname VARCHAR(30) PATH 'name/first',
lastname VARCHAR(30) PATH 'name/last',
office INTEGER PATH 'office') AS X
216PanPeter902
344DoeJohn901
officelastnamefirstnameempid
CREATE TABLE dept(deptdoc XML);
SELECT lastname, officeFROM empviewWHERE empid = 901;
CREATE INDEX idx1 ON dept(deptdoc) GENERATE KEYS USING XMLPATTERN '/dept/employee/@id' AS SQL DOUBLE;
IBM Software Group | DB2 Information Management Software
16
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
XMLTABLE: Flexible Transformation
PeterNULL902
NULLJohn901
R216R344empID
SELECT X.* FROM dept,XMLTABLE (‘$DEPTDOC/dept/employee’COLUMNS
empID INTEGER PATH ‘@id’,R344 VARCHAR(30) PATH ‘.[office=344]/name/first’,R216 VARCHAR(30) PATH ‘.[office=216]/name/first’
) AS X
Transpose your data. Conditionally mapelements to one column or the other.
IBM Software Group | DB2 Information Management Software
17
Topics
� Overview
� XMLTABLE Basics
� Corner Cases and Best Practices
� Splitting XML Documents
� Reassembling XML Documents
� Using XMLTABLE with Namespaces, Cursors
IBM Software Group | DB2 Information Management Software
18
XMLTABLE: Simple Shredding, "Table to Table"
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
INSERT INTO dept2SELECT X.* FROM dept,XMLTABLE ('$DEPTDOC/dept/employee'
COLUMNS empID INTEGER PATH '@id',firstname VARCHAR(30) PATH 'name/first',lastname VARCHAR(30) PATH 'name/last',office INTEGER PATH 'office') AS X
CREATE TABLE dept2 (empid int, firstname varchar(30),lastname varchar(30), office int);
dept2
dept
IBM Software Group | DB2 Information Management Software
19
XMLTABLE: Shredding, "Parameter to Table"
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
INSERT INTO dept2SELECT X.* FROMXMLTABLE ('$d/dept/employee'
passing cast(? as XML) as "d"COLUMNS empID INTEGER PATH '@id',firstname VARCHAR(30) PATH 'name/first',lastname VARCHAR(30) PATH 'name/last',office INTEGER PATH 'office') AS X
dept2
input
CREATE TABLE dept2 (empid int, firstname varchar(30),lastname varchar(30), office int);
IBM Software Group | DB2 Information Management Software
20
Shredding into 2 or more target tables…
<dept DID="101" DNAME="Sales">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
departments
Sales101
DNAMEDID
101
101
DID
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
employees
IBM Software Group | DB2 Information Management Software
21
CREATE PROCEDURE insertXML(IN doc XML)BEGIN
INSERT INTO departmentsSELECT X.* FROM
XMLTABLE ('$d/dept' passing doc as "d" COLUMNS
DID INTEGER PATH '@DID',DNAME VARCHAR(30) PATH '@DNAME') AS X ;
INSERT INTO employeesSELECT X.* FROM
XMLTABLE ('$d/dept/employee' passing doc as "d" COLUMNS
DID INTEGER PATH '../@DID',empID INTEGER PATH '@id',firstname VARCHAR(30) PATH 'name/first',lastname VARCHAR(30) PATH 'name/last',office INTEGER PATH 'office') AS X ;
END#
Shredding into 2 or more target tables…<dept DID="101" DNAME="Sales">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
departments
Sales101
DNAMEDID
Sales101
DNAMEDID
101
101
DID
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
101
101
DID
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
employees
IBM Software Group | DB2 Information Management Software
22
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
INSERT INTO dept2SELECT X.DID, X.empID, XMLDOCUMENT(X.employee) FROMXMLTABLE ('$d/dept/employee' passing cast(? as XML) as "d"
COLUMNS DID INTEGER PATH ‘../@DID’,empID INTEGER PATH ‘@id’,employee XML PATH ‘.’) AS X
CREATE TABLE dept2 (DID int, empID int, employee XML);
dept2
101
101
DID
<employee id="902"><name>
<first>Peter</first><last>Pan</last>
</name><office>216</office
</employee>
902
<employee id="901"><name>
<first>John</first><last>Doe</last>
</name><office>344</office>
</employee>
901
employeeempID
XMLTABLE: Splitting DocumentsDB2 for z/OS, DB2 for LUW
IBM Software Group | DB2 Information Management Software
23
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
INSERT INTO dept2SELECT X.* FROMXMLTABLE ('$d/dept/employee' passing cast(? as XML) as "d"
COLUMNS DID INTEGER PATH ‘../@DID’,empID INTEGER PATH ‘@id’,employee XML PATH ‘document{.}’) AS X
dept2
101
101
DID
<employee id="902"><name>
<first>Peter</first><last>Pan</last>
</name><office>216</office
</employee>
902
<employee id="901"><name>
<first>John</first><last>Doe</last>
</name><office>344</office>
</employee>
901
employeeempID
XMLTABLE: Splitting DocumentsDB2 for LUW
CREATE TABLE dept2 (DID int, empID int, employee XML);
IBM Software Group | DB2 Information Management Software
24
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
XMLTABLE: Ordinality
2
1
seqNo
101
101
DID
<employee id="902"><name>
<first>Peter</first><last>Pan</last>
</name><office>216</office
</employee>
902
<employee id="901"><name>
<first>John</first><last>Doe</last>
</name><office>344</office>
</employee>
901
employeeempID
INSERT INTO dept2SELECT X.* FROM dept,XMLTABLE (‘$DEPTDOC/dept/employee’
COLUMNS seqNo FOR ORDINALITY,DID INTEGER PATH ‘../@DID’,empID INTEGER PATH ‘@id’,employee XML PATH ‘document{.}’) AS “X”
CREATE TABLE dept2 (seqNo int, DID int, empID int, employee XML);
IBM Software Group | DB2 Information Management Software
25
Topics
� Overview
� XMLTABLE Basics
� Corner Cases and Best Practices
� Splitting XML Documents
� Reassembling XML Documents
� Using XMLTABLE with Namespaces, Cursors
IBM Software Group | DB2 Information Management Software
26
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
SELECT XMLELEMENT(name "dept", XMLATTRIBUTES(DID as "DID"),XMLAGG(employee) )
FROM dept2GROUP BY DID;
Reconstructing the split documents
CREATE TABLE dept2 (DID int, empID int, employee XML);
dept2
2
1
seqNo
101
101
DID
<employee id="902"><name>
<first>Peter</first><last>Pan</last>
</name><office>216</office
</employee>
902
<employee id="901"><name>
<first>John</first><last>Doe</last>
</name><office>344</office>
</employee>
901
employeeempID
IBM Software Group | DB2 Information Management Software
27
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>CREATE TABLE dept2 (DID int, empID int, employee XML);
dept2
2
1
seqNo
101
101
DID
<employee id="902"><name>
<first>Peter</first><last>Pan</last>
</name><office>216</office
</employee>
902
<employee id="901"><name>
<first>John</first><last>Doe</last>
</name><office>344</office>
</employee>
901
employeeempID
Reconstructing the split documentsSELECT XMLELEMENT(name "dept",
XMLATTRIBUTES(DID as "DID"),XMLAGG(employee ORDER BY seqNo) )
FROM dept2GROUP BY DID;
IBM Software Group | DB2 Information Management Software
28
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
SELECT XMLELEMENT(name "employee", XMLATTRIBUTES(empID as "id"),XMLELEMENT(name "name",
XMLELEMENT(name "first", firstname),XMLELEMENT(name "last", lastname) ),
XMLELEMENT(name "office", office) )FROM dept2;
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
dept2
Reconstructing the shredded documents
Nesting of SQL/XML construction functions in the SELECT clause matchesthe nesting of the XML elements and attributes in the constructed XML data.
IBM Software Group | DB2 Information Management Software
29
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
SELECT XMLQUERY('<employee id="{$EMPID}"><name>
<first>{$FIRSTNAME}</first>
<last>{$LASTNAME}</last>
</name>
<office>{$OFFICE}</office>
</employee>')
FROM dept2;
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
dept2
DB2 for LUW
Reconstructing the shredded documents (XQuery)
IBM Software Group | DB2 Information Management Software
30
Topics
� Overview
� XMLTABLE Basics
� Corner Cases and Best Practices
� Splitting XML Documents
� Reassembling XML Documents
� Using XMLTABLE with Namespaces, Cursors
IBM Software Group | DB2 Information Management Software
31
XMLTABLE over XML Data w/ NamespacesSELECT X.* FROM dept,
XMLTABLE ( '$DEPTDOC/dept/employee'COLUMNS empID INTEGER PATH ‘@id’,firstname VARCHAR(30) PATH ‘name/first’,lastname VARCHAR(30) PATH ‘name/last’,office INTEGER PATH ‘office’) AS X
officelastnamefirstnameempID
relational result set
<dept xmlns="http://www.myURI.com/"
DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
0 Record(s) selected.
IBM Software Group | DB2 Information Management Software
32
XMLNAMESPACESSELECT X.* FROM dept,
XMLTABLE (XMLNAMESPACES(default 'http://www.myURI.com/'),'$DEPTDOC/dept/employee'
COLUMNS empID INTEGER PATH ‘@id’,firstname VARCHAR(30) PATH ‘name/first’,lastname VARCHAR(30) PATH ‘name/last’,office INTEGER PATH ‘office’) AS X
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
relational result set
<dept xmlns="http://www.myURI.com/"
DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
IBM Software Group | DB2 Information Management Software
33
XMLNAMESPACESSELECT X.* FROM dept,
XMLTABLE (XMLNAMESPACES(default 'http://www.myURI.com/',
'http://www.namespace2.org/' as "ns" ),'$DEPTDOC/dept/ns:employee'
COLUMNS empID INTEGER PATH ‘@id’,firstname VARCHAR(30) PATH ‘name/first’,lastname VARCHAR(30) PATH ‘name/last’,office INTEGER PATH ‘ns:office’) AS X
216PanPeter902
344DoeJohn901
officelastnamefirstnameempID
relational result set
<dept xmlns="http://www.myURI.com/"
xmlns:p="http://namespace2.org/"
DID="101">
<p:employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<p:office>344</p:office>
</p:employee>
<p:employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<p:office>216</p:office>
</p:employee>
</dept>
IBM Software Group | DB2 Information Management Software
34
Defining a Cursor on XMLTABLE
Sample Stored Procedure
EMPID FIRSTNAME LASTNAME OFFICE
----------- -------------------- -------------------- -----------
901 John Doe 344
902 Peter Pan 216
Result set from calling the Stored Procedure: db2 “CALL SP_XT_Cursor”
<dept DID="101"><employee id="901">
<name><first>John</first><last>Doe</last>
</name><office>344</office>
</employee><employee id="902">
<name><first>Peter</first><last>Pan</last>
</name><office>216</office>
</employee></dept>
Sample document
CREATE PROCEDURE SP_XT_Cursor() DYNAMIC RESULT SETS 1
LANGUAGE SQL BEGIN DECLARE CURSOR1 CURSOR WITH RETURN FOR SELECT X.* FROM dept, XMLTABLE('$d/dept/employee' passing deptdoc as "d"
COLUMNSempID INTEGER PATH '@id',firstname VARCHAR(20) PATH 'name/first',lastname VARCHAR(20) PATH 'name/last',office INTEGER PATH 'office') AS X;
OPEN CURSOR1; END
IBM Software Group | DB2 Information Management Software
35
Summary: What did we learn?
� XMLTABLE is a SQL/XML table function
� Query XML data, return relational result sets
� Apply SQL functions & features on XMLTABLE columns as needed
� Careful with repeating elements!
� Iterate over the deepest repeating element that your query requires
� Predicates in XMLTABLE vs. XMLEXISTS
� XMLTABLE can split or shred XML documents
� Use SQL/XML to reconstruct split or shredded documents, if needed
� Use XMLNAMESPACES function inside XMLTABLE
� Can define cursors on XMLTABLE queries
IBM Software Group | DB2 Information Management Software
36
Questions?
[email protected]@[email protected]
db2 =>
IBM Software Group | DB2 Information Management Software
37
Backup Slides,Resources
38 Matthias Nicola, IBM SVL
Further Reading: Querying & Updating XML in DB2
� "XMLTABLE by Example", Part 1 & 2http://www.ibm.com/developerworks/db2/library/techarticle/dm-0708nicola/http://www.ibm.com/developerworks/db2/library/techarticle/dm-0709nicola/
� "Examples of Cascading XMLTABLE() in SQL/XML" http://www.ibm.com/developerworks/blogs/page/purexml?entry=examples_of_cascading_xmltable_in
� "pureXML™ in DB2 9: Which way to query your XML Data?"http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0606nicola/
� "Query DB2 XML Data with SQL"http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0603saracco2/
� "Query XML data that contains namespaces"http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0611saracco/
� “Update XML in DB2 9.5”:http://www.ibm.com/developerworks/db2/library/techarticle/dm-0710nicola/
� "Integration of SQL and XQuery in IBM DB2"http://www.research.ibm.com/journal/sj/452/ozcan.html
IBM Software Group | DB2 Information Management Software
39
�Comprehensive coverage ofpureXML in DB2 for Linux, UNIX, Windows and DB2 for z/OS
�http://tinyurl.com/pureXML
IBM Software Group | DB2 Information Management Software
40
XMLTable() – Ordinality Clause� Ordinality is a sequence number of the rows produced by XMLTable� Useful to indicate XML element order in relational format
� Example: “part” is a repeating element, “id” isn’t:
select t.*
from order, xmltable('$DOC/order/part'
columns
seqno for ordinality
oid integer path '../@oid'
Pid char(10) path 'pid') as t
� Result:
seqno oid pid
------------------------------
1 1409 CX8
2 1409 PT500
3 1409 487
3 records(s) selected
<order oid = “1409” "><date>6-23-2007</date><part>
<pid>CX8</pid><quantity>400</quantity>
</part><part>
<pid>PT500</pid><quantity>4</quantity>
</part><part>
<pid>487</pid><quantity>4</quantity>
</part></customer>
IBM Software Group | DB2 Information Management Software
41
XMLTABLE: Multiple values per cell?
<dept DID="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
</employee>
</dept>
101
DID
<rooms><office>344</office><office>216</office>
</rooms>
<names><last>Doe</last><last>Pan</last>
</names>
officelastname
SELECT X.* FROM dept,XMLTABLE (‘$DEPTDOC/dept’
COLUMNS DID INTEGER PATH ‘@DID’,lastname XML PATH ‘<names>{employee/name/last}</names>’,office XML PATH ‘<rooms>{employee/office}</rooms>’) AS X
XML type columns can hold repeatingitems encapsulated in a single root element.
DB2 for LUW