如何使用 Oracle SQL 查询在 XML 中选择特定节点名称及其值?

2022-01-10 00:00:00 sql xml-parsing oracle nsxmlelement extract-value

我有一个名为 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>

我想查询此列以获得 SUBSCRIPTION_ID，即 201501070917439804.我试过上面的查询

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 返回一个节点的值.

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

extractvalue 已弃用，如文档中所述

您可以改用 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

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

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

相关文章