I’m trying to extract data from an XML document using SQL Server. The XML has namespaces and a complex structure. Here is the XML I’m working with:
<code><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<ProcessXmlDocumentResponse xmlns="http://www.website.com/">
<ProcessXmlDocumentResult>
<browse xmlns="" result="1" first="1" last="2" total="2">
<th>
<td label="Office" hideforuser="false" type="String">fin.trs.head.office</td>
</th>
<tr>
<td field="fin.trs.head.office" hideforuser="false" type="String">ABC123</td>
<key>
<office>officecode</office>
<code>AAA</code>
<number>123456789</number>
<line>1</line>
</key>
</tr>
<tr>
<td field="fin.trs.head.office" hideforuser="false" type="String">ABC123</td>
<key>
<office>officecode</office>
<code>AAA</code>
<number>12345678</number>
<line>1</line>
</key>
</tr>
</browse>
</ProcessXmlDocumentResult>
</ProcessXmlDocumentResponse>
</soap:Body>
</soap:Envelope>
</code>
<code><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<ProcessXmlDocumentResponse xmlns="http://www.website.com/">
<ProcessXmlDocumentResult>
<browse xmlns="" result="1" first="1" last="2" total="2">
<th>
<td label="Office" hideforuser="false" type="String">fin.trs.head.office</td>
</th>
<tr>
<td field="fin.trs.head.office" hideforuser="false" type="String">ABC123</td>
<key>
<office>officecode</office>
<code>AAA</code>
<number>123456789</number>
<line>1</line>
</key>
</tr>
<tr>
<td field="fin.trs.head.office" hideforuser="false" type="String">ABC123</td>
<key>
<office>officecode</office>
<code>AAA</code>
<number>12345678</number>
<line>1</line>
</key>
</tr>
</browse>
</ProcessXmlDocumentResult>
</ProcessXmlDocumentResponse>
</soap:Body>
</soap:Envelope>
</code>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<ProcessXmlDocumentResponse xmlns="http://www.website.com/">
<ProcessXmlDocumentResult>
<browse xmlns="" result="1" first="1" last="2" total="2">
<th>
<td label="Office" hideforuser="false" type="String">fin.trs.head.office</td>
</th>
<tr>
<td field="fin.trs.head.office" hideforuser="false" type="String">ABC123</td>
<key>
<office>officecode</office>
<code>AAA</code>
<number>123456789</number>
<line>1</line>
</key>
</tr>
<tr>
<td field="fin.trs.head.office" hideforuser="false" type="String">ABC123</td>
<key>
<office>officecode</office>
<code>AAA</code>
<number>12345678</number>
<line>1</line>
</key>
</tr>
</browse>
</ProcessXmlDocumentResult>
</ProcessXmlDocumentResponse>
</soap:Body>
</soap:Envelope>
What I’m trying to do:
I want to extract the data from the td elements and the key elements and process it in SQL Server. Here’s the SQL query I’ve tried:
<code>WITH XMLNAMESPACES (
'http://schemas.xmlsoap.org/soap/envelope/' AS soap,
'http://www.website.com/' AS website
)
SELECT
t.value('(td)[1]', 'varchar(1000)') AS OfficeLabel,
t.value('(td)[2]', 'varchar(1000)') AS OfficeValue,
t.value('(key/office)[1]', 'varchar(1000)') AS OfficeKey,
t.value('(key/code)[1]', 'varchar(1000)') AS Code,
t.value('(key/number)[1]', 'varchar(1000)') AS Number
FROM
@xml.nodes('/soap:Envelope/soap:Body/ProcessXmlDocumentResponse/ProcessXmlDocumentResult/browse/tr') AS x(t);
</code>
<code>WITH XMLNAMESPACES (
'http://schemas.xmlsoap.org/soap/envelope/' AS soap,
'http://www.website.com/' AS website
)
SELECT
t.value('(td)[1]', 'varchar(1000)') AS OfficeLabel,
t.value('(td)[2]', 'varchar(1000)') AS OfficeValue,
t.value('(key/office)[1]', 'varchar(1000)') AS OfficeKey,
t.value('(key/code)[1]', 'varchar(1000)') AS Code,
t.value('(key/number)[1]', 'varchar(1000)') AS Number
FROM
@xml.nodes('/soap:Envelope/soap:Body/ProcessXmlDocumentResponse/ProcessXmlDocumentResult/browse/tr') AS x(t);
</code>
WITH XMLNAMESPACES (
'http://schemas.xmlsoap.org/soap/envelope/' AS soap,
'http://www.website.com/' AS website
)
SELECT
t.value('(td)[1]', 'varchar(1000)') AS OfficeLabel,
t.value('(td)[2]', 'varchar(1000)') AS OfficeValue,
t.value('(key/office)[1]', 'varchar(1000)') AS OfficeKey,
t.value('(key/code)[1]', 'varchar(1000)') AS Code,
t.value('(key/number)[1]', 'varchar(1000)') AS Number
FROM
@xml.nodes('/soap:Envelope/soap:Body/ProcessXmlDocumentResponse/ProcessXmlDocumentResult/browse/tr') AS x(t);
Problem:
This query is not returning any results. It seems that there might be an issue with the namespaces or the XPath structure. I’ve ensured that the namespaces are correctly defined, but the values are not being retrieved as expected.