Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

XML QUERY FROM CLOB

Posted on 2016-09-14
2
Medium Priority
?
59 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-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

648 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