Hi, I'm trying to extract this SOAP Response into a table. When I run this code I get "no data found". Any help would be greatly appreciated. Thank you!
with t as (select XMLType('<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:s="http://www.w3.org/2001/XMLSchema">
<SOAP-ENV:Body>
<GetDataSetByNameResponse xmlns:nm="http://tempuri.org">
<GetDataSetByNameResult>
<s:schema id="ByNameDataSet" targetNamespace="http://tempuri.org/ByNameDataSet" attributeFormDefault="qualified" elementFormDefault="qualified" xmlns="http://tempuri.org/ByNameDataSet" xmlns:mstns="http://tempuri.org/ByNameDataSet" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<s:element name="ByNameDataSet" msdata:IsDataSet="true">
<s:complexType>
<s:choice maxOccurs="unbounded">
<s:element name="ByName">
<s:complexType>
<s:sequence>
<s:element name="ID" type="s:long" minOccurs="0"/>
<s:element name="Name" type="s:string" minOccurs="0"/>
<s:element name="DOB" type="s:date" minOccurs="0"/>
<s:element name="SSN" type="s:string" minOccurs="0"/>
</s:sequence>
</s:complexType>
</s:element>
</s:choice>
</s:complexType>
<s:unique name="Constraint1" msdata:PrimaryKey="true">
<s:selector xpath=".//mstns:ByName"/>
<s:field xpath="mstns:ID"/>
</s:unique>
</s:element>
</s:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<ByNameDataSet xmlns="http://tempuri.org/ByNameDataSet">
<ByName diffgr:id="ByName1" msdata:rowOrder="0">
<ID>59</ID>
<Name>Gibbs,Ed X.</Name>
<DOB>1957-01-12</DOB>
<SSN>523-37-7523</SSN>
</ByName>
<ByName diffgr:id="ByName2" msdata:rowOrder="1">
<ID>123</ID>
<Name>Gibbs,Patrick N.</Name>
<DOB>1945-09-05</DOB>
<SSN>295-50-6545</SSN>
</ByName>
</ByNameDataSet>
</diffgr:diffgram>
</GetDataSetByNameResult>
</GetDataSetByNameResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>') xml_data from dual)
SELECT xmlresponse.*
FROM t, XMLTable(Xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope' AS "SOAP-ENV",
'http://tempuri.org' AS "nm"),
'for $ByName in /SOAP-ENV:Envelope/SOAP-ENV:Body/nm:GetDataSetByNameResponse/ByName
return $ByName'
PASSING xml_data
COLUMNS
ID Varchar2(100) Path 'ID',
Name Varchar2(100) Path 'Name',
DOB Varchar2(100) Path 'DOB',
SSN Varchar2(100) Path 'SSN'
) xmlresponse;