get the value from xml string in oracle sql

Question

I have below xml string which is mentioned below:

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" 
xmlns:ser="http://service.soap.CDRator.com" xmlns:xsd="http://data.soap.CDRator.com/xsd" 
xmlns:xsd1="http://core.data.soap.CDRator.com/xsd" xmlns:xsd2="http://core.result.service.soap.CDRator.com/xsd">
   <soap:Body>
      <ser:generateArchiveDocument>
         <!--Optional:-->
         <ser:contextUser>
            <!--Optional:-->
            <xsd:brandKey>QAMSP</xsd:brandKey>
         </ser:contextUser>
         <!--Optional:-->
         <ser:subscription>
            <!--Optional:-->
            <xsd1:id>201505261213407749</xsd1:id>            
         </ser:subscription>
         <!--Optional:-->
         <ser:letterKey>POS_CONTRACT_PRIVATE</ser:letterKey>
      </ser:generateArchiveDocument>
   </soap:Body>
</soap:Envelope>

I have written below select query to get the value from string <xsd1:id>201505261213407749</xsd1:id> and <ser:letterKey>POS_CONTRACT_PRIVATE</ser:letterKey>. I want to get the value from this string but the query return nothing.

Here is my select query:

SELECT ID,xt_req.SUBSCRIPTION_ID,CREATE_DATE,WEB_SERVICE_NAME,WEB_METHOD_NAME
FROM TEMP_SOAP_GENERATE_CONTRACT sm
CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://www.w3.org/2003/05/soap-envelope' AS "xsd1"
    ),
    'for $i in //xsd1:id return $i'
    passing XMLType(sm.REQUEST_XML)
    columns "SUBSCRIPTION_ID" number path '/') xt_req    
CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://www.w3.org/2003/05/soap-envelope' AS "ser"
    ),
    'for $i in ser:letterKey return $i'
    passing XMLType(sm.REQUEST_XML)
    columns "LETTER_KEY" VARCHAR2(1000) path '/') xt_letterkey;

Alex Poole · Accepted Answer · 2015-09-24 13:11:10Z

The URL you give for the namespace aliases has to match what is defined in the XML:

CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://core.data.soap.CDRator.com/xsd' AS "xsd1"
    ),

and

CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://service.soap.CDRator.com' AS "ser"
    ),

The namespace alias doesn't have to match the one used in the original XML - you can call it something else, though that's likely to be confusing so I wound't recommend it; but the URL has to match. It's the URL the namespace alias represents that is used to match the node. So this woudl also work, for example:

CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://core.data.soap.CDRator.com/xsd' AS "my_alias"
    ),
    'for $i in //my_alias:id return $i'

And for your seconf XMLTable the XPath is looking for a top-level element; as in the first one, the simplest thing to do is ignore any previous levels with //ser:letterKey rather than just //ser:letterKey.

So put together:

SELECT ID,xt_req.SUBSCRIPTION_ID,CREATE_DATE,WEB_SERVICE_NAME,WEB_METHOD_NAME
FROM TEMP_SOAP_GENERATE_CONTRACT sm
CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://core.data.soap.CDRator.com/xsd' AS "xsd1"
    ),
    'for $i in //xsd1:id return $i'
    passing XMLType(sm.REQUEST_XML)
    columns "SUBSCRIPTION_ID" number path '/') xt_req    
CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://service.soap.CDRator.com' AS "ser"
    ),
    'for $i in //ser:letterKey return $i'
    passing XMLType(sm.REQUEST_XML)
    columns "LETTER_KEY" VARCHAR2(1000) path '/') xt_letterkey;

... which gets back one row.

Collectives™ on Stack Overflow

get the value from xml string in oracle sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related