CODEWITHSUNDEEP
sql-serverxml
Chris Lombardi
Chris Lombardi

Reputation: 891

Get XML Value from SOAP Request Stored in SQL Server 2014

I have the below SOAP that is stored in an XML column in SQL and I am looking for a way to fetch a specific value. An example of the SOAP is as follows. This is a snippet of a way larger request. 

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Header>
    <Action xmlns="http://schemas.microsoft.com/ws/2005/05/addressing/none" s:mustUnderstand="1">http://xyzservice/submit</Action>
  </s:Header>
  <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <serviceAuthorization xmlns="http://www.xyzservice.com/xyzz/schema/auth">
      <recordType xmlns="">Authorization</recordType>
      <externalAuthorizationId xmlns="">4</externalAuthorizationId>
      <authorizationStatus xmlns="">APPROVED</authorizationStatus>
</serviceAuthorization>
  </s:Body>
</s:Envelope>

The SQL I am using is as follows. I have tried it a few different ways, but still no luck. Any help would be appreciated. I have found very few resources that cover what I am trying to do.

WITH XMLNAMESPACES 
('http://schemas.xmlsoap.org/soap/envelope/' AS s)
select 
REQUEST_XML.query('/s:Envelope/s:Body/serviceAuthorization/recordType/*')
FROM HE_EXTRACT_HISTORY
WHERE REQUEST_XML IS NOT NULL

Upvotes: 0

Views: 415

Answers (1)

Ryan Wilson
Ryan Wilson

Reputation: 10765

Here ya go, I tested this locally in SQL Server 2014:

SELECT a.value('recordType[1]', 'varchar(100)')
FROM REQUEST_XML.nodes('//*:serviceAuthorization') AS xx(a)

Upvotes: 1

Related Questions