On a client project today we had the need to create a table from an XML data file sent in a hierarchical format. The XML file contains multiple orders. Each order is for a particular store which is provided inside a header tag. Each store can order multiple items which are provided via sub tags of each header. A simplified sample of the file looks like this:
[box type=”shadow”]
<orders>
<order_header>
<id>{6315E9FF}</id>
<account_number>12345</account_number>
<account_name>JOES CONVENIENCE</account_name>
<store_address>123 MAIN STREET</store_address>
<store_address_city>Pittsburgh</store_address_city>
<store_address_state>PA</store_address_state>
<store_address_postcode>15235</store_address_postcode>
<order_line>
<product_code>123456</product_code>
<product_externalid>123456</product_externalid>
<product_name>Peg Counter Spinner</product_name>
<order_quantity>1</order_quantity>
</order_line>
<order_line>
<product_code>723678</product_code>
<product_externalid>723678</product_externalid>
<product_name>Skittles</product_name>
<order_quantity>1</order_quantity>
</order_line>
</order_header>
<order_header>
<id>{3CB06C14}</id>
<account_number>23456</account_number>
<account_name>SMITH LIMITED</account_name>
<store_address>132 WEST AVENUE</store_address>
<store_address_city>Pittsburgh</store_address_city>
<store_address_state>PA</store_address_state>
<store_address_postcode>15213</store_address_postcode>
<order_line>
<product_code>123456</product_code>
<product_externalid>123456</product_externalid>
<product_name>Peg Counter Spinner</product_name>
<order_quantity>1</order_quantity>
</order_line>
</order_header>
</orders>
[/box]
The challenge was to write an Oracle query that could easily return both the header and detail information on a single row. After some quick research I found that this can be done by using two separate XMLTables that hit the same file. The first XMLTable contains store-level information. The second XMLTable contains item level information. When used together the second table starts with the XMLType (order_lines) that is defined in the first XMLTable. The outer query simply selects fields you need. It’s essentially a Cartesian product but it only intersects where appropriate given the “passing” clause. Here is a sample of the query to read the data. This query assumes the file name is orders.xml and it is sitting in the previously defined XMLDIR Oracle directory.
[box type=”shadow”]
SELECT stores.order_id
,stores.account_number
,stores.account_name
,stores.retail_number
,stores.store_name
,stores.store_address
,stores.store_state
,stores.store_zip
,items.*
FROM XMLTable('/orders/order_header'
passing xmltype( bfilename('STRIPE_UAT','wrigley_orders.xml'), nls_charset_id('AL32UTF8') )
columns order_id varchar2(2000) path 'id'
,account_number varchar2(50) path 'account_number'
,account_name varchar2(50) path 'account_name'
,retail_number varchar2(12) path 'store_externalid'
,store_name varchar2(50) path 'store'
,store_address varchar2(100) path 'store_address'
,store_state varchar2(5) path 'store_address_state'
,store_zip varchar2(10) path 'store_address_postcode'
,order_lines XMLTYPE path 'order_line'
) stores,
XMLTable('/order_line'
passing stores.order_lines
columns product_code varchar2(100) path 'product_code'
,product_name varchar2(100) path 'product_name'
,order_quantity varchar2(100) path 'order_quantity'
) items
[/box]
This methodology can easily be extended to query an XML structure n levels deep to return information in one result set.
