DB2 is not an XML-enabled database where XML data is stored in relational form using LOB/VARCHAR, where XML document is stored as it is in text form or using shredding/decomposition techniques.
Well lets not focus on this XML-enabled stuff here. XML data type is available only with database with codeset ‘Unicode’, and with single partition-ed database only.
To write various queries, we will consider the CUSTOMER table in SAMPLE database which DB2 creates after it is installed.
The 1st command to get the INFO column data out in XML form xquery declare default element namespace ' for $b in db2-fn:xmlcolumn('CUSTOMER.
Though its not needed to be concerned about DB2’s physical storage management for XML, however to help you understand what DB2 is doing behind the scene, lets briefly discuss it. There are some other meta tables present in DB2 where it stores some meta information.
In above example DB2 will preserve the internal structure of the XML documents, converting its tag names and other information into integer values, doing so helps conserve disk space and also improves the performance of queries that use navigational expressions. DB2 automatically splits portion of an XML document if the document size is more than a page size, it splits node of the document tree across multiple database pages as needed.
A column of type XML can hold one well-formed XML document for every row of the table.
You should choose the page size depending on the size of your XML documents.
INFO')/customerinfo/name return data($b) @ 1 -------------------- Kathy Smith Kathy Smith Jim Noodle Robert Shoemaker Matt Foreman Larry Menard 6 record(s) selected.xquery declare default element namespace ' let $a := for $b in db2-fn:xmlcolumn('CUSTOMER.
INFO')/customerinfo/name return data($b) let $c := count($a) let $d := $c - 4 return $a [position() xquery declare default element namespace ' let $a := db2-fn:xmlcolumn('CUSTOMER.
Here is an example below, see the contents of c:\IMPORT FROM c:\of del xml from c:\ INSERT INTO test SQL3109N The utility is beginning to load data from file "c:\data.del". Consider the below query xquery let $a := for $b in db2-fn:xmlcolumn('TEST.
INFO')/lib/book where $b/@category = 'WEB' return data($b/author) return distinct-values($a) Index CREATE INDEX Index Test1 ON TEST(INFO) GENERATE KEY USING XMLPATTERN '/lib/book/@category' AS SQL VARCHAR(32) This would help queries to directly get access to all ‘/lib/book/@category’ I have covered basics of DB2 XML Indexes over here.
You can create a table with XML columns as well as with relational data types columns.