[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

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

0
FutureDBA-
Asked:
FutureDBA-
1 Solution
 
sdstuberCommented:
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now