Example: Processing hierarchical data using XMLTABLE

XML documents can contain a hierarchy of data with a variable number of nested levels. You can use XPath expressions to process the hierarchical data.

The following example creates list of parts for a computer and the parent component of each part. The information is based on an XML document that contains the computer's components and the relationship of the components.

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

CREATE TABLE BOMLIST (Cid BIGINT NOT NULL PRIMARY KEY, ITEMS XML )

The XML document contains a list of components and subcomponents. The list of components is related in a hierarchy that describes the subcompoents of a component. If a component consists of subcomponents, each subcomponent of the component is listed as a sub element of the component.

The following statement inserts the XML document into the table BOMLIST

CREATE TABLE BOMLIST (Cid BIGINT NOT NULL PRIMARY KEY, ITEMS XML )
insert into BOMLIST (Cid, ITEMS) values ( 1, '
<item desc="computersystem" model="L1234123">
  <part desc="computer" partnum="5423452345">
    <part desc="motherboard" partnum="5423452345">
      <part desc="CPU" partnum="6109486697">
        <part desc="register" partnum="6109486697"/>
      </part>
      <part desc="memory" partnum="545454232">
        <part desc="transistor" partnum="6109486697"/>
      </part>
    </part>
    
    <part desc="diskdrive" partnum="6345634563456">
      <part desc="spindlemotor" partnum="191986123"/>
    </part>
    <part desc="powersupply" partnum="098765343">
      <part desc="powercord" partnum="191986123"/>
    </part>
  </part>

  <part desc="monitor" partnum="898234234">
    <part desc="cathoderaytube" partnum="191986123"/>
  </part>
  
  <part desc="keyboard" partnum="191986123">
    <part desc="keycaps" partnum="191986123"/>
  </part>
  
  <part desc="mouse" partnum="98798734">
    <part desc="mouseball" partnum="98798734"/>
  </part>
</item>
')

The following SELECT statement navigates through the document and creates a table listing the part and the parent part.

The key feature is the creation of table B using the XMLTABLE function. Using the // in the XPath axis $doc//part navigates through all part elements in the Item node.

SELECT
  A.ITEMNAME,
  B.PART,
  B.PARENT
  FROM BOMLIST ,
  XMLTABLE('$doc/item' PASSING BOMLIST.ITEMS AS "doc"
    COLUMNS
    ITEMNAME VARCHAR(20)  PATH './@desc',
    ITEM     XML          PATH '.'
  )AS A,
  XMLTABLE('$doc//part' PASSING A.ITEM AS "doc"
    COLUMNS
    PART   VARCHAR(20) PATH './@desc',
    PARENT VARCHAR(20) PATH '../@desc'
  )AS B

When the following statement is run against the data, the following table is displayed, listing the part and the parent part:

ITEMNAME             PART                 PARENT
-------------------- -------------------- --------------------
computersystem       computer             computersystem
computersystem       motherboard          computer
computersystem       CPU                  motherboard
computersystem       register             CPU
computersystem       memory               motherboard
computersystem       transistor           memory
computersystem       diskdrive            computer
computersystem       spindlemotor         diskdrive
computersystem       powersupply          computer
computersystem       powercord            powersupply
computersystem       monitor              computersystem
computersystem       cathoderaytube       monitor
computersystem       keyboard             computersystem
computersystem       keycaps              keyboard
computersystem       mouse                computersystem
computersystem       mouseball            mouse