Get XML Value from SOAP Request Stored in SQL Server 2014

Question

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

I have tried it that way as well with no luck. It is probably a stupid mistake. WITH XMLNAMESPACES ('schemas.xmlsoap.org/soap/envelope' AS s) select REQUEST_XML.query('/s:Envelope/s:body/serviceAuthorization/recordType/*'), REQUEST_XML.value('(/s:Envelope/s:body/serviceAuthorization/recordType/node())[1]', 'nvarchar(max)') as 'test1' FROM HE_EXTRACT_HISTORY WHERE REQUEST_XML IS NOT NULL — Chris Lombardi
– Chris Lombardi, Commented Jun 13, 2018 at 19:27

Ryan Wilson · Accepted Answer · 2018-06-13 19:48:16Z

1

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)

answered Jun 13, 2018 at 19:48

Ryan Wilson

10.9k3 gold badges23 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Lombardi Over a year ago

Thank you. I manipulated this a little bit, but it worked. SELECT a.value('recordType[1]', 'varchar(100)') FROM HE_EXTRACT_HISTORY CROSS APPLY REQUEST_XML.nodes('//*:serviceAuthorization') AS xx(a)

Collectives™ on Stack Overflow

Get XML Value from SOAP Request Stored in SQL Server 2014

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related