Solved

XML QUERY FROM CLOB

Posted on 2016-09-14
2
27 Views
Last Modified: 2016-10-03
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
Comment
Question by:FutureDBA-
2 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points (awarded by participants)
ID: 41798564
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now