| read value from xml [message #525646] |
Tue, 04 October 2011 07:25  |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
Hello
I need to select all records from table tmp_mape
where value <loyaltyDebit id="5554431"> from xml_params
is for example 5554431.
create table tmp_mape
(xml_params clob)
Example of xml code:
insert into tmp_mape values (
'<prolongationData mode="normal" exceptionCaseID="" xmlns="http://tower.t-mobile.sk/tower/tdm">
<customer status="active" xmlns:ns2="http://tower.t-mobile.sk/client/twizard/communication/orderentry" xmlns:coen="http://tower.t-mobile.sk/client/twizard/communication/orderentry" xmlns:tns="http://tower.t-mobile.sk/client/twizard/communication/orderentry">
<customerReferences>
<reference name="customerID" platform="bscs">2319375</reference>
<reference name="externID" platform="other">BSCS-PRD-CUSTOMER-2319375</reference>
<reference name="siteID" platform="clarify">S-061009-1893787</reference>
<reference name="objID" platform="clarify">4012450</reference>
<reference name="customerCode" platform="bscs">1.91106437</reference>
</customerReferences>
</customer>
<specialDebits>
<loyaltyDebit id="5554431">
<loyaltyMembership id="2426087">
<loyaltyCard id="2229337">
<serialNumber>2229337</serialNumber>
<status>active</status>
</loyaltyCard>
<status>active</status>
<points>2403</points>
<loyaltyExpirations>
<loyaltyExpiration>
<expiryDate>2012-09-01T00:00:00.000+02:00</expiryDate>
<points>195</points>
</loyaltyExpiration>
<loyaltyExpiration>
<expiryDate>2012-10-01T00:00:00.000+02:00</expiryDate>
<points>191</points>
</loyaltyExpiration>
<loyaltyExpiration>
<expiryDate>2012-11-01T00:00:00.000+01:00</expiryDate>
<points>174</points>
</loyaltyExpiration>
</loyaltyExpirations>
</loyaltyMembership>
<loyaltyPayer id="2426087">
<loyaltyCard id="2229337">
<serialNumber>2229337</serialNumber>
<status>active</status>
</loyaltyCard>
<status>active</status>
<points>2403</points>
<loyaltyExpirations>
<loyaltyExpiration>
<expiryDate>2012-09-01T00:00:00.000+02:00</expiryDate>
<points>195</points>
</loyaltyExpiration>
<loyaltyExpiration>
<expiryDate>2012-10-01T00:00:00.000+02:00</expiryDate>
<points>191</points>
</loyaltyExpiration>
<loyaltyExpiration>
<expiryDate>2012-11-01T00:00:00.000+01:00</expiryDate>
<points>174</points>
</loyaltyExpiration>
</loyaltyExpirations>
</loyaltyPayer>
<loyaltyProduct kind="special">
<code>ADMINPOPL_DODAT</code>
<name>Administratívny poplatok pri uzavretí Dodatku k Zmluve o pripojení</name>
<stockSetCard>ADMINPOPL_DODAT</stockSetCard>
<price>
<amount>6.64</amount>
<vat>20</vat>
<currency>EUR</currency>
<exchangeRate>1</exchangeRate>
</price>
</loyaltyProduct>
<loyaltyPayment xmlns:ccom="http://tower.t-mobile.sk/client/twizard/components/common" xmlns:tns="http://tower.t-mobile.sk/client/twizard/communication/bpelflowmanager" xmlns:bpws="http://schemas.xmlsoap.org/ws/2003/03/business-process/">
<points>1328</points>
<discount>
<ratio>50</ratio>
<money>
<amount>3.32</amount>
<vat>20</vat>
<currency>EUR</currency>
<exchangeRate>1</exchangeRate>
</money>
</discount>
<cash>
<amount>3.32</amount>
<vat>20</vat>
<currency>EUR</currency>
<exchangeRate>1</exchangeRate>
</cash>
</loyaltyPayment>
<debitDate>2011-10-01T08:49:13+02:00</debitDate>
</loyaltyDebit>
</specialDebits>
</prolongationData>' )
And I need to read value id from xml <loyaltyDebit id="5554431">
I try to do this but unsuccessfully
select extractValue (value(x), '//customer/@specialDebits', 'xmlns="http://tower.t-mobile.sk/tower/tdm"') idd,
extractvalue (value (x), '//customer/specialDebits/loyaltyDebit[1]/value', 'xmlns="http://tower.t-mobile.sk/tower/tdm"' ) name1,
extractvalue (value (x), '//customer/specialDebits/loyaltyDebit/@id','xmlns="http://tower.t-mobile.sk/tower/tdm"') value
from tmp_mape, table(xmlsequence(extract(xmltype(xml_params), '//prolongationData','xmlns="http://tower.t-mobile.sk/tower/tdm"'))) x
Could you please tell me how to do that?
Thanks
Regards
|
|
|
|
| Re: read value from xml [message #525725 is a reply to message #525646] |
Tue, 04 October 2011 18:57  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
"specialDebits" is not under "customer". You need to use the correct path, like below.
SCOTT@orcl_11gR2> select extractvalue
2 (value (x),
3 '//specialDebits/loyaltyDebit/@id',
4 'xmlns="http://tower.t-mobile.sk/tower/tdm"' )
5 from tmp_mape,
6 table
7 (xmlsequence
8 (extract
9 (xmltype
10 (xml_params),
11 '//prolongationData',
12 'xmlns="http://tower.t-mobile.sk/tower/tdm"'))) x
13 /
EXTRACTVALUE(VALUE(X),'//SPECIALDEBITS/LOYALTYDEBIT/@ID','XMLNS="HTTP://TOWER.T-
--------------------------------------------------------------------------------
5554431
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
