title: Querying xml in SQL Server with Soap envelope
body: <p>I have found many examples for handling soap namespaces in an xml field, but none have helped me with my case:</p>
  
  <p>I am trying to query a single element in the xml example below. (I truncated the example so you won't see the closing elements, but it is properly formed.)</p>
  
  <p>Here is the xml:</p>
  
  <pre><code>&lt;soapenv:Body xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope"     xmlns:wsa="http://www.w3.org/2005/08/addressing" xmlns:tns="http://www.example.org/TransactionDataSchema" xmlns:saml="urn:oasis:names:tc:SAML:2.0:assertion" xmlns:wsse11="http://docs.oasis-open.org/wss/oasis-wss-wssecurity-secext-1.1.xsd" xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
  &lt;PRPA_IN201305UV02 xmlns="urn:hl7-org:v3" ITSVersion="XML_1.0"&gt;
  &lt;id extension="123abc-12a3-123a-a1bc-0a0a0a0a0a0a" root="1.23.456.7.890123.4.5678.901.2.3.4" /&gt;
  &lt;creationTime value="20150602175238" /&gt;
  &lt;interactionId extension="PRPA_IN201305UV02" root="1.23.456.7.890123.4.5" /&gt;
  &lt;processingCode code="P" /&gt;
  &lt;processingModeCode code="T" /&gt;
  &lt;acceptAckCode code="AL" /&gt;
  &lt;receiver typeCode="RCV"&gt;
    &lt;device classCode="DEV" determinerCode="INSTANCE"&gt;
      &lt;id root="1.23.456.7.890123.4.5678.001.1" /&gt;
      &lt;telecom value="https://stage.mysite.org:1111/myevent" /&gt;
  etc …
  </code></pre>
  
  <p>Here is the query I started with (Request_Body is the xml field in the table), though I believe the issue has something to do with the xml namespace - I'm just not sure what:</p>
  
  <pre><code>SELECT
  Request_Body.value
  ('(/soapenv:Body/PRPA_IN201305UV02[@xmlns="urn:hl7-org:v3"]/receiver/device/id/telecom)[1]', 'varchar(100)' )
  FROM [SW_Reporting].[dbo].[Client_Request] 
  </code></pre>
  
  <p>Here is the error i got with my original query above:</p>
  
  <pre><code>Msg 2229, Level 16, State 1, Line 19
  XQuery [SW_Reporting.dbo.Client_Request.Request_Body.value()]: The name "soapenv" does not denote a namespace.
  </code></pre>
  
  <p>I've tried using <code>With xmlnamespaces</code> and I got the select to return the whole xml using just '/' by itself. And I got everything after the second element (<code>&lt;PRPA_IN201305UV02</code>), too, using a couple of namespace prefixes:</p>
  
  <pre><code>WITH xmlnamespaces (
  'http://www.w3.org/2003/05/soap-envelope' as soapenv
  ,'urn:hl7-org:v3' as ns1)
  </code></pre>
  
  <p>But I can't get anything deeper than that. I am able to get a NULL value in some cases, so at least my syntax is accepted, but I know there is data to retrieve.</p>
  
  <p>Here's my latest query, which isn't working either (after dumping the xml into a table variable):</p>
  
  <pre><code>declare @table table (data xml);
  insert into @table values (@xml);
  
  WITH xmlnamespaces (
  'http://www.w3.org/2003/05/soap-envelope' as soapenv   
  ,'urn:hl7-org:v3' as ns1)
  SELECT Data.query
  ('/soapenv:Body/ns1:PRPA_IN201305UV02/creationTime') as test
  FROM @Table ;
  </code></pre>
  
  <p>I have queried xml before, but not with all the soap stuff. I appreciate any help/suggestions. Thanks in advance</p>