Extracting from XML Datatype



WITH XMLDATA AS
(SELECT xmltype(
'<?xml version="1.0" encoding="utf-8"?>
<transaction type="IN">
<customer id="Cust001"/>
<product>
<product_details>
<orderno>ORD000001</orderno>
<orderlineno>000010</orderlineno>
<product_code>PRD</product_code>
<product_content>
<URL/>
<product_one>
<textType>1</textType>
<text/>
<text1>Surrounding Areas</text1>
<iconCreditCards boolean="false"/>
<messagingEmailAddress>myemailAddress@email.com</messagingEmailAddress>
</product_one>
</product_content>
<live_date>2007-02-21</live_date>
<term_date>2008-03-05</term_date>
<proposed_start_date>2007-03-07</proposed_start_date>
</product_details>
<classification code="00001">
<branch bf_id="0001">
<suppress_address boolean="false"/>
<branch_override_details>
<telephone_number prefix="Tel">01234 567890</telephone_number>
</branch_override_details>
<organisation_name>SIGNAL</organisation_name>
<address_line1>Address Line 1, Some Road</address_line1>
<address_line2/>
<address_line3/>
<locality>London</locality>
<post_town>PostTown</post_town>
<postcode>PO1 BO1</postcode>
<telephone_number>02314 555000</telephone_number>
<telephone_number_2 prefix="Mobile">07834 123123</telephone_number_2>
</branch>
<branch bf_id="0002">
<suppress_address boolean="true"/>
<branch_override_details>
<telephone_number prefix="Tel">01234 567891</telephone_number>
</branch_override_details>
<organisation_name>SIGNAL</organisation_name>
<address_line1>Adress Line 1, Street Road</address_line1>
<address_line2/>
<address_line3/>
<locality>Best Locality</locality>
<post_town>Wonderful Town</post_town>
<postcode>PO1 CD2</postcode>
<telephone_number>01189 118118</telephone_number>
<telephone_number_2 prefix="Mobile">07834 123456</telephone_number_2>
</branch>
</classification>
</product>
</transaction>'
') XML FROM DUAL)
SELECT
extractValue(xml,'/transaction/customer@id') as CustomerID,
extractValue(xml,'/transaction/product/product_details/live_date') as startDate,
extractValue(xml,'/transaction/product/product_details/orderno') as orderno,
extractValue(xml,'/transaction/product/product_details/product_content/product_one/messagingEmailAddress') as Email,
extractValue(value(d),'/branch@bf_id') branch,
extractValue(value(d),'/branch/suppress_address@boolean') AddressSuppress,
extractValue(value(d),'/branch/branch_override_details/telephone_number@prefix') TelPrefix,
extractValue(value(d),'/branch/branch_override_details/telephone_number') TelNo
FROM XMLDATA x,
table(xmlsequence(extract(x.xml,'/transaction/product//classification/branch'))) d
/

---------- ---------- ---------- -------------------------- ------ --------------- ----------- -------------
CUSTOMERID STARTDATE ORDERNO EMAIL BRANCH ADDRESSSUPPRESS TELPREFIX TELNO
---------- ---------- ---------- -------------------------- ------ --------------- ----------- -------------
Cust001 2007-02-21 ORD000001 myemailAddress@email.com 0001 false Tel 01234 567890
Cust001 2007-02-21 ORD000001 myemailAddress@email.com 0002 true Tel 01234 567891
---------- ---------- ---------- -------------------------- ------ --------------- ----------- -------------