How to select a particular Node name and its values in XML using Oracle SQL query?

Question

I have a table called SOAP_MONITORING in which i have RESPONSE_XML column which is CLOB datatype. In this column large xml string is stored. I want to get the node name and node value from this xml string. Here is my xml :

<?xml version='1.0' encoding='utf-8'?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><ns:placeShopOrderResponse xmlns:ns="http://service.soap.CDRator.com">
<ns:return xmlns:ax2133="http://signup.data.soap.CDRator.com/xsd" xmlns:ax2134="http://core.signup.data.soap.CDRator.com/xsd" xmlns:ax2127="http://data.soap.CDRator.com/xsd" xmlns:ax2129="http://webshop.data.soap.CDRator.com/xsd" xmlns:ax2130="http://core.data.soap.CDRator.com/xsd" xmlns:ax2140="http://core.result.service.soap.CDRator.com/xsd" xmlns:ax2139="http://result.service.soap.CDRator.com/xsd" xmlns:ax2147="http://webshop.result.service.soap.CDRator.com/xsd" xmlns:ax2148="http://mandate.result.service.soap.CDRator.com/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ax2147:PlaceShopOrderResultDTO">
<ax2130:id xsi:nil="true" /><ax2140:description>SOAP_GLOBAL_SUCCESS</ax2140:description>
<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>201501070917439804</ax2130:id>
<ax2130:id>201501070917439804</ax2130:id>
</ns:return></ns:placeShopOrderResponse>
</soapenv:Body>
</soapenv:Envelope>

I want to query this column in order to get the SUBSCRIPTION_ID which is 201501070917439804. I tried the above query

SELECT extractvalue(RESPONSE_XML, '/*/ax2130/*/id/@value')
FROM SOAP_MONITORING where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder'

but received an error

ORA-00932: inconsistent datatypes: expected - got -
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

I am very much new in order to run such queries to get the node value from xml.

The quick answer is to use `XMLType(RESPONSE_XML)`, but this doesn't seem to be valid XML: `LPX-00225: end-element tag "ns:return" does not match start-element tag "ax2147:subscriptions"`. Looks like it's missing an end tag? (Also, [`extractvalue()` is deprecated](https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions061.htm)). — Alex Poole, Jan 29 '15 at 12:41
Actually its a huge xml and i have deleted some parts from it. So that i can show it here. Can you please tell me the query using XMLType ? And will it work as my column datatype is CLOB ? — , Jan 29 '15 at 13:11
I think you should take a look [here](http://stackoverflow.com/questions/4884421/oracle-10g-extract-data-select-from-xml-clob-type) — Aramillo, Jan 29 '15 at 13:17
I have tried in this way SELECT XMLTYPE(RESPONSE_XML).extract('//ax2130:id/text()').getStringVal() FROM SOAP_MONITORING where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder' But its giving an error as XML paring failed,Invalid token '//ax2130:id/text()'. — , Jan 29 '15 at 13:20
When you do this `SELECT XMLTYPE(RESPONSE_XML) FROM SOAP_MONITORING where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder'`, do you get an error? — Aramillo, Jan 29 '15 at 13:23
No i did not get any error at that time. It gives me a complete xml string. — , Jan 29 '15 at 13:37
Please do not change your question in a way that invalidates existing answers. You should ask a new question if you have a new problem; you can always refer back to this one for context if necessary. — Alex Poole, Jan 29 '15 at 15:40
Sorry for this Alex. I thought if i post a new question it will be duplicated. — , Jan 29 '15 at 15:46

Alex Poole · Accepted Answer · 2015-01-29T13:56:29.217

You can convert your CLOB to an XMLType, assuming it's valid, just with:

extractvalue(XMLType(RESPONSE_XML), ...

Not sure why your column type isn't XMLType if you're storing XML in it, but that's not entirely relevant.

You could then supply the namespace to extractvalue():

SELECT extractvalue(XMLType(RESPONSE_XML),
  '//ax2130:id/text()',
  'xmlns:ax2130="http://core.data.soap.CDRator.com/xsd"')
FROM SOAP_MONITORING
where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder';

.. but you have multiple IDs, so you get: ORA-19025: EXTRACTVALUE returns value of only one node.

And extractvalue is deprecated, as noted in the documentation

You can use XQuery instead, specifically here an XMLTable.

Assuming you only want the ax2130:id values nested inside ax2147:subscription, you can use this XQuery:

SELECT xt.id
FROM SOAP_MONITORING 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"
    ),
    'for $i in /soapenv:Envelope/soapenv:Body/ns:placeShopOrderResponse/ns:return/ax2147:subscriptions
      return $i/ax2130:id'
    passing XMLType(sm.RESPONSE_XML)
    columns "ID" number path '/') xt
where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder';

                   ID
---------------------
   201501070917439804 
   201501070917439804 

 2 rows selected

Or if you want any ax:2130 node anywhere, including the blank one, you can use:

SELECT xt.id
FROM SOAP_MONITORING sm
CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://core.data.soap.CDRator.com/xsd' as "ax2130"
    ),
    'for $i in //ax2130:id return $i'
    passing XMLType(sm.RESPONSE_XML)
    columns "ID" number path '/') xt
where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder';

                   ID
---------------------

   201501070917439804 
   201501070917439804 

 3 rows selected

Only the namespaces referred to in the XQuery need to be specified in the XMLNamespaces clause.

You can join to another table based on the selected IDs if you need to:

SELECT xt.id
FROM SOAP_MONITORING sm
CROSS JOIN XMLTable(XMLNAMESPACES (
    ...) xt
JOIN someothertable sot on sot.id = xt.id
where sm.WEB_SERVICE_NAME='RatorWebShopService'
and sm.WEB_METHOD_NAME='placeShopOrder';

Thank you very much. It works after long try. Just a small query. Now i got all the SUBSCRIPTION_ID. I want to take this SUBSCRIPTION_ID and join it with another table. So in this case can i create alias for this complete sql query and simply use this SUBSCRIPTION_ID to join with other tables ? — , Jan 29 '15 at 13:48
You can add a join to another table, and use `xt.id` for the join condition; I've changed it to ANSI-format cross joins (rather than with a comma between tables) to make that clearer and to make additional joins work better. — Alex Poole, Jan 29 '15 at 13:53
Please check my updated question. I just have one more xml column and i tried the same way you query to get the orderType which is NEW. But getting an error. — , Jan 29 '15 at 15:19

score -1 · Answer 2 · edited Mar 09 '15 at 15:24

-1

Do:

"SELECT extractvalue(XMLTYPE(RESPONSE_XML), '/*/ax2130/*/id/@value')
     FROM SOAP_MONITORING where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder'"

In stead of:

SELECT extractvalue(RESPONSE_XML, '/*/ax2130/*/id/@value')
    FROM SOAP_MONITORING where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder'

edited Mar 09 '15 at 15:24

Bono

4,757
6
48
77

answered Mar 09 '15 at 14:36

prashanth

1

How to select a particular Node name and its values in XML using Oracle SQL query?

2 Answers2

Linked