how to access value of XML

Question

SQL Server 2012

How to access "datefrom" value from below XML? Have try almost everything but without success :(

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://www.w3.org/2003/05/soap-envelope">
   <SOAP-ENV:Body>
      <ns0:getInterestAndExchangeRatesResponse xmlns:ns0="http://swea.riksbank.se/xsd">
         <return>
            <datefrom>2020-03-05</datefrom>
            <dateto>2020-03-05</dateto>
         </return>
      </ns0:getInterestAndExchangeRatesResponse>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Can you share what you tried? You are probably pretty close. — Sean Lange, Mar 10 '20 at 14:18
I finding XML querying challenging enough so that I don't know if this is a duplicate, but check out this [question](https://stackoverflow.com/questions/1890923/xpath-to-fetch-sql-xml-value). — Isaac, Mar 10 '20 at 14:25

score 3 · Accepted Answer · answered Mar 10 '20 at 14:25

You need to respect the XML namespaces in play - but once you do, this should work for you:

DECLARE @Data XML = '....(your XML here).....';

-- define the two XML namespaces in play
WITH XMLNAMESPACES('http://www.w3.org/2003/05/soap-envelope' AS soap,
                   'http://swea.riksbank.se/xsd'AS ns)
SELECT
    @Data.value('(soap:Envelope/soap:Body/ns:getInterestAndExchangeRatesResponse/return/datefrom)[1]', 'varchar(20)')

This will return something like:

2020-03-05

how to access value of XML

1 Answers1