FutureDBA-
asked on
XML QUERY FROM CLOB
Hi All,
I am looking to extract information from a CLOB that has XML data in it.
above is sample data
below are desired results
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>
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.