How can I use namespaces in an SQL XML query using the "nodes" command?

Question

I am trying to request fields from the following XML request (which is really a web service call):

<soap:Envelope xmlns:xsi="[schema]" xmlns:xsd="[shema]" xmlns:soap="[schema]"> <soap:Body> <RunPackage xmlns="http://tempuri.org/"> <xmlDoc> <Request> <SubscriberCode>543253</SubscriberCode> <CompanyCode>54325</CompanyCode> <BranchName>TestBranchName</BranchName> <TempWorksUserName>TempWorksUserName</TempWorksUserName> [...] 

With the following XML request:

 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) 

Running, but not returning any results!

If I create the same query but delete the contents of the namespace THEN, it works. For example, the following works just fine:

<xmlDoc> <& GT request; <SubscriberCode> 543253 </SubscriberCode> <CompanyCode> 54325 </CompanyCode> <BranchName> TestBranchName </BranchName> [...]

SQL query:

- Define a namespace for MITS so that we can use the MITS namespace. With XMLNAMESPACES ('[schema]' AS soap2, DEFAULT '[Schema]')

SELECT TransactionID, T2.Loc.query ('data (Request / SubscriberCode)') as 'SubscriberCode' FROM TempWorksRequest CROSS APPLY RequestXML.nodes ('xmlDoc') as T2 (Loc)

Any ideas?

Answer 1

Found a question thanks to Mark! Namespaces must be explicitly declared.

New WORKING request:

 WITH XMLNAMESPACES('[URI1]' AS ns, '[URI2]' AS soap) SELECT TransactionID, T2.Loc.query('data(ns:SubscriberCode)') as 'SubscriberCode', FROM TempWorksRequest CROSS APPLY RequestXML.nodes('soap:Envelope/soap:Body/ns:RunPackage/ns:xmlDoc/ns:Request') as T2(Loc)