XML and DB2

1
XML and DB2

• By Josephine Cheng, Jane Xu
• IBM Santa Teresa Laboratory

Slides edited from the original slides of Yunyu
Song
2
Outline

• Net.Data
• XML Extender
• Create XML document from DB2
• Transform XML document to DB2
• Searching XML documents
• Conclusion

3
Net.Data VI

• A scripting language to create web applications
  which can access data from any ODBC compliant
  databases (e.g. DB2, Oracle, DRDA, ODBC, flat
  files and web registry data)
• Can generate XML documents from SQL queries.
• Its successor product is WebSphere Application
  server.

4
Generating XML documents from existing DB2 data

• Net.Data Macro to generate XML Data

5
Generating XML documents from existing DB2 data

• Resultset of XML data generated by Net.Data

6
Outline

• Net.Data
• XML Extender
• Create XML document from DB2
• Transform XML document to DB2
• Searching XML documents
• Conclusion

7
XML Extender

• Repository for XML documents and their DTDs.
• Data management functionalities (integrity,
  security, recoverability and management).
• User has the option to store the entire document
  as an column or decomposed into multiple tables
  and columns.
• Users can retrieve the entire document or extract
  XML elements and attributes dynamically in an SQL
  query.

8
XML Collection

• SQL Collection Mapping
• It only works when you can collect all relevant
  data with a single SELECT statement, which is
  placed in the DAD. If multiple tables are
  involved you should use primary-foreign key
  relationships.
• RDB_Node Collection Mapping
• It allows node definitions for multiple tables.
  All involved tables must have primary keys. This
  approach does not require SQL in the DAD. It does
  allow imports as well as exports.

9
XML Collection
10
XML Collection
11
XML Column

• Store entire XML document in a column of XML type
• Allows to query XML content
• Associate and store DTD in DB2 for one or more
  document
• Map element and attribute to DB2 table (side
  table)
• Used when your XML storage is mainly archival or
  for reference, and document content is updated
  infrequently

12
XML Column
13
XML Column
14
DAD File

• Define which DB2 tables hold the data
• The mapping method SQL Collection, RDB Node
  Collection, or XML Column
• Whether to validate XML documents with a DTD
  before storing or translating the contents
• Which DTD to use for validation and translation

15
Sample DAD (SQL Collection)
16
Sample DAD (RDB Node Collection)
17
Sample DAD (XMLColumn)
18
XML in DB2
19
Outline

• Net.Data
• XML Extender
• Create XML document from DB2
• Transform XML document to DB2
• Searching XML documents
• Conclusion

20
Create XML from DB2

• Generate XML fragments (SQL Collection)
• Generate whole XML documents (RDB Node
  Collection)

21
Generate XML fragments (SQL Collection)
REC2XML Return XML fragments
XMLELEMENT Return Single elements
XMLATTRIBUTES Group elements as XML attributes
XMLAGG Aggregate elements in a hierarchy
22
Examples I

• SELECT XML2CLOB( XMLELEMENT(NAME "Department",
  XMLATTRIBUTES(e.workdept AS "name"),
  XMLAGG(XMLELEMENT(NAME "emp", e.lastname) ORDER
  BY e.lastname) )) AS "dept_list" FROM employee e
  WHERE e.workdept IN ('C01','E21') GROUP BY
  workdept This query produces the following
  output. dept_list
• ltDepartment name "C01"gt
• ltempgtKWANlt/empgt
• ltempgtNICHOLLSlt/empgt
• ltempgtQUINTANAlt/empgt
• lt/Departmentgt
• ltDepartment name "E21"gt
• ltempgtGOUNOTlt/empgt
• ltempgtLEElt/empgt
• ltempgtMEHTAlt/empgt
• ltempgtSPENSERlt/empgt
• lt/Departmentgt

23
Example II

• REC2XML Fuction
• select rec2xml(1.0, 'COLATTVAL','MyRow',DEPTNO,
  DEPTNAME, MGRNO) AS MYROWS FROM DEPARTMENT
• MYROWS
• -------------------------------------------------
  -------------------
• ltMyRowgt
• ltcolumn name"DEPTNO"gtA00lt/columngt
• ltcolumn name"DEPTNAME"gtSPIFFY COMPUTER SERVICE
  DIV.lt/columngt
• ltcolumn name"MGRNO"gt000010lt/columngt
• lt/MyRowgt
• ltMyRowgt
• ltcolumn name"DEPTNO"gtB01lt/columngt
• ltcolumn name"DEPTNAME"gtPLANNINGlt/columngt
• ltcolumn name"MGRNO"gt000020lt/columngt
• lt/MyRowgt

24
Compose XML documents(RDB Node)
dxxGenXML generates an XML document according to the given DAD and places the result in a table you specify used when document update occasionally
dxxGenXMLClob identical with dxxGenXML except that it returns the XML directly as a Character Large Object (CLOB)
dxxRetrieveXML Used when composing or decomposing XML documents frequently Using the dxxEnableCollection stored procedure to create a named collection and stores the DAD within the database Then refer to it by name using dxxRetrieveXML and dxxRetrieveXMLClob
25
Examples

