title: Parsing web service response (in XML) in SQL Server using XML
body: <p>I have a SQL Server procedure which calls a web service and the web service returns response in the following format:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" ?&gt;  
   &lt;ProductServiceOutput ProductID="160" TransactionID="315" AMT="10000.00" TransactionStatus="SUCCESS" EntityID="1000005580401" Comments="Successfull" TxnDate="2017-12-19 19:31:45" UDF9="null" stdCode="null"/&gt;
  </code></pre>
  
  <p>I would like to parse this XML and get the values of ProductID, TransactionID, Amt, TransactionStatus and TxnDate. </p>
  
  <p>I've tried the following SQL, but I'm getting encoding error. </p>
  
  <pre><code>DECLARE @sql NVARCHAR(MAX)
  SET @sql = '&lt;?xml version="1.0" encoding="UTF-8" ?&gt;  
   &lt;ProductServiceOutput ProductID="160" TransactionID="315" AMT="10000.00" 
   TransactionStatus="SUCCESS" EntityID="1000005580401" Comments="Successfull" 
   TxnDate="2017-12-19 19:31:45" UDF9="null" stdCode="null"/&gt;'
  
  DECLARE @xmlData XML
  SET @xmlData = CONVERT(XML, @sql, 1);
  SELECT 
        T.C.value('(ProductID)[1]', 'int')
        ,T.C.value('(TransactionID)[1]', 'nvarchar(20)')
        ,T.C.value('(AMT)[1]', 'decimal(15,2)')
        ,T.C.value('(TransactionStatus)[1]', 'nvarchar(30)')
     FROM @xmlData.nodes('ProductServiceOutput') T(C)
  </code></pre>
  
  <p>Even after solving encoding error, I'm getting NULL values.
  Please help me to get the values.</p>