如何使用Oracle SQL查询选择XML中的特定节点名称及其值? [英] How to select a particular Node name and its values in XML using Oracle SQL query?

问题描述

我有一个名为SOAP_MONITORING的表，其中有RESPONSE_XML列，它是CLOB数据类型.在此列中，存储了较大的xml字符串.我想从此xml字符串中获取节点名称和节点值.这是我的xml:

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>

我想查询此列以获得201501070917439804的SUBSCRIPTION_ID.我尝试了上述查询

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'

但收到错误

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

为了运行此类查询以从xml获取节点值，我非常陌生.

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

推荐答案

您可以使用以下方法将CLOB转换为XMLType(假设它是有效的):

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

extractvalue(XMLType(RESPONSE_XML), ...

如果要在其中存储XML，则不确定为什么列类型不是XMLType，但这并不完全相关.

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

然后可以将名称空间提供给extractvalue():

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';

..但您有多个ID，因此得到:ORA-19025: EXTRACTVALUE returns value of only one node.

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

不推荐使用extractvalue，如文档中所述

And extractvalue is deprecated, as noted in the documentation

您可以改为使用 XQuery 一个XMLTable.

You can use XQuery instead, specifically here an XMLTable.

假设您只想将ax2130:id值嵌套在ax2147:subscription内，则可以使用以下XQuery:

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 

或者，如果您希望在任何地方(包括空白节点)任何一个ax:2130节点，都可以使用:

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 

仅需要在XMLNamespaces子句中指定XQuery中引用的名称空间.

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

如果需要，您可以根据所选的ID联接到另一个表.

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';

这篇关于如何使用Oracle SQL查询选择XML中的特定节点名称及其值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！