IBM GLOBAL SERVICES

1
IBM GLOBAL SERVICES

F15
Generating XML from Relational Data Using SQL
with Ease
Guogen (Gene) Zhang
Sept. 12-16, 2005
Orlando, FL
2
Outline

• XML basics and motivation for SQL/XML publishing
  functions
• XML publishing functions by example
• Advanced topics
• Preview of future SQL/XML features
• Summary

3
XML Basics I
Element name
Attribute name
Attribute value
ltEmp name"Jack Lee"gt ltBIRTHDAYgt1960-10-28lt/BIRT
HDAYgt ltdepartmentgtShippinglt/departmentgt lt/Empgt

Start tag
Nested elementsas content of Emp
Element contentfor department
End tag
4
XML Basics II
NCName
Namespace prefix
Prefix
Local part
Namespace name (URI)
lthrEmp xmlnshr"http//www.example.com/hr.xsd"
name"Jack Lee"gt
lthrBIRTHDAYgt1960-10-28lt/hrBIRTHDAYgt
lthrdepartmentgtShippinglt/hrdepartmentgt lt/hrEmpgt
QName
Namespace declaration
5
Motivation for XML Publishing Functions

• The world wants XML.
• Majority of DB2 data is still relational.
• Generating XML from relational data makes DB2 an
  XML data source.
• That's what XML constructor functions are for.
• And that is the first step towards total XML
  support inside engine.

6
Advantages of using SQL/XML Functions

• Easy to extend existing applications with XML
  using SQL
• Works for non-Unicode tables
• Lightweight Web server/applications
• Submit SQL queries and return results to Web/XML
  clients
• Less application development efforts
• High-performance
• Better performance for tables in Unicode
• Conforming to the SQL/XML standard

7
XML Publishing Functions

• Scalar functions (constructors)
• XMLELEMENT, XMLATTRIBUTES
• XMLNAMESPACES (DB2 LUW V8.2)
• XMLFOREST
• XMLCONCAT
• Aggregate function XMLAGG
• Transient XML data type internal use only
• Cast function XML2CLOB (DB2 LUW 8.2
  XMLSERIALIZE)
• Available in DB2 V8 (z/OS New Function Mode)

