Using XML With DB2

clumpsmackoverSoftware and s/w Development

Dec 2, 2013 (3 years and 10 months ago)

233 views

© 2009 Themis, Inc. All rights reserved.

Using XML With DB2

XML
-
Related Functions and

Programming Language Support

Gregg Lippa

Themis Inc.

http://www.themisinc.com

glippa@themisinc.com

© 2009 Themis, Inc. All rights reserved.


Gregg Lippa is currently a Senior Technical Advisor at Themis Inc.

He teaches DB2
-
related courses on SQL, Application Programming,
and optimization, performance and tuning as well as Java, J2EE,
distributed computing and Java
-
related topics. Gregg has worked
with DB2 as a consultant and trainer for over 20 years and with Java
for 10 years.



This material is taken from the Themis course DB1091:

DB2 9 for z/OS pureXML Features
.

For more information visit http://www.themisinc.com.



Products and company names mentioned may be trademarks of
their respective companies. Mention of third party products or
software is for reference only and constitutes neither a
recommendation nor an endorsement.


© 2009 Themis, Inc. All rights reserved.

The XML Data Type


DB2 native XML support includes a new XML data type


Available to CREATE TABLE and ALTER TABLE




XML type column holds one XML document for each row


Must be a
well
-
formed

XML document having:


One root element, proper end tags, proper nesting, attribute values

enclosed within (either single or double) quotes, case
-
sensitivity


Insert or update statements fail if XML is not well
-
formed


XML storage requirements


XML data is stored in a
parsed tree structure


Separate XML tablespace plus space for needed indexes

CREATE TABLE MYSAMPLE (REGDATA DECIMAL(10,0), XMLDATA
XML
)

© 2009 Themis, Inc. All rights reserved.

Implicit Objects Created to Support XML Column


Creating a table with an XML column causes DB2 to
implicitly create several objects

to support it
:


A hidden column

called
DB2_GENERATED_DOC_ID_FOR_XML


(a.k.a.
DocID
) which uniquely represents each row


Unique index

is defined on the DocID column


An

XML table space


An

XML table
with columns docid, min_nodeid, and xmldata


A

NodeID index
on the XML table with key DocID and xmldata

© 2009 Themis, Inc. All rights reserved.

SQL/XML Functions


DB2 V8 introduced several XML publishing functions:


XMLAGG


XMLATTRIBUTES


XMLCONCAT


XMLELEMENT


XMLFOREST


XMLNAMESPACE


DB2 V9 adds four new functions for constructing XML documents:


XMLCOMMENT


XMLDOCUMENT


XMLPI


XMLTEXT


DB2 V9 offers additional new functions as well:


XMLSERIALIZE


XMLPARSE


XMLQUERY


XMLEXISTS

© 2009 Themis, Inc. All rights reserved.

XML Publishing Functions Construct XML Sequences


Use SQL/XML publishing functions together to
publish relational data in XML format


XMLAGG
is the only aggregate function (the rest, shown below, scalar functions)


Returns an XML sequence containing an item for each non
-
null value in a set of XML values


XMLATTRIBUTES
constructs XML attributes from the arguments


Only valid as an argument of XMLELEMENT


XMLCOMMENT
returns a comment node (input argument provides content)


XMLCONCAT
returns a sequence that concatenates two or more XML input arguments


XMLDOCUMENT
returns an XML document node with its child nodes


A document node is required by every XML document


XMLELEMENT
returns an XML element node
(does not create a document node)


XMLFOREST
returns a sequence of XML element nodes


XMLNAMESPACES declaration
constructs namespace declarations


For use as an argument to XMLELEMENT, XMLFOREST, or XMLTABLE


XMLPI
returns a processing instruction node


XMLTEXT
returns a text node (input argument provides content)

© 2009 Themis, Inc. All rights reserved.

XML Publishing Functions

Simple Example

INSERT INTO PATIENT VALUES('12345',


(SELECT XMLDOCUMENT(XMLELEMENT(NAME
"
patient",


c.firstname || ' ' || c.lastname),


XMLCOMMENT('sample comment'))


FROM customer c


WHERE c.custno = 55331));

<
patient
>JIM BEAM</
patient
><!
--
sample comment
--
>

© 2009 Themis, Inc. All rights reserved.

