0

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chris Lombardi
  • 861
  • 2
  • 14
  • 31
  • https://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008 – Ryan Wilson Jun 13 '18 at 19:22
  • I have tried it that way as well with no luck. It is probably a stupid mistake. WITH XMLNAMESPACES ('http://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 Jun 13 '18 at 19:27

1 Answers1

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)
Ryan Wilson
  • 10,223
  • 2
  • 21
  • 40
  • 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) – Chris Lombardi Jun 13 '18 at 19:53