8
XML Functions by Example 1. simple element
ltEmpgtJack Leelt/Empgt
SELECT XML2CLOB(
XMLELEMENT(NAME "Emp",
e.fname ' ' e.lname )
) AS "result" FROM employees e WHERE ...
9
XML Functions by Example 1. simple element
ltEmp name"Jack Lee"gtlt/Empgt
SELECT XML2CLOB(
XMLELEMENT(NAME "Emp",
XMLATTRIBUTES(e.fname ' ' e.lname AS "name")
) ) AS "result" FROM employees e
WHERE ...
10
XML Functions by Example- 1. simple element
null values
ltEmpgtlt/Empgt
SELECT XML2CLOB(
XMLELEMENT(NAME "Emp",
XMLATTRIBUTES(e.fname ' ' e.lname AS "name")
) ) AS "result" FROM employees e
WHERE ...
Use CASE expression to avoid empty elements
11
XML Functions by Example 2. namespace
ltdocEmp xmlnsdoc"http//www.ibm.com/emp.xsd"gt
Jack Lee lt/docEmpgt
SELECT XML2CLOB(
XMLELEMENT(NAME "docEmp",
XMLNAMESPACES ('http//www.ibm.com/emp.xsd'

AS "doc" ),
e.fname ' ' e.lname) ) AS
"result" FROM employees e WHERE ...
12
XML Functions by Example 2 namespace - illegal
ltdocEmp xmlnsdoc"http//www.ibm.com/emp.xsd"gt
Jack Lee lt/docEmpgt
SELECT XML2CLOB(
XMLELEMENT(NAME "docEmp",
XMLATTRIBUTES ('http//www.ibm.com/emp.xsd'
AS
"xmlnsdoc" ),
e.fname ' ' e.lname) ) AS
"result" FROM employees e WHERE ...
13
XML Functions by Example 3. nested elements
ltEmp name"Jack Lee"gt ltBIRTHDAYgt1960-10-28lt/BIRT
HDAYgt ltdepartmentgtShippinglt/departmentgt lt/Empgt
SELECT XML2CLOB(
XMLELEMENT(NAME "Emp",
XMLATTRIBUTES(e.fname ' ' e.lname AS "name")
XMLELEMENT(NAME BIRTHDAY,
e.birthday), XMLELEMENT(NAME
department, e.dept) ) ) AS
"result" FROM employees e WHERE ...
14
XML Functions by Example 3. alternative
ltEmp name"Jack Lee"gt ltBIRTHDAYgt1960-10-28lt/BIRT
HDAYgt ltdepartmentgtShippinglt/departmentgt lt/Empgt
SELECT XML2CLOB(
XMLELEMENT(NAME "Emp",
XMLATTRIBUTES(e.fname ' ' e.lname AS "name")
XMLFOREST(e.birthday, e.dept
as department) ) ) AS "result"
FROM employees e WHERE ...
15
XML Functions by Example 4. mixed content
ltEmpgtEmployee ltnamegtJack Leelt/namegtwas hired on
lthiredategt2000-05-24lt/hiredategt lt/Empgt
SELECT XML2CLOB( XMLELEMENT(NAME
"Emp",
Employee , XMLELEMENT(NAME
name, e.fname ' ' e.lname),
was hired on , XMLELEMENT(NAME
hiredate, e.hire) ) ) AS
"result" FROM employees e WHERE ...
16
XML Functions by Example 5. concat
ltEmp name"Jack Lee"gtlt/Empgt ltdepartmentgtShippinglt/
departmentgt
SELECT XML2CLOB( XMLCONCAT(
XMLELEMENT(NAME "Emp",
XMLATTRIBUTES(e.fname ' ' e.lname AS "name")
), XMLELEMENT(NAME department,
e.dept) ) ) AS "result" FROM
employees e WHERE ...
17
XML Functions by Example 6. grouping
ltDepartment name"Shipping"gt
ltempgtOppenheimerlt/empgt ltempgtMartinlt/empgt
ltempgtLeelt/empgt lt/Departmentgt
SELECT XML2CLOB( XMLELEMENT(NAME
"Department", XMLATTRIBUTES
(e.dept AS "name" ),
XMLAGG(XMLELEMENT(NAME "emp", e.lname) )
) ) AS "dept_list" FROM
employees e GROUP BY dept
18
XML Functions by Example 6. grouping with
order
ltDepartment name"Shipping"gt ltempgtLeelt/empgt
ltempgtMartinlt/empgt ltempgtOppenheimerlt/empgt lt/D
epartmentgt
SELECT XML2CLOB( XMLELEMENT(NAME
"Department", XMLATTRIBUTES
(e.dept AS "name" ),
XMLAGG(XMLELEMENT(NAME "emp", e.lname)
ORDER BY e.lname )
) ) AS
"dept_list" FROM employees e GROUP BY dept
19
XML Functions by Example 7. complex query
SELECT XML2CLOB( XMLELEMENT( NAME
"Dept", XMLATTRIBUTES ( D.DEPTNO AS
"deptno", D.DEPTNAME AS "name" ), (
SELECT XMLAGG ( XMLELEMENT
(NAME "Proj",
XMLATTRIBUTES (P.PROJNO AS "projno", P.PROJNAME
AS "name"), ( SELECT
XMLAGG (
XMLELEMENT (NAME "Emp",
XMLATTRIBUTES(E.EMPNO
as "empno"),
E.FIRSTNME ' ' E.LASTNAME
) ) FROM
DSN8810.EMPPROJACT EP, DSN8810.EMP E
WHERE EP.PROJNO P.PROJNO AND

EP.EMPNO E.EMPNO
) ) )
FROM
DSN8810.PROJ P
WHERE P.DEPTNO D.DEPTNO
) )
)
FROM DSN8810.DEPT D
WHERE D.DEPTNO
'D01'
20
XML Functions by Example 7. result
ltDept deptno"D01" name"DEVELOPMENT CENTER"gt
ltProj projno"AD3100" name"ADMIN SERVICES"gt
ltEmp empno"000010"gtCHRISTINE HAASlt/Empgt
lt/Projgt ltProj projno"MA2100" name"WELD LINE
AUTOMATION"gt ltEmp empno"000010"gtCHRISTINE
HAASlt/Empgt ltEmp empno"000110"gtVINCENZO
LUCCHESIlt/Empgt lt/Projgt lt/Deptgt
21
XML Functions by Example 8. HTML table
22
XML Functions by Example 8. HTML file
ltTABLE border"1"gtltCAPTIONgtDepartment-Employee
Tablelt/CAPTIONgt ltTRgtltTHgtDept Nolt/THgtltTHgtDept
Namelt/THgtltTHgtEmp Nolt/THgt ltTHgtEmp
Namelt/THgtltTHgtPhonelt/THgtlt/TRgt ltTRgtltTD
rowspan"6"gtA00lt/TDgt ltTD
rowspan"6"gtSPIFFY COMPUTER SERVICE
DIV.lt/TDgtlt/TRgt ltTRgtltTDgt000010lt/TDgtltTDgtCHRISTINE
HAASlt/TDgtltTDgt3978lt/TDgtlt/TRgt ltTRgtltTDgt000110lt/TDgtltTD
gtVINCENZO LUCCHESIlt/TDgtltTDgt3490lt/TDgtlt/TRgt ltTRgtltTDgt
000120lt/TDgtltTDgtSEAN O'CONNELLlt/TDgtltTDgt2167lt/TDgtlt/T
Rgt ltTRgtltTDgt200010lt/TDgtltTDgtDIAN HEMMINGERlt/TDgtltTDgt3
978lt/TDgtlt/TRgt ltTRgtltTDgt200120lt/TDgtltTDgtGREG
ORLANDOlt/TDgtltTDgt2167lt/TDgtlt/TRgt ltTRgtltTD
rowspan"5"gtC01lt/TDgt ltTD
rowspan"5"gtINFORMATION CENTERlt/TDgtlt/TRgt ltTRgtltTDgt0
00030lt/TDgtltTDgtSALLY KWANlt/TDgtltTDgt4738lt/TDgtlt/TRgt ltT
RgtltTDgt000130lt/TDgtltTDgtDOLORES QUINTANAlt/TDgtltTDgt4578
lt/TDgtlt/TRgt ltTRgtltTDgt000140lt/TDgtltTDgtHEATHER
NICHOLLSlt/TDgtltTDgt1793lt/TDgtlt/TRgt ltTRgtltTDgt200140lt/TD
gtltTDgtKIM NATZlt/TDgtltTDgt1793lt/TDgtlt/TRgt lt/TABLEgt
23
XML Functions by example 8. the query for HTML
table
SELECT VARCHAR( XML2CLOB( XMLElement(NAME
"TABLE", XMLATTRIBUTES('1'
as "border"),
XMLElement(NAME CAPTION, 'Department-Employee
Table'), XMLElement(NAME TR,
XMLFOREST('Dept No as TH, 'Dept Name as TH,
'Emp No as
TH, 'Emp Name as TH, 'Phone as TH) ),
XMLAGG(
XMLCONCAT(

XMLELEMENT(NAME TR, XMLELEMENT(NAME TD,

XMLATTRIBUTES(
X.CNT1 as "rowspan"),

D.DEPTNO),
XMLELEMENT(NAME TD,

XMLATTRIBUTES( X.CNT1 as
"rowspan"),
D.DEPTNAME)

),
( SELECT XMLAGG(XMLElement(NAME
TR,
XMLForest(EMPNO as TD,

FIRSTNME ' ' LASTNAME as
TD,
PHONENO as TD) )
) FROM DSN8810.EMP E
WHERE E.WORKDEPT D.DEPTNO )
) ) ) ) ) FROM
DSN8810.DEPT D, (SELECT WORKDEPT, COUNT()
FROM
DSN8810.EMP GROUP BY WORKDEPT) X(DEPTNO,
CNT) WHERE D.DEPTNO X.DEPTNO AND
D.DEPTNO IN ('A00', 'C01')

