Monday, 15 October 2007

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
---------- ---------- ---------- -------------------------- ------ --------------- ----------- -------------

1 comment:

Jenifer Hwang said...

agen bola piala euro
agen bola piala euro
prediksi bola
prediksi bola
berita bola
berita bola
kontes seo SITUS88BET SITUS JUDI AGEN BOLA SBOBET CASINO ONLINE DAN BANDAR TARUHAN EURO 2016

SITUS88BET SITUS JUDI AGEN BOLA SBOBET CASINO ONLINE DAN BANDAR TARUHAN EURO 2016