CODEWITHSUNDEEP
xmlsql-server-2008
Nagesh
Nagesh

Reputation: 1308

Parsing web service response (in XML) in SQL Server using XML

I have a SQL Server procedure which calls a web service and the web service returns response in the following format:

<?xml version="1.0" encoding="UTF-8" ?>  
 <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"/>

I would like to parse this XML and get the values of ProductID, TransactionID, Amt, TransactionStatus and TxnDate.

I've tried the following SQL, but I'm getting encoding error. 

DECLARE @sql NVARCHAR(MAX)
SET @sql = '<?xml version="1.0" encoding="UTF-8" ?>  
 <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"/>'

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)

Even after solving encoding error, I'm getting NULL values. Please help me to get the values.

Upvotes: 1

Views: 363

Answers (1)

Epistaxis
Epistaxis

Reputation: 211

Please review the image and/or code. You nearly had it! :) 

  DECLARE @sql NVARCHAR(MAX)
SET @sql = '
 <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"/>'

DECLARE @xmlData XML
SET @xmlData = CONVERT(XML, @sql, 1);
SELECT 
      T.C.value('@ProductID[1]', 'int') AS ProductId
      ,T.C.value('@TransactionID[1]', 'nvarchar(20)') As TransactionID
      ,T.C.value('@AMT[1]', 'decimal(15,2)') AS AMT
      ,T.C.value('@TransactionStatus[1]', 'nvarchar(30)') AS TransactionStatus
   FROM @xmlData.nodes('ProductServiceOutput') T(C)

Upvotes: 2

Related Questions