Can't extract the data from xml values in oracle sql

Question

I have the following 2 xml value which is similar and which is stored in the request_xml column and which is clob data type:

<?xml version='1.0' encoding='utf-8'?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
   <S:Body>
      <ns5:updateRechargeTicket xmlns:ns5="http://service.soap.CDRator.com" xmlns:ns2="http://core.result.service.soap.CDRator.com/xsd" xmlns="http://core.data.soap.CDRator.com/xsd" xmlns:ns4="http://data.soap.CDRator.com/xsd" xmlns:ns3="http://payment.result.service.soap.CDRator.com/xsd">
         <ns5:contextUser>
            <ns4:brandKey>FNC</ns4:brandKey>
         </ns5:contextUser>
         <ns5:rechargeTicket>
            <id xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
            <billingGroupId>200907111603122893</billingGroupId>
            <code>TIME_DIRECTDEBIT</code>
            <dateCreated xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
            <dayOfMonth>1</dayOfMonth>
            <nextRechargeDate>2015-06-01+02:00</nextRechargeDate>
            <rechargeAmount>20</rechargeAmount>
         </ns5:rechargeTicket>
      </ns5:updateRechargeTicket>
   </S:Body>
</S:Envelope>


<?xml version='1.0' encoding='utf-8'?><S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/"><S:Body><ns5:addDirectDebitPayment xmlns:ns5="http://service.soap.CDRator.com" xmlns:ns2="http://core.result.service.soap.CDRator.com/xsd" xmlns="http://core.data.soap.CDRator.com/xsd" xmlns:ns4="http://data.soap.CDRator.com/xsd" xmlns:ns3="http://payment.result.service.soap.CDRator.com/xsd"><ns5:contextUser><ns4:brandKey>FNC</ns4:brandKey></ns5:contextUser><ns5:billingGroupId>201008141448491784</ns5:billingGroupId><ns5:amount>10.0</ns5:amount></ns5:addDirectDebitPayment></S:Body></S:Envelope>

I want to use only 1 select query to extract the BillingGroupId from this 2 xml values. Is it possible or do i want to use separate select query to extarct BillingGroupId for this 2 xml values? I want to extract the billingGroupID value from this xml value but its not returning anything. I am doing a small mistake in my select query which i am not able to identify.

Here is my query :

SELECT xt_billingGroupId.BILLING_GROUP_ID
FROM TEMP_SOAP_MONITORING_TOPUP sm
CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
      'http://service.soap.CDRator.com' as "ns",
      'http://core.data.soap.CDRator.com/xsd' as "ax2130",
      'http://webshop.result.service.soap.CDRator.com/xsd' as "ax2147",
      'http://core.signup.data.soap.CDRator.com/xsd' as "ns3",
      'http://service.soap.CDRator.com' as "ns5",
      'http://core.result.service.soap.CDRator.com/xsd' as "ax232"
    ),
    'for $i in //ns5:billingGroupId return $i'
    passing XMLType(sm.REQUEST_XML)
    columns "BILLING_GROUP_ID" VARCHAR2(100) path '/') xt_billingGroupId    
CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://core.result.service.soap.CDRator.com/xsd' as "ax232"
    )

Alex Poole · Accepted Answer · 2015-05-13 09:44:43Z

3

Also one variant

select xt_billingGroupId.* from x,
XMLTable(XMLNAMESPACES (
      'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
      'http://service.soap.CDRator.com' as "ns",
      'http://core.data.soap.CDRator.com/xsd' as "ax2130",
      'http://webshop.result.service.soap.CDRator.com/xsd' as "ax2147",
      'http://core.signup.data.soap.CDRator.com/xsd' as "ns3",
      'http://service.soap.CDRator.com' as "ns5",
      'http://core.result.service.soap.CDRator.com/xsd' as "ax232"
    ),
    '//ns5:rechargeTicket'
    passing x
    columns "BILLING_GROUP_ID" VARCHAR2(100) path 'ax2130:billingGroupId',
    lineitem  XMLType  PATH '/') xt_billingGroupId ;

edited May 13, 2015 at 9:44

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

answered May 13, 2015 at 9:40

Dmitry.Samborskyi

4852 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Poole Over a year ago

Well spotted, I hadn't noticed the default xmlns from the XML text was aliased as ax2130 in the XMLNameSpaces definition. Might be helpful to explain that in your answer though.

Dmitry.Samborskyi Over a year ago

I think it's because name isn't asigned to this ns, so it used as default. And also, I found it in lineitem value, when parsed xml :)

Alex Poole · Accepted Answer · 2015-05-13 10:09:27Z

2

You're trying to access a node with no namespace prefix, so in the default namespace, but within a node that has a specific namespace prefix, ns5. You could do that with local-name():

SELECT xt_billingGroupId.BILLING_GROUP_ID
FROM TEMP_SOAP_MONITORING_TOPUP sm
CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://schemas.xmlsoap.org/soap/envelope/' AS "S",
      'http://service.soap.CDRator.com' as "ns",
      'http://core.data.soap.CDRator.com/xsd' as "ax2130",
      'http://webshop.result.service.soap.CDRator.com/xsd' as "ax2147",
      'http://core.signup.data.soap.CDRator.com/xsd' as "ns3",
      'http://service.soap.CDRator.com' as "ns5",
      'http://core.result.service.soap.CDRator.com/xsd' as "ax232"
    ),
    '//ns5:rechargeTicket/*[local-name()="billingGroupId"]'
    passing XMLType(sm.REQUEST_XML)
    columns "BILLING_GROUP_ID" VARCHAR2(100) path '/') xt_billingGroupId;

BILLING_GROUP_ID                                                               
--------------------------------------------------------------------------------
200907111603122893

Or by wild-carding the namespace:

...
'//ns5:rechargeTicket/*:billingGroupId'
passing XMLType(sm.REQUEST_XML)
columns "BILLING_GROUP_ID" VARCHAR2(100) path '/') xt_billingGroupId

If you want it to be flexible you could just use the wild-card without referring to the containing node at all; and then you don't need the XMLNameSpaces definition either. With your two sample XMLs:

SELECT xt_billingGroupId.BILLING_GROUP_ID
FROM TEMP_SOAP_MONITORING_TOPUP sm
CROSS JOIN XMLTable('//*:billingGroupId'
    passing XMLType(sm.REQUEST_XML)
    columns "BILLING_GROUP_ID" VARCHAR2(100) path '/') xt_billingGroupId    

BILLING_GROUP_ID                                                               
--------------------------------------------------------------------------------
200907111603122893                                                              
201008141448491784

edited May 13, 2015 at 10:09

answered May 13, 2015 at 9:35

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

4 Comments

Marcus Over a year ago

Hello Alex its working. I had one more question related to this can i edit my question ?

Alex Poole Over a year ago

@Rahul - depends if it's related to the answer, in which case you ask for clarification as a comment; or is an unrelated question about the same task, in which case you should ask a new question.

Marcus Over a year ago

Hello Alex i have updated my question little bit. I have 2 versions of xml values which is somewhat similar. And i want to use the same query with modifications and make it flexible to extract the BillingGroupId from this 2 xml values.

Alex Poole Over a year ago

@Rahul - OK, added generic version that will find any billingGroupId regardless of containing node or namespace.

Collectives™ on Stack Overflow

Can't extract the data from xml values in oracle sql

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related