XML QUERY FROM CLOB

Hi All,

I am looking to extract information from a CLOB that has XML data in it.

<ORDER FW="1005_201609120336807">
  <HEADER VENDOR_ID="222500" VENDOR_Name="COMPANY NATURAL" ORDER_NUM="156456465" ORDER_DATE="09/12/2016" SHIP_DATE="09/13/2016" Total_Cases="15" Total_Pieces="0" DETAILS="ORGANICS" DEPT="058" STORE="N/A" ACCOUNT="N/A" PO_Number="336807" Notes="N/A" BillTo_Name="FOODCOMPANY PARAMUS LLC" BillTo_Address="30 BOSTON AVE STORE #8, ROUTE 17" BillTo_City="CUTYONE" BillTo_State="AL" BillTo_Zip="07652" ShipTo_Name="FOODCOMPANY PARAMUS LLC" ShipTo_Address="30 BOSTON AVE STORE #8, ROUTE 17" ShipTo_City="CUTYONE" ShipTo_State="AL" ShipTo_Zip="07652">
    <DETAIL UPC="093966033660" ProductCode="2290" QTY="6" size="81" MEASURE="FOZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="857682003054" ProductCode="3163" QTY="1" size="3.5" MEASURE="OZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="857682333047" ProductCode="3162" QTY="1" size="3.5" MEASURE="OZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="857682333016" ProductCode="3161" QTY="1" size="3.5" MEASURE="OZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="857682333009" ProductCode="3160" QTY="1" size="3.5" MEASURE="OZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="857682333153" ProductCode="3164" QTY="1" size="3.5" MEASURE="OZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="857732332310" ProductCode="1330" QTY="2" size="14" MEASURE="FOZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="8576820333122" ProductCode="3165" QTY="1" size="3.5" MEASURE="OZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="093966335301" ProductCode="2803" QTY="1" size="11" MEASURE="FOZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
  </HEADER>
</ORDER>

Open in new window


above is sample data


below are desired results
ORDER_NUM   |     PO_NUMBER     | ORDER_DATE | DELIVERY_DATE | BILLTO_CITY   | DEPT   | ProductCode | QTY
----------------------------------------------------------------------------------------------------------
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          2290        6
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          3163        1
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          3162        1
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          3161        1
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          3160        1
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          3164        1
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          1330        2
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          3165        1
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          2806        1

Open in new window

FutureDBA-Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
SELECT x.order_num,
       x.po_number,
       x.order_date,
       x.delivery_date,
       x.billto_city,
       x.dept,
       y.product_code,
       y.qty
  FROM yourtable,
       XMLTABLE(
           '/ORDER/HEADER'
           PASSING xmltype(yourclob)
           COLUMNS order_num VARCHAR2(10) PATH '@ORDER_NUM',
                   po_number VARCHAR2(10) PATH '@PO_Number',
                   order_date VARCHAR2(10) PATH '@ORDER_DATE',
                   delivery_date VARCHAR2(10) PATH '@SHIP_DATE',
                   billto_city VARCHAR2(20) PATH '@BillTo_City',
                   dept VARCHAR2(3) PATH '@DEPT',
                   xml XMLTYPE PATH '.'
       ) x,
       XMLTABLE(
           '//DETAIL'
           PASSING x.xml
           COLUMNS product_code NUMBER PATH '@ProductCode', qty NUMBER PATH '@QTY'
       ) y
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.