DB2 10.5 for Linux, UNIX, and Windows

Example: Processing elements from multiple trees in an XML document using XMLTABLE

In an XML document that contains multiple hierarchies, or trees, an element in one tree of the document can have a relationship to an element in another tree of the document. You can use XPath expressions to process the related elements in XML documents.

The following example generates a table for moving equipment and furniture to a new building based on information in an XML document. The XML document contains information related the location of the items in the new building.

The following statement creates the table MOVE that stores the XML document:

CREATE TABLE MOVE (ID BIGINT NOT NULL PRIMARY KEY, MOVEINFO XML )

The following XML data contains information about the companies items and the location of the items. The XML information is separated into two trees. One tree contains information about the items such as the department, tag number and a description of the item. The other tree contains information about the move, such as the new location of the departments and the floor assigned to offices, the common public area, and storage area.

The following statement inserts the XML document into the table MOVE:

INSERT into MOVE (ID, MOVEINFO) values ( 1, '
<listing>
  <items>
  <item dept="acct" tag="12223">
    <name>laser printer</name>
    <area>common</area>
  </item>
  <item dept="recptn" tag="23665">
    <name>monitor, CRT</name>
    <area>storage</area>
  </item>
  <item dept="acct" tag="42345">
    <name>CPU, desktop</name>
    <area>office</area>
  </item>
  <item dept="recptn" tag="33301">
    <name>monitor, LCD</name>
    <area>office</area>
  </item>
  <item dept="mfg" tag="10002">
    <name>cabinet, 3 dwr</name>
    <area>office</area>
  </item>
  <item dept="acct" tag="65436">
    <name>table, round 1m</name>
    <area>storage</area>
  </item>
  </items>

  <locations>
  <building dept="recptn" >
     <wing>main</wing>
     <floor area="storage">1</floor>
     <floor area="common">1</floor>
     <floor area="office">2</floor>
  </building>
  
  <building dept="mfg" >
     <wing>east</wing>
     <floor area="storage">1</floor>
     <floor area="common">2</floor>
     <floor area="office">2</floor>
  </building>
  
  <building dept="acct" >
     <wing>west</wing>
     <floor area="storage">2</floor>
     <floor area="common">1</floor>
     <floor area="office">2</floor> 
  </building>
  </locations>
  
</listing>
')

The following SELECT statement combines the items information and location information and creates a table that lists the item information, the department, and the new location.

The key to the mapping is using the relative XPath axis $x/../../ to match the items information with the location information

SELECT T.* 
   from MOVE, 
  XMLTABLE( '$doc/listing/items/item' PASSING MOVE.MOVEINFO AS "doc"
  COLUMNS
    ITEM_ID  VARCHAR(10) PATH 'let $x := . return $x/@tag' ,
    DESC     VARCHAR(20) PATH 'let $x := . return $x/name' ,
    DEPT     VARCHAR(15) PATH 'let $x := . return $x/@dept' ,
    WING     VARCHAR(10) PATH 'let $x := . return $x/../../locations/
               building[@dept = $x/@dept]/wing',
    FLOOR    VARCHAR(10) PATH 'let $x := . return $x/../../locations/
               building/floor[@area = $x/area
                               and ../@dept = $x/@dept ]'
  )as T

When run against the sample data, the statement returns the following data:

ITEM_ID    DESC                 DEPT            WING       FLOOR
---------- -------------------- --------------- ---------- ----------
12223      printer, laser       acct            west       1
23665      monitor, CRT         recptn          main       1
42345      CPU, desktop         acct            west       2
33301      monitor, LCD         recptn          main       2
10002      cabinet, 3 dwr       mfg             east       2
65436      table, round 1m      acct            west       2