CODEWITHSUNDEEP
oracle-databaseplsqlxml-parsing
user2297790
user2297790

Reputation: 95

Extracting xml node from soap response

Following is part of a soap response,

<?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>

I want to extract the node dID from it. I have tried using the function extractValue as follows,

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;

But it does not work. How do i extract the node dID?

Upvotes: 0

Views: 7280

Answers (2)

S.Roshanth
S.Roshanth

Reputation: 1507

If you want to do it in Pl/SQL then construct a DBMS_XMLDOM.DomDocument from your CLOB or BLOB XML message. You can use the below method.

DBMS_XMLPARSER.getDocument()

Then you can use the below method to get the node value from SOAP response

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;

You may use other methods from DBMS_XMLDOM package but providing the correct namespace is important. Also note the way XPATH is constructed. For more info on creating XPATH Click here.

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67772

Use 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

Both ExtractValue and EXTRACT are deprecated in 11g.

Upvotes: 3

Related Questions