1 / 43

Using XML With DB2

Using XML With DB2. XML-Related Functions and Programming Language Support. Gregg Lippa Themis Inc. http://www.themisinc.com glippa@themisinc.com.

inez-robles
Download Presentation

Using XML With DB2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using XML With DB2 XML-Related Functions andProgramming Language Support Gregg Lippa Themis Inc. http://www.themisinc.com glippa@themisinc.com

  2. 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.

  3. 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)

  4. Implicit Objects Created to Support XML Column • Creating a table with an XML column causes DB2 to implicitly create several objectsto 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 • AnXML table space • AnXML table with columns docid, min_nodeid, and xmldata • ANodeID index on the XML table with key DocID and xmldata

  5. 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

  6. 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)

  7. 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-->

  8. 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

  9. 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 createa displayable character string from the resulting XML

  10. 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 alastname column as a basis for this result

  11. 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>

  12. 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

  13. 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 tostore XML data in a table’s XML column

  14. 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> Partialresult

  15. XMLPARSE • 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 ))

  16. 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

  17. Sample Data for XMLQUERY Examples • Assume an XML column containing these two XML documents: • <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> • <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>

  18. 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 surroundthe XPath expressionwith apostrophes

  19. 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

  20. 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 threedifferent 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 phonenumbers all belongto Jim Beam

  21. 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)

  22. 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="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> • <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>

  23. 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 );

  24. The XMLTABLE Function Creating Tabular Output From XML Data

  25. 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

  26. 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 PATNAME LOCATION ---------------- ---------- Jane Zoe Anytown Bob Bee Uptown 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 addr/city ---------------- ---------- Bob Bee Uptown Jane Zoe Anytown

  27. 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; 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 PATIENTSERVICEafter these inserts 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

  28. 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

  29. Programming Language Support for DB2 XML

  30. 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 expressionis used to retrieve an XML sequence • Use in an SQL FETCH or single-row SELECT INTO

  31. 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

  32. 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 } }

  33. 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 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>' );

  34. 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.xml <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();

  35. 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

  36. 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: patien2.xml <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();

  37. 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);

  38. 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

  39. 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.

  40. 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.

  41. 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)

  42. 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 ****** ****************************** 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

  43. 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

More Related