TableHeader

Dept

Emp
24
XML Functions by Example-- 9 SOAP message with
empty body
ltenvEnvelope xmlnsenv"http//www.w3.org/2002/12
/soap-envelope"gt ltenvHeadergt ltmreservation
xmlnsm http//travelcompany.example.org/reserva
tion envrolehttp//www.w3.org/2
002/12/soap-envelope/role/next
envmustUnderstand"true"gt ltmreferencegtuuid09
3a2da1-q345-739r-ba5d-pqff98fe8j7dlt/mreferencegt
ltmdateAndTimegt2001-11-29T132000.000-0500lt/m
dateAndTimegt lt/mreservationgt ltnpassenger
xmlnsnhttp//mycompany.example.com/employees
envrolehttp//www.w3.org/2002/1
2/soap-envelope/role/next
envmustUnderstand"true"gt ltnnamegtJack
Leelt/nnamegt lt/npassengergt lt/envHeadergt
ltenvBodygt lt/envBodygtlt/envEnvelopegt
25
XML Functions by Example- 9 code for SOAP msg w/
empty body
XMLELEMENT(NAME envEnvelope,
XMLNAMESPACES(http//www.w3.org/2002/12/soap-enve
lope AS env), XMLELEMENT(NAME
envHeader, XMLELEMENT(NAME
mreservation, XMLNAMESPACES(http//trav
elcompany.example.org/reservation AS m,
http//www.w3.org/2002/12/soap-envelope/r
ole/next AS role), XMLATTRIBUTES(true
AS envmustUnderstand), XMLELEMENT(NAME
mreference,
uuid093a2da1-q345-739r-ba5d-pqff98fe8j7d),
XMLELEMENT(NAME mdateAndTime,
2001-11-29T132000.000-0500
) ), XMLELEMENT(NAME npassenger,
XMLNAMESPACES( http//mycompany.example.com/emplo
yees AS n,
http//www.w3.org/2002/12/soap-envelope/role/next
AS role), XMLATTRIBUTES(true AS
envmustUnderstand), XMLELEMENT(NAME
nname, Jack Lee) ) ), XMLELEMENT(NAME
envBody, ))
26
XML Functions by Example 10. recursive document
Part (PartID, PartName, ParentPartID)
Base One level no recursion
ltPartListgt ltPart PartID1gt
ltPartNamegtCarlt/PartNamegt
lt/Partgt lt/PartListgt
Base One level no recursion
SELECT XML2CLOB(XMLELEMENT(NAME "PartList",
XMLAGG(XMLELEMENT(NAME "Part",
XMLATTRIBUTES(P.PartID as
"PartID"),
XMLELEMENT(NAME "PartName", P.PartName) )
ORDER BY P.PartID ) ) ) FROM Part
P WHERE P.ParentPartID 0
27
XML Functions by Example 10. recursive document
Two level recursion
ltPartListgt ltPart PartID1gt
ltPartNamegtCarlt/PartNamegt
ltPart PartID2gt
ltPartNamegtBodylt/PartNamegt lt/Partgt
lt/Partgt lt/PartListgt
SELECT XML2CLOB(XMLELEMENT(NAME "PartList",
XMLAGG(XMLELEMENT(NAME "Part",
XMLATTRIBUTES(P.PartID as "PartID"),
XMLELEMENT(NAME "PartName", P.PartName),
(SELECT XMLAGG(XMLELEMENT(NAME
"Part ",
XMLATTRIBUTES(S.PartID as "PartID"),
XMLELEMENT(NAME "PartName",
S.PartName) ) ORDER BY
S.PartID ) FROM Part S
WHERE S.ParentPartID P.PartID )
) ORDER BY P.PartID) ) ) FROM Part
P WHERE P.ParentPartID 0
28
XML Functions by Example 10. recursive document
Three level recursion
Three level recursion
SELECT XML2CLOB(XMLELEMENT(NAME "PartList",
XMLAGG(XMLELEMENT(NAME "Part",
XMLATTRIBUTES(P.PartID as "PartID"),
XMLELEMENT(NAME "PartName", P.PartName),
(SELECT XMLAGG(XMLELEMENT(NAME
"Part ",
XMLATTRIBUTES(S.PartID as "PartID"),
XMLELEMENT(NAME "PartName",
S.PartName), (SELECT
XMLAGG(XMLELEMENT(NAME "Part",
XMLATTRIBUTES(SS.PartID
as "PartID"),
XMLELEMENT(NAME "PartName",
SS.PartName) )
ORDER BY SS.PartID )
FROM Part SS
WHERE SS.ParentPartID S.PartID ) )
ORDER BY S.PartID )
FROM Part S WHERE
S.ParentPartID P.PartID ) ) ORDER BY
P.PartID) ) ) FROM Part P WHERE
P.ParentPartID 0
29
XML Functions by Example 10. recursive
document sample result
Three level recursion
ltPartListgt ltPart PartID1gt
ltPartNamegtCarlt/PartNamegt
ltPart PartID2gt
ltPartNamegtBodylt/PartNamegt
ltPart PartID3gt
ltPartNamegtFramelt/PartNamegt
lt/Partgt ltPart PartID4gt
ltPartNamegtDoorslt/PartN
amegt lt/Partgt
lt/Partgt ltPart PartID5gt
ltPartNamegtWheelslt/PartNamegt
lt/Partgt lt/Partgt lt/PartListgt
30
SQL to XML Mappings