XML Publishing Functions Example 2


Construct the following document with its constant values:


<theRoot xmlns="http://xyz.com/aSchema" att="123">


<!
--

comment line here
--
>


<firstElement>one</firstElement>


<secondElement>two</secondElement>

</theRoot>




The document consists of:


Three element nodes (theRoot, firstElement, secondElement)


A namespace declaration


An att attribute on theRoot


A comment node

© 2009 Themis, Inc. All rights reserved.

XML Publishing Functions Example 2

SELECT XMLSERIALIZE(XMLELEMENT (NAME "theRoot",

XMLNAMESPACES (DEFAULT ’http://xyz.com/aSchema’),

XMLATTRIBUTES (’123’ AS "att"),

XMLCOMMENT (’ comment line here ’),

XMLFOREST(

’one’ as "firstElement",




’two’ as "secondElement")) AS CLOB(2K))

FROM SYSIBM.SYSDUMMY1

All wrapped in XMLSERIALIZE function call to create

a displayable character string from the resulting XML

© 2009 Themis, Inc. All rights reserved.

XML Publishing Functions Example 3


Construct XML document from Lastname column of Employee table


Format of document to be constructed:


<allEmployees xmlns="http://xyz.com/aSchema">


<emp>Ashmore</emp>


<emp>Harper</emp>


<emp>Jones</emp>


<emp>Brown</emp>


<emp>Smith</emp>


<emp>Baker</emp>


<emp>Hunter</emp>


<emp>Walker</emp>

</allEmployees>


Document contents:


Root node containing emp element nodes


An allEmployees element containing multiple emp elements


A namespace declaration

Assume there is an employee table with a

lastname column as a basis for this result

© 2009 Themis, Inc. All rights reserved.

XML Publishing Functions Example 3


SELECT statement to construct the document and sample output:

SELECT XMLELEMENT (NAME "allEmployees",

XMLNAMESPACES (DEFAULT 'http://xyz.com/aSchema'),

XMLAGG(XMLELEMENT (NAME "emp", e.lastname)))

FROM THEMIS.EMPLOYEE e

<allEmployees xmlns="http://xyz.com/aSchema"><emp>Jones</emp>

<emp>Smith</emp><emp>Baker</emp><emp>Wang</emp>

<emp>Davis</emp><emp>Et Cetera</emp></allEmployees>

© 2009 Themis, Inc. All rights reserved.

DB2 V9 Additional New Functions


XMLSERIALIZE


Converts XML values in to textual XML


XMLPARSE


Supports insertion of XML data by converting string

expressions into values compatible with XML column


XMLQUERY


Extracts data from XML using XPath expressions


XMLEXISTS


Determines whether an XPath expression on XML data

will return non
-
empty values

© 2009 Themis, Inc. All rights reserved.

XML Serialization and Parsing


XMLSERIALIZE function
converts XML
from its tree format

into text



Use XMLSERIALIZE to retrieve an XML document from DB2


XMLSERIALIZE is given an XML
-
expression to be
converted to a string


Requires specifying generated data type (CLOB, DBCLOB, or BLOB)


Optional INCLUDING XMLDECLARATION clause follows type spec:


Indicates that output will include an explicit XML declaration such as:


<?xml version="1.0" encoding="UTF
-
8"?>


The default is EXCLUDING XMLDECLARATION


XMLPARSE supports the reverse functionality


Use
XMLPARSE to

store XML data in a table’s XML column


© 2009 Themis, Inc. All rights reserved.

XMLSERIALIZE Example


Serialize XML value returned by XMLELEMENT function into a CLOB


Resulting column contains a single XML element with EmpName

as the element name and full employee name as element content

SELECT EMPNO, XMLSERIALIZE(XMLELEMENT(


NAME "EmpName", firstnme || ' ' || lastname)


AS CLOB(80) EXCLUDING XMLDECLARATION)

FROM DSN8910.EMP

000010 <EmpName>CHRISTINE HAAS</EmpName>

000020 <EmpName>MICHAEL THOMPSON</EmpName>

000030 <EmpName>SALLY KWAN</EmpName>

000050 <EmpName>JOHN GEYER</EmpName>

000060 <EmpName>IRVING STERN</EmpName>

000070 <EmpName>EVA PULASKI</EmpName>

000090 <EmpName>EILEEN HENDERSON</EmpName>

000100 <EmpName>THEODORE SPENSER</EmpName>

000110 <EmpName>VINCENZO LUCCHESI</EmpName>

Partial

result

© 2009 Themis, Inc. All rights reserved.


XMLPARSE function:
insert XML documents into an XML column


Provide a string expression or XML host variable as input


XMLPARSE is
not required

to insert XML document into XML column


A regular INSERT... VALUES statement also works


The
XMLPARSE advantage is the ability
to specify what to do with
whitespace that appears between elements (boundary whitespace)


STRIP WHITESPACE

is the default


For regular INSERT (without XMLPARSE)


And for INSERT with XMLPARSE


PRESERVE WHITESPACE

is the alternative



INSERT INTO PATIENT VALUES (98789,

XMLPARSE ( DOCUMENT xml
-
string
-
or
-
host
-
variable PRESERVE WHITESPACE ))

XMLPARSE

© 2009 Themis, Inc. All rights reserved.

XMLQUERY


To retrieve portions of XML documents, use XMLQUERY function


Accepts an
XPath expression

from within an SQL context


XMLQUERY supports:


Retrieving parts of XML documents instead of entire XML documents


Having XML data participate in SQL queries


Operating on both relational and XML data in a single SQL statement


Application of further SQL processing on returned XML values


E.g.: ordering results with the ORDER BY clause


Requires XMLCAST to cast the results to a non
-
XML type


XMLQUERY returns an XML sequence



Sequence can contain one or more items or be empty

© 2009 Themis, Inc. All rights reserved.

Sample Data for XMLQUERY Examples


Assume an XML column containing these two XML documents:

<patient id="55555">


<name>Jim Beam</name>


<addr>



<street>789 Pine</street>



<city>Uptown</city>



<state>NJ</state>



<zip>07733</zip>


</addr>


<phone type="home">908
-
554
-
5454</phone>


<phone type="work">908
-
445
-
4545</phone>


<phone type="fax">908
-
332
-
2424</phone>


<email>jbeam@gmail.com</email>


<service>



<sdate>2007
-
10
-
31</sdate>



<reason>short of breath</reason>



<descrip>trick or treatment</descrip>



<cost>45.00</cost>



<copay>5.00</copay>


</service>

</patient>

<patient id="11123">


<name>Sara Lee</name>


<addr>



<street>33 Maple</street>



<city>Nearly</city>



<state>NJ</state>



<zip>07123</zip>


</addr>


<phone type="home">908
-
842
-
7531</phone>


<email>saralee@cakemail.com</email>


<service>



<sdate>2004
-
02
-
29</sdate>



<reason>brittle nails</reason>



<descrip>prescribe hormones</descrip>



<cost>84.00</cost>



<copay>15.00</copay>


</service>

</patient>

© 2009 Themis, Inc. All rights reserved.

XMLQUERY Example 1


XMLQUERY statement embedded within a SELECT statement requests all

XML documents that are stored in PATIENT_XML column of PATIENT table


SELECT
XMLQUERY('/' passing PATIENT_XML )

FROM PATIENT;


<?xml version="1.0" encoding="IBM037"?><patient id="55555"><name>Jim
Beam</name><addr><street>789 Pine</

DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

DSNT418I SQLSTATE = 01004 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSN SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 0 0 0
-
1 0 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'

X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

DSNT417I SQLWARN0
-
5 = W,W,,,, SQL WARNINGS

DSNT417I SQLWARN6
-
A = ,,,, SQL WARNINGS

<?xml version="1.0" encoding="IBM037"?><patient id="11123"><name>Sara
Lee</name><addr><street>33 Maple</

DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION

DSNT418I SQLSTATE = 01004 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSN SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 0 0 0
-
1 0 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF‘

X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

DSNT417I SQLWARN0
-
5 = W,W,,,, SQL WARNINGS

DSNT417I SQLWARN6
-
A = ,,,, SQL WARNINGS

Important:

Always surround

the XPath expression

with apostrophes

© 2009 Themis, Inc. All rights reserved.

XMLQUERY Example 2


The previous result did not display all XML documents because DSNTEP2 and SPUFI
truncate results after 100 bytes of output since XML does not have a specific length


Work around this problem by nesting XMLQUERY in an XMLSERIALIZE function


SELECT
XMLSERIALIZE(
XMLQUERY('/' passing PATIENT_XML)

AS CLOB(2K))

FROM PATIENT;


<patient id="55555"><name>Jim Beam</name><addr><street>789 Pine
</street><city>Uptown</city><state>NJ</state><zip>07733</zip>

</addr><phone type="home">908
-
554
-
5454</phone><phone type="work">

908
-
445
-
4545</phone><phone type="fax">908
-
332
-
2424</phone><email>

jbeam@gmail.com</email><service><sdate>2007
-
10
-
31</sdate><reason>

short of breath</reason><descrip>trick or treatment</descrip>

<cost>45.00</cost><copay>5.00</copay></service></patient>

<patient id="11123"><name>Sara Lee</name><addr><street>33 Maple

</street><city>Nearly</city><state>NJ</state><zip>07123</zip>

</addr><phone type="home">908
-
842
-
7531</phone><email>

saralee@cakemail.com</email><service><sdate>2004
-
02
-
29</sdate>

<reason>brittle nails</reason><descrip>prescribed hormones</descrip>

<cost>84.00</cost><copay>15.00</copay></service></patient>


Each XML document is actually displayed as one long row

Wraparound applied to make complete information visible

© 2009 Themis, Inc. All rights reserved.

XMLQUERY Example 3


Return all phone numbers of all patients


SELECT XMLSERIALIZE(XMLQUERY('/patient/phone'

passing PATIENT_XML) AS CLOB(2K))

FROM PATIENT;


<phone type="home">908
-
554
-
5454</phone>

<phone type="work">908
-
445
-
4545</phone>

<phone type="fax">908
-
332
-
2424</phone>

<phone type="home">908
-
842
-
7531</phone>



Actually, only two rows result


the first one containing three

different phone numbers from the first XML document


When XMLQUERY returns a sequence that contains multiple elements, the
elements are concatenated into a single string during the serialization process


The resulting row is not necessarily a well
-
formed document


Applications that receive this result must handle this properly

First three phone

numbers all belong

to Jim Beam

© 2009 Themis, Inc. All rights reserved.

XMLQUERY Example 4


XPath expression may return an empty sequence



XMLQUERY will also return an empty sequence


Return all XML documents that have a phone type attribute of “fax”


SELECT XMLSERIALIZE(XMLQUERY('/patient/phone
[@type="fax"]/..
'

passing PATIENT_XML) AS CLOB(2K)) FROM PATIENT;


<patient id="55555"><name>Jim Beam</name><addr><street>789 Pine
</street><city>Uptown</city><state>NJ</state><zip>07733</zip>

</addr><phone type="home">908
-
554
-
5454</phone><phone type="work">

908
-
445
-
4545</phone><phone type="fax">908
-
332
-
2424</phone><email>

jbeam@gmail.com</email><service><sdate>2007
-
10
-
31</sdate><reason>

short of breath</reason><descrip>trick or treatment</descrip>

<cost>45.00</cost><copay>5.00</copay></service></patient>




The result is one empty (not shown) and one non
-
empty sequence


Eliminate the empty sequences using the XMLEXISTS predicate

(covered next)

© 2009 Themis, Inc. All rights reserved.

XMLQUERY Example 5


If XML documents contain namespace definitions, then the XMLQUERY
statement must reference them also


The two XML documents in the table now have namespaces
















Modified SELECT statement to accommodate the namespace

SELECT XMLSERIALIZE(XMLQUERY('

declare default element namespace
"http://xyz.com/aSchema"
;

/patient' passing
PATIENT_XML
) AS CLOB(2K)) FROM PATIENT;

<patient
xmlns="http://xyz.com/aSchema"




id="55555">


<name>Jim Beam</name>


<addr>



<street>789 Pine</street>



<city>Uptown</city>



<state>NJ</state>



<zip>07733</zip>


</addr>


<phone type="home">908
-
554
-
5454</phone>


<phone type="work">908
-
445
-
4545</phone>


<phone type="fax">908
-
332
-
2424</phone>


<email>jbeam@gmail.com</email>


<service>



<sdate>2007
-
10
-
31</sdate>



<reason>short of breath</reason>



<descrip>trick or treatment</descrip>



<cost>45.00</cost>



<copay>5.00</copay>


</service>

</patient>

<patient
xmlns="http://xyz.com/aSchema"




id="11123">


<name>Sara Lee</name>


<addr>



<street>33 Maple</street>



<city>Nearly</city>



<state>NJ</state>



<zip>07123</zip>


</addr>


<phone type="home">908
-
842
-
7531</phone>


<email>saralee@cakemail.com</email>


<service>



<sdate>2004
-
02
-
29</sdate>



<reason>brittle nails</reason>



<descrip>prescribe hormones</descrip>



<cost>84.00</cost>



<copay>15.00</copay>


</service>

</patient>



© 2009 Themis, Inc. All rights reserved.

XMLEXISTS


Use XMLEXISTS predicate to
restrict set of rows

returned by query


Code this restriction
based on the values in XML columns


XMLEXISTS predicate
specifies an XPath expression


XMLEXISTS predicate returns false if its XPath

expression returns an empty sequence


Otherwise, it returns true and the rows are returned


Avoid receiving an empty sequence by using XMLEXISTS:


SELECT XMLSERIALIZE(XMLQUERY('/patient/phone'

passing PATIENT_XML) AS CLOB(2K)) FROM PATIENT

WHERE XMLEXISTS('
/patient/phone[@type="work"]'

passing PATIENT_XML
)
;

© 2009 Themis, Inc. All rights reserved.

The XMLTABLE Function

Creating Tabular Output From XML Data

© 2009 Themis, Inc. All rights reserved.

XMLTABLE Function


XMLTABLE function returns a table from an XML column


Row
-
defining XPath expression specifies result table rows


Column
-
defining XPath expressions specify row contents


The returned table can have any types of columns


Result table structure defined by COLUMNS clause


Includes column name, data type, and source of column value


PATH clause used to specify the source of a result table column


An XPath expression is used for this


XMLNAMESPACES function may be used


Applies to all of XMLTABLE’s XPath expressions

© 2009 Themis, Inc. All rights reserved.

XMLTABLE Uses and Examples


Results from XPath expression may be easier to process in a table


A table supports various operations via SQL:


Iteration over results of XPath expression in SELECT:







Sorting on values from an XML document:








Storing some XML values as XML and others as relational data


Using stored XML documents to supply values for insertion into tables


A simple form of decomposition (next page)

SELECT T.*

FROM PATIENT P,
XMLTABLE

(XMLNAMESPACES(DEFAULT 'http://xyz.com/aSchema'),

'/patient' PASSING P.PATIENT_XML

COLUMNS

"PATNAME" VARCHAR(30) PATH 'name',



"LOCATION" VARCHAR(30) PATH ‘addr/city’) T

SELECT T.*

FROM PATIENT P, XMLTABLE (XMLNAMESPACES(DEFAULT 'http://xyz.com/aSchema'),

'/patient' PASSING P.PATIENT_XML

COLUMNS "PATNAME" VARCHAR(30) PATH 'name',

"addr/city" VARCHAR(30)) T

ORDER BY T.PATNAME

PATNAME LOCATION

----------------

----------

Jane Zoe Anytown

Bob Bee Uptown

PATNAME addr/city

----------------

----------

Bob Bee Uptown

Jane Zoe Anytown

© 2009 Themis, Inc. All rights reserved.

Inserting Values Returned From XMLTABLE


Use XMLTABLE to retrieve XML

document values stored in the

PATIENT table and insert them

into the PATIENTSERVICE table


Definition of PATIENTSERVICE

table into which values from

these documents will be inserted:


INSERT statement using XMLTABLE to populate PATIENTSERVICE:

CREATE TABLE PATIENTSERVICE

(PATIENTNAME VARCHAR(30) NOT NULL,


SERVDATE DATE NOT NULL,


REASON VARCHAR(30) NOT NULL,


DESCRIPTION VARCHAR(30) NOT NULL,


COST DECIMAL(7,2) NOT NULL,


COPAY DECIMAL(7,2) NOT NULL

) IN XMLDB.XML3TS;

PATIENTNAME SERVDATE REASON DESCRIPTION COST COPAY

------------

----------

-----------

--------------

-----

-----


Bob Bee 2008
-
05
-
15 sweet tooth remove it 90.00 10.00

Jane Zoe 2008
-
02
-
22 flew shot gave flew shot 41.00 12.00

PATIENTSERVICE

after these inserts

INSERT INTO PATIENTSERVICE

SELECT T.* FROM PATIENT P,

XMLTABLE

(XMLNAMESPACES(DEFAULT 'http://xyz.com/aSchema'),

'/patient' PASSING P.PATIENT_XML

COLUMNS "PATIENTNAME" VARCHAR(30) PATH 'name',


"SERVDATE" DATE PATH 'service/sdate',


"REASON" VARCHAR(30) PATH 'service/reason',


"DESCRIPTION" VARCHAR(30) PATH 'service/descrip',


"COST" DECIMAL(7,2) PATH 'service/cost',


"COPAY" DECIMAL(7,2) PATH 'service/copay' ) as T

© 2009 Themis, Inc. All rights reserved.

Using XMLTABLE to Create Views


XMLTABLE function supports creation of views from data in an XML column


View may include relational column data combined with XML document data


Example: create the view patient_view with three columns


The name and email values are taken from the XML document


CREATE VIEW patient_view AS

SELECT patientid, p.name, p.email FROM patient,

xmltable('$px/patient' passing patient_xml as "px"

columns

name char(20) path 'name',



email char(20) path 'email') as p;


SELECT * FROM patient_view;


PATIENTID

NAME


EMAIL

---------

------------

--------------------

13579

John Doe

jdoe@mymail.com

24680

Faye Rae

frae@mailone.com

55555

Jim Beam

jbeam@gmail.com

11123

Sara Lee

saralee@cakemail.com

© 2009 Themis, Inc. All rights reserved.

Programming Language
Support for DB2 XML

© 2009 Themis, Inc. All rights reserved.

Programming Language Support for DB2 XML


Programming languages that support DB2 XML:


C or C++ (in embedded SQL or DB2 ODBC applications)


COBOL


Java (JDBC or SQLJ)


Assembler


PL/I


Entire document or a document fragment (sequence) can be

retrieved an from an XML column


Only an entire document can be placed into an XML column


Application variable can receive an entire retrieved XML document


XMLQUERY function containing an XPath expression

is used to retrieve an XML sequence


Use in an SQL FETCH or single
-
row SELECT INTO

© 2009 Themis, Inc. All rights reserved.

XML and JDBC


JDBC applications can store and retrieve XML data


XML data in applications is in the serialized string format


JDBC applications can:


Use setXXX methods to store entire XML document in XML column


Use getXXX methods to retrieve entire XML document from XML column


Use XMLQUERY to retrieve a sequence (portion of) an XML column


Then retrieve the data into an application variable using getXXX methods


Serialized string data is returned when XML column data is retrieved


Whether entire XML column contents or a sequence is returned

© 2009 Themis, Inc. All rights reserved.

Retrieve Data From XML Column Into String Variable

public void processRecordSetUsingStrings(Connection conn, String id) {


PreparedStatement pStmt = null;


String sql = null;


String doc = null;


ResultSet rs = null;


try {



sql = "SELECT PATIENT_XML FROM PATIENT WHERE PATIENTID = ?";



pStmt = conn.prepareStatement(sql);



pStmt.setString(1, id);



rs = pStmt.executeQuery();



while (rs.next()) {




doc = rs.getString(1);




System.out.println("Document contents:
\
n" + doc);



}


} catch (Exception e) {




// handling of exceptions not shown


}

}

© 2009 Themis, Inc. All rights reserved.

INSERT INTO PATIENT (PATIENTID, PATIENT_XML)

VALUES('91919',


'<patient id="91919">


<name>Jane Zoe</name>


<addr>


<street>456 Main</street>


<city>Anytown</city>


<state>PA</state>


</addr>


<phone type="work">610
-
987
-
4321</phone>


<email>jzoe@mymail.com</email>


<service>


<sdate>2008
-
02
-
22</sdate>


<reason>flu shot</reason>


<descrip>gave flu shot</descrip>


<cost>41.00</cost>


<copay>12.00</copay>


</service>


</patient>'

);

Putting XML Data into the Database


For both insert and update:


XML document must

be well
-
formed


Application data type

can be XML, character,

or binary


In the app, XML data

is a serialized string


Must be converted

to XML format when

inserted into an XML

column


Invoke XMLPARSE

if application data

type is not XML

© 2009 Themis, Inc. All rights reserved.

Insertion Into XML Columns


In a JDBC application, read XML data

from file patient.xml as binary data,

and insert it into an XML column:


<patient id="91919">


<name>Jane Zoe</name>


<addr>


<street>456 Main</street>


<city>Anytown</city>


<state>PA</state>


</addr>


<phone type="work">610
-
987
-
4321</phone>


<email>jzoe@mymail.com</email>


<service>


<sdate>2008
-
02
-
22</sdate>


<reason>flu shot</reason>


<descrip>gave flu shot</descrip>


<cost>41.00</cost>


<copay>12.00</copay>


</service>

</patient>

PreparedStatement pStmt = null;

String sql = null;

String patid = "91919";

sql =

"INSERT INTO Patient " +



"(PATIENTID, PATIENT_XML) " +



"VALUES (?, ?) " ;

pStmt = conn.prepareStatement(sql);

pStmt.setString(1, patid);

File file = new File("patient.xml");

pStmt.setBinaryStream(2,



new FileInputStream(file),



(int)file.length());

pStmt.executeUpdate();

patient.xml

© 2009 Themis, Inc. All rights reserved.

Updates Of XML Columns


Supported by SQL UPDATE statement


XML column values may be used to specify which rows
are to be updated (or deleted or selected)


Use XPath expressions to find values within XML documents


The XMLEXISTS predicate supports XPath expressions


Determine whether an empty sequence results from expression


If it does not, rows will be updated

© 2009 Themis, Inc. All rights reserved.

Updates of XML Columns


In a JDBC application, read XML data

from file patien2.xml as binary data, and

use it to update data in the XML column:


<patient id="80808"


xmlns="http://xyz.com/aSchema">


<name>Bob Bee</name>


<addr>


<street>789 Oak</street>


<city>Uptown</city>


<state>PA</state>


</addr>


<phone type="work">610
-
987
-
5556</phone>


<email>bbee@mymail.com</email>


<service>


<sdate>2008
-
05
-
15</sdate>


<reason>sweet tooth</reason>


<descrip>remove it</descrip>


<cost>90.00</cost>


<copay>10.00</copay>


</service>

</patient>

PreparedStatement pStmt = null;

String sql = null;

String patid = "80808";

sql =

"UPDATE Patient " +



"SET PATIENT_XML = ? " +



"WHERE PATIENTID = ? " ;

pStmt = conn.prepareStatement(sql);

pStmt.setString(2, patid);

File file = new File("patien2.xml");

pStmt.setBinaryStream(1,



new FileInputStream(file),



(int)file.length());

pStmt.executeUpdate();

patien2.xml

© 2009 Themis, Inc. All rights reserved.

Deletion Of XML Data From Tables


Delete rows from table PATIENT with a PATIENTID value of 91919

DELETE FROM PATIENT WHERE PATIENTID=91919;


Delete rows from PATIENT with a city element value of "Uptown"


DELETE FROM PATIENT WHERE XMLEXISTS ('
/patient/addr[city="Uptown"]' passing PATIENT_XML);



Delete the XML document in the row of the PATIENT table

with a city element value of "Uptown", but leave the row


UPDATE PATIENT SET PATIENT_XML = NULL WHERE XMLEXISTS (

'/patient/addr[city="Uptown"]' passing PATIENT_XML);

© 2009 Themis, Inc. All rights reserved.

Embedded SQL With XML


Supported languages: assembler, C, C++, COBOL, or PL/I


Supported operations:


Store entire XML document in XML column using INSERT or UPDATE


Retrieve entire XML document from an XML column using SELECT


Retrieve a sequence from a document in an XML column


Use XMLQUERY to generate serialized XML string


Then SELECT or FETCH it into an application variable

01 XML
-
BLOB
USAGE IS SQL TYPE IS XML

AS BLOB(2M).


EXEC SQL DECLARE :XML
-
BLOB VARIABLE
CCSID UNICODE
;


XML host variable types offered by DB2:


XML AS BLOB


XML AS CLOB


XML AS DBCLOB



XML AS BLOB_FILE


XML AS CLOB_FILE


XML AS DBCLOB_FILE

© 2009 Themis, Inc. All rights reserved.

COBOL Retrieval Example

**********************************************************************

* Host variable declarations *

**********************************************************************


01
XMLDATA

USAGE IS SQL
TYPE IS XML

AS CLOB(4K).


01
XMLBLOB

USAGE IS SQL
TYPE IS XML

AS BLOB(4K).


01
CLOBNOTXMLTYPE

USAGE IS SQL TYPE IS CLOB(4K).


**********************************************************************

* Retrieve data from XML column into XML AS CLOB host variable *

**********************************************************************



EXEC SQL
SELECT PATIENT_XML INTO :XMLDATA

FROM PATIENT





WHERE PATIENTID = 12345.


**********************************************************************

* Retrieve data from XML column into XML AS BLOB host variable *

**********************************************************************



EXEC SQL
SELECT PATIENT_XML INTO :XMLBLOB

FROM PATIENT





WHERE PATIENTID = 12345.


**********************************************************************

* Retrieve data from XML column into CLOB variable. Use XMLSERIALIZE *

* function to convert the data from the XML type to the CLOB type *

**********************************************************************



EXEC SQL
SELECT XMLSERIALIZE(PATIENT_XML AS CLOB(4K))





INTO :CLOBNOTXMLTYPE

FROM PATIENT





WHERE PATIENTID = 12345.

© 2009 Themis, Inc. All rights reserved.

COBOL Update Example

******************************************************************

* Host variable declarations *

******************************************************************


01
XMLDATA

USAGE IS SQL
TYPE IS XML

AS CLOB(4K).


01
XMLBLOB

USAGE IS SQL
TYPE IS XML

AS BLOB(4K).


01
CLOBNOTXMLTYPE

USAGE IS SQL TYPE IS CLOB(4K).


******************************************************************

* Update XML column using data in an XML AS CLOB host variable *

******************************************************************




EXEC SQL UPDATE PATIENT
SET PATIENT_XML = :XMLDATA






WHERE PATIENTID = 12345.



******************************************************************

* Update XML column using data in an XML AS BLOB host variable *

******************************************************************



EXEC SQL UPDATE PATIENT SET
PATIENT_XML = :XMLBLOB






WHERE PATIENTID = 12345.


******************************************************************

* Update XML column using data in a CLOB host variable. *

* Use XMLPARSE function to convert data to XML type. *

******************************************************************



EXEC SQL UPDATE PATIENT
SET PATIENT_XML =





XMLPARSE(DOCUMENT :CLOBNOTXMLTYPE)






WHERE PATIENTID = 12345.

© 2009 Themis, Inc. All rights reserved.

LOAD Utility Supports Loading XML Data


Similar to loading other types of data, except:


For delimited input, XML fields follow LOAD utility delimited format


Otherwise,
XML fields are specified like VARCHAR input


Field length specified in a
2
-
byte binary field preceding the data


LOAD statement uses keyword
XML
for all XML type input fields


Keywords
PRESERVE WHITESPACE
if desired (stripped by default)




LOAD DATA INTO TABLE PRODUCTS

(PATIENTID POSITION (1) CHAR(6),


PATIENT_XML POSITION (8) XML PRESERVE WHITESPACE)

© 2009 Themis, Inc. All rights reserved.

****** ****************************** Top of Data *************************

000001 12345 <?xml version="1.0" encoding="IBM037"?><patient id="12345"><na

000002 43210 <?xml version="1.0" encoding="IBM037"?><patient id="43210"><na

000003 51234 <?xml version="1.0" encoding="IBM037"?><patient id="51234"><na

UNLOAD Utility Supports Unloading XML Data


In the unloaded file fragment shown here, the XML document

has been converted to the EBCDIC 37 encoding scheme








Specify UNICODE in the UNLOAD statement and use

Unicode delimiter characters to maximize portability


UNLOAD DATA FROM TABLE PATIENT UNICODE

© 2009 Themis, Inc. All rights reserved.


New XML Data Type in DB2


New DB2 Functions to Support It


Index Support (XMLEXISTS)


XPath Expression Language


Programming Support


Languages


APIs


Insert and Update Whole Doc


Retrieve All or Portion of Doc

Review