从soap响应中提取xml节点

Question

以下是soap响应的一部分，

<?xml version="1.0" encoding="utf-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Body>
<idc:QuickSearchResponse xmlns:idc="http://www.stellent.com/Search/">
<idc:QuickSearchResult>
<idc:SearchResults>
<idc:dID>127649</idc:dID>
<idc:dRevisionID>1</idc:dRevisionID>
<idc:dDocName>10026726</idc:dDocName>
<idc:dDocTitle>approved Milestone fpr Porject 4176</idc:dDocTitle>
<idc:dDocType>IT</idc:dDocType>
<idc:dDocAuthor>Portfolio</idc:dDocAuthor>

我想从中提取节点dID。我尝试使用函数extractValue，如下所示：

select extractValue(xml1, '//idc:dID', 'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:idc="http://www.stellent.com/Search/"') AS l_value from dual;

但它不起作用。如何提取节点dID

Answer 1

使用XMLTable

SQL> WITH DATA AS (SELECT '<?xml version="1.0" encoding="utf-8"?>
  2  <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
  3     <SOAP-ENV:Body>
  4     <idc:QuickSearchResponse xmlns:idc="http://www.stellent.com/Search/">
  5        <idc:QuickSearchResult>
  6           <idc:SearchResults>
  7              <idc:dID>127649</idc:dID>
  8              <idc:dRevisionID>1</idc:dRevisionID>
  9              <idc:dDocName>10026726</idc:dDocName>
 10              <idc:dDocTitle>approved Milestone fpr Porject 4176</idc:dDocTitle>
 11              <idc:dDocType>IT</idc:dDocType>
 12              <idc:dDocAuthor>Portfolio</idc:dDocAuthor>
 13           </idc:SearchResults>
 14        </idc:QuickSearchResult>
 15     </idc:QuickSearchResponse>
 16     </SOAP-ENV:Body>
 17  </SOAP-ENV:Envelope>' xml FROM dual)
 18  SELECT did
 19    FROM data,
 20         xmltable(XMLNamespaces ('http://schemas.xmlsoap.org/soap/envelope/'
 21                                  AS "SOAP-ENV",
 22                                 'http://www.stellent.com/Search/' AS "idc"),
 23                  '/SOAP-ENV:Envelope/SOAP-ENV:Body/idc:QuickSearchResponse/idc:QuickSearchResult/idc:SearchResults'
 24                  PASSING XMLTYPE(xml)
 25                  COLUMNS
 26                    did NUMBER PATH 'idc:dID');

       DID
----------
    127649

11g中弃用了ExtractValue和EXTRACT。

Answer 2

如果您想在Pl/SQL中执行此操作，那么可以从您的CLOB或BLOB XML消息构造一个DBMS_XMLDOM.DomDocument。您可以使用下面的方法。

DBMS_XMLPARSER.getDocument()

然后，您可以使用以下方法从SOAP响应中获取节点值

FUNCTION Get_Node_Value_From_Doc(
   v_Doc         IN DBMS_XMLDOM.DomDocument) RETURN VARCHAR2
IS
   v_Clob CLOB;
   v_RootNode DBMS_XMLDOM.DomNode;
   v_Value VARCHAR2(2000);
BEGIN       
   v_RootNode := DBMS_XMLDOM.MakeNode(XmlDom.GetDocumentElement(v_Doc));
   v_Value := DBMS_XMLDOM.GetNodeValue(XslProcessor.SelectSingleNode(v_RootNode,
   '/soap:Envelope/soap:Body/GetLiveAnalysisIDSResponse[1]/AnalysisIDs[1]/guid[1]/text()'
   ,'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns="https://www.testkid.net/"'));  
   RETURN v_Value;
END Get_Node_Value_From_Doc;

您可以使用DBMS_XMLDOM包中的其他方法，但提供正确的名称空间很重要。还要注意XPATH的构造方式。有关创建XPATH的更多信息，请单击here。