Databases
StackOverflow2013
Postsdbo
POHow can I use namespaces in a SQL XML query with the "nodes" command?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I use namespaces in a SQL XML query with the "nodes" command?
    text
    copied!<p>I'm trying to query fields from the following XML query, (which is really a web service call):</p> <pre><code>&lt;soap:Envelope xmlns:xsi="[schema]" xmlns:xsd="[shema]" xmlns:soap="[schema]"&gt; &lt;soap:Body&gt; &lt;RunPackage xmlns="http://tempuri.org/"&gt; &lt;xmlDoc&gt; &lt;Request&gt; &lt;SubscriberCode&gt;543253&lt;/SubscriberCode&gt; &lt;CompanyCode&gt;54325&lt;/CompanyCode&gt; &lt;BranchName&gt;TestBranchName&lt;/BranchName&gt; &lt;TempWorksUserName&gt;TempWorksUserName&lt;/TempWorksUserName&gt; [...] </code></pre> <p>With the following XML Query:</p> <pre><code>WITH XMLNAMESPACES('[schema]' AS soap2, DEFAULT '[schema]') SELECT TransactionID, T2.Loc.query('data(Request/SubscriberCode)') as 'SubscriberCode' FROM TempWorksRequest CROSS APPLY RequestXML.nodes('soap2:Envelope/soap2:Body/RunPackage/xmlDoc') as T2(Loc) </code></pre> <p>It runs but does not return any retults!</p> <p>If I build the same query but remove the namespace stuff THEN it works. For example, the following works fine:</p> <blockquote> <p>&lt;xmlDoc&gt; &lt;Request&gt; &lt;SubscriberCode&gt;543253&lt;/SubscriberCode&gt; &lt;CompanyCode&gt;54325&lt;/CompanyCode&gt; &lt;BranchName&gt;TestBranchName&lt;/BranchName&gt; [...]</p> </blockquote> <p>SQL Query:</p> <blockquote> <p>-- Define a namespace for MITS so we can use the MITS namespace. WITH XMLNAMESPACES('[schema]' AS soap2, DEFAULT '[schema]')</p> <p>SELECT TransactionID, T2.Loc.query('data(Request/SubscriberCode)') as 'SubscriberCode' FROM TempWorksRequest CROSS APPLY RequestXML.nodes('xmlDoc') as T2(Loc)</p> </blockquote> <p>Any ideas?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload