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

Question

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.

Epistaxis · Accepted Answer · 2017-12-20 13:29:32Z

2

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)

answered Dec 20, 2017 at 13:29

Epistaxis

2111 silver badge6 bronze badges

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

2 Comments

Nagesh Over a year ago

Thanks a lot @Epistaxis. I've missed the @ symbol :)

Epistaxis Over a year ago

No probs! We all miss something sometime.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related