• dxxGenXML(CLOB(100K) DAD, / input /
• char(resultTabName) resultTabName, / input /
• integer overrideType / input /
• varchar(1024) override, / input /
• integer maxRows, / input /
• integer numRows, / output /
• long returnCode, / output /
• varchar(1024) returnMsg) / output /
• dxxRetrieveXML(char(collectionName)
  collectionName, / input /
• char(resultTabName) resultTabName, / input /
• integer overrideType, / input /
• varchar_value override, / input /
• integer maxRows, / input /
• integer numRows, / output /
• long returnCode, / output /
• varchar(1024) returnMsg) / output /

26
Outline

• Net.Data
• XML Extender
• Create XML document from DB2
• Transform XML document to DB2
• Searching XML documents
• Conclusion

27
Transform XML into DB2

• XML RDB_Node Collection
• XML column

28
RDB_Node collection

• In this case it is the reverse of generating XML
  from an RDB Node Collection. The end result of
  this is pure relational data, in tables, and
  ready to be accessed with the usual database
  tools. You can do Queries, indexes, views,
  optimizations etc.
• dxxShredXML
• Takes the DAD as a parameter, similar to
  dxxGenXML
• Used for applications that do occasional
  updates
• dxxInsertXML
• Uses a DAD already stored, similar to
  dxxRetrieveXML
• Used for applications that make regular updates
• Takes an enabled XML collection as input
  parameter

29
UDTs provided by XML extender(XML Column)
XMLCLOB XML content stored as a character larger object (CLOB) in DB2
XMLVARCHAR XML content stored as VARCHAR in DB2
XMLDBCLOB XML document stored as double byte CLOB in DB2
XMLFILE XML document in a file on a local file system
30
Store XML as XML Column in DB2

• Ensure that the database is XML-enabled
• Optional Locate or create a DTD for your
  documents and store it in DB2XML.DTD_REF.
• Create the table to store the XML documents and
  add the XML column. Also create any side tables.
• Create a DAD to map the XML to the relational DB
  structures.
• Enable the XML column, specifying the DAD.
• Create indexes for the side-tables.
• Insert some XML documents and start performing
  queries.

31
Key Features of XML Column I

• The XML extender supports a subset of XPath,
  providing a way to locate specific data within an
  XML document.
• Combined with the provided extracttype functions
  (extractInteger, extractTimestamp,
  extractVarChar, etc.) you can retrieve values as
  database types.

32
Example I
33
Example II
34
Key features of XML Column II

• You can select to copy specific element and
  attribute values to a side table. Doing that lets
  you search and manipulate the values directly via
  SQL just like any other column data, linking to
  the stored XML to pick up related values.
• You can create side table indexes, to improve
  search time.

35
Use side table for fast search
36
Key features of XML Column III

• XPath based element or attribute update
• UPDATE sales_tab SET order Update(order,
  '/Order/Customer/Name', 'Customer X') WHERE
  sales_person 'Salesperson Y'
• Using the Update function, you can correctly
  alters both the stored XML document and any
  relevant side tables.

37
Outline

• Net.Data
• XML Extender
• Create XML document from DB2
• Transform XML document to DB2
• Searching XML documents
• Conclusion

38
Searching XML docs (XML Column)

• Example
• of
• XML
• Column

39
Searching XML documents (Column)

• Using side tables.
• Can make a view of the joint.

40
Searching XML documents (Column)

• Example of query against attribute.
• The UDF is expensive. Alternatives
• Use DAD to define CUSTOMER as another index in
  the side table.
• Enable XML column to use text indexing (e.g.
  section search with db2tx.contains).

41
Searching XML documents (Collect)

• Search can be done directly against the SQL based
  tables, or using procedures dxxGenXML() and
  dxxRetrieveXML().
• The DAD is used to specify whether to retrieve
  the entire document or a fragment.
• The DAD is also used to specify the search
  criteria which can be based either on tables or
  SQL query.

42
Outline

• Net.Data
• XML Extender
• Create XML document from DB2
• Transform XML document to DB2
• Searching XML documents
• Conclusion

43
Conclusion XML in DB2
Requirement Solutions
generate XML document from relational data (DB2 tables) Use simple functions (REC2XML XMLELEMENT etc.) Use a "SQL Collection" to map a query to an XML document structure Use an "RDB Node Collection" to map a number of tables to an XML document structure (this also allows XML import) Use Net.Data Macro to generate XML Data Ignore DB2's capability and use Visual Basic or VB Script
Transform XML to relational data Use RDB_node collection to store XML in DB2 tables
Store XML documents in DB2 Database for later reference Use an "XML Column" to store entire XML documents.They can still be indexed and queried via DB2 functions