Home » Developer & Programmer » JDeveloper, Java & XML » read value from xml (Oracle 10g)
read value from xml [message #525646] Tue, 04 October 2011 07:25 Go to next message
mape
Messages: 232
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 Go to previous message
Barbara Boehmer
Messages: 8005
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>

Previous Topic: Re: Bigger type length than Maximum
Next Topic: Extract Data from Clob field using Regular expression
Goto Forum:
  


Current Time: Wed Nov 26 05:27:23 CST 2014

Total time taken to generate the page: 0.09231 seconds