• SQL ltidentifiergts to XML names
• Escaping for column names
• SQL "Order Items" gt XML "Order_x0020_Items"
• SQL "XMLDoc" gt XML "_x0078_MLDoc"
• SQL "deptid" gt XML "dept_x003A_id
• SQL values to XML values
• SQL 'Shipping Receiving gt XML 'Shipping
  amp Receiving'
• SQL 'X lt 5' gt XML 'X lt 5'
• SQL '2002-02-26-10.01.01.123456'
• gt XML '2002-02-26T100101.123456'

31
XML Functions in Views

• Need to use XML2CLOB
• Using CLOB result for XML in other XML
  constructors will lead to surprising result.
• ltEmpgtJack Leelt/Empgt will be become
  ltEmpgtJack Leelt/Empgt if its used in
  XMLELEMENT as content.

32
Namespace Scoping

• Namespaces declared by XMLNAMESPACES are
  syntactically scoped within the
  XMLELEMENT/XMLFOREST function, e.g. XMLELEMENT(,
  NAMESPACES(as ns) ).
• Namespace prefixes need to be declared in-scope
  except for these pre-defined namespace prefixes
  xml, xs, xsd, xsi, sqlxml

start
end
33
Major Optimization Techniques (DB2 z/OS)

• Flatten nested XML scalar functions into one
  function and a tagging template - avoid data copy
