CODEWITHSUNDEEP
sqlxmloracle-databaseplsql
mostafa khalil
mostafa khalil

Reputation: 11

Extract XML value

This is my xml message:

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
 xmlns:wsa="http://www.w3.org/2005/08/addressing">
 <env:Header>
 <wsa:Action>http://xmlns.oracle.com/apps/hcm/trees/organizationTreeService//OrganizationTreeService/addValueOrganizationTreeNodeResponse</wsa:Action>
 <wsa:MessageID>urn:uuid:d8f54d7e-8319-492a-9e0f-ec48ba2e840c</wsa:MessageID></env:Header><env:Body>
 <ns0:addValueOrganizationTreeNodeResponse xmlns:ns0="http://xmlns.oracle.com/apps/hcm/trees/organizationTreeService/types/">
 <ns2:treeNodeId xmlns:ns2="http://xmlns.oracle.com/apps/hcm/trees/organizationTreeService/types/" xmlns:ns1="http://xmlns.oracle.com/apps/hcm/trees/organizationTreeService/" xmlns:tns="http://xmlns.oracle.com/adf/svc/errors/" xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns0:StringResult">
 <ns0:Value>80CF98290891BB06E053C8B0BB0A2848</ns0:Value></ns2:treeNodeId></ns0:addValueOrganizationTreeNodeResponse>
 </env:Body></env:Envelope>

I need to get the value80CF98290891BB06E053C8B0BB0A2848 from this xml message

I am trying this code to put it in xmltable and then put in in mt custom table 

select  y.JobFamilyId 
  INTO L_PARENTTRENODEID  

 from (select xmltype (P_RESPONSE) xml from dual) t, 
     xmltable(
  xmlnamespaces ( 'http://xmlns.oracle.com/apps/hcm/trees/organizationTreeService/' as "ns0" ),
  '//ns1:value'
  passing t.xml
  columns
 value varchar2(500) path '/*:value'
 ) x

Upvotes: 0

Views: 114

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

The path you've given for the ns0 namespace is missing the /types from the end - although you've used the wrong URL for the ns0 that is actually in force for Value node anyway; your XPath has ns1:value instead of ns0:Value but either needs to be wildcarded or have the full path; and the columns clause could be simplified to '.':

select x.value
into l_parenttrenodeid  
from xmltable(
  xmlnamespaces('http://xmlns.oracle.com/adf/svc/types/' as "ns0"),
  '//ns0:Value'
  passing xmltype(p_response)
  columns value varchar2(500) path '.'
) x;

or wilcarded (no NS declaration needed):

select x.value
into l_parenttrenodeid  
from xmltable(
  '//*:Value'
  passing xmltype(p_response)
  columns value varchar2(500) path '.'
) x;

or with the full XPath to that node (split here so part goes into the columns clause, for fun and to reduce scrolling), and all namespaces in that path declared - which is confusing as at least one URL is used for multiple names, and at least one name is used for multiple URLs. Fortunately the names don't have to match, they just have to resolve to the right path:

select x.value
into l_parenttrenodeid  
from xmltable(
  xmlnamespaces(
    'http://schemas.xmlsoap.org/soap/envelope/' as "env",
    'http://xmlns.oracle.com/apps/hcm/trees/organizationTreeService/types/' as "ns1",
    'http://xmlns.oracle.com/adf/svc/types/' as "ns2"
  ),
  '/env:Envelope/env:Body/ns1:addValueOrganizationTreeNodeResponse/ns1:treeNodeId'
  passing xmltype(p_response)
  columns value varchar2(500) path 'ns2:Value'
) x;

You could also do with with an XMLQuery() call rather than XMLTable(), as you're only expecting one result:

select xmlquery(
  'declare namespace env = "http://schemas.xmlsoap.org/soap/envelope/"; (::)
    declare namespace ns1 = "http://xmlns.oracle.com/apps/hcm/trees/organizationTreeService/types/"; (::)
    declare namespace ns2 = "http://xmlns.oracle.com/adf/svc/types/"; (::)
    /env:Envelope/env:Body/ns1:addValueOrganizationTreeNodeResponse/ns1:treeNodeId/ns2:Value/text()'
  passing xmltype(p_response)
  returning content
).getstringval()
into l_parenttrenodeid
from dual;

Upvotes: 1

Related Questions