• Two phase processing for XML
• Map SQL values to XML values and build in-memory
  records before tagging
• Apply tagging templates to generate XML string
  (Serialize)
• Materialized XML values in workfile contains only
  the handles to XML values in memory
• Optimize XMLAGG by internal quicksort for ORDER
  BY, and sharing tagging template for its argument
• Serialized XML text has no superfluous namespace
  declarations
• XML2CLOB materializes CLOB in-memory only.

34
Composition by DB2 SQL/XML Publishing functions
in V8
Z990 5 processors, 10GB, z/OS 1.4, DB2 for z/OS
V8
35
Composition XML Extender v.s. Publishing V8
XML Extender for DB2 for z/OS V8 IXM4C40 IXMLC13
36
Preview of Future SQL/XML Features
0
Data model
Bind in XML
1
DB2 Engine
7
2
Store as XML
XML
3
Shred into SQL
Application
2
1
4
Retrieve XML
4
Textual XML
5
Publish XML
8
9
3
6
5
6
Bind out XML
Relational
7
XML to XML
8
XML to SQL
9
SQL to XML
37
Summary

• SQL/XML publishing functions in DB2 V8
• Performance characteristics (z/OS)
• Preview of some future SQL/XML features

38
Thanks!

• Guogen (Gene) Zhang, gzhang_at_us.ibm.com