CODEWITHSUNDEEP
sql-serverxml-deserialization
Skary
Skary

Reputation: 1362

SQL Server deserialize XML with OPENXML

I have read this documentation describing how to deserialize xml into table : https://learn.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql?view=sql-server-ver15

In my scenario each XML is a "table" with only one row, so I have written this code:

DECLARE @data AS NVARCHAR(4000) = N'<row ACOCLI="cod" ARAGS1="Some description" 
ACOFIS="cod" AEMAIL=" " ACOAGE="  " AANNUL=" " ANOMIG="Some Name"/>';
SELECT *  
FROM OPENXML (@data,'row' ,0);

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=94d10fa2f6c4f0eb580d5166c07f4553

It is not clear to me if I used the right root node, and in case 'row' is wrong, which is the correct node to set here. The error I get also is strange, it talk about cast to int, but there is no int there, just XML.

The error:

Msg 245 Level 16 State 1 Line 2 Conversion failed when converting the nvarchar value to data type int.

What I am expecting is a select result like that:

ACOCLI ARAGS1 ACOFIS AEMAIL ACOAGE AANNUL ANOMIG
cod Some description cod Some Name

Upvotes: 0

Views: 185

Answers (1)

Thom A
Thom A

Reputation: 95561

Seems like what you actually just need to is value operator:

DECLARE @data AS xml = N'<row ACOCLI="00330" ARAGS1="Some description" ACOFIS="01266820248" AEMAIL=" " ACOAGE="  " AANNUL=" " ANOMIG="Some Name"/>';

SELECT @data.value('(row/@ACOCLI)[1]','varchar(6)'),
       @data.value('(row/@ARAGS1)[1]','varchar(30)'),
       @data.value('(row/@ACOFIS)[1]','varchar(15)'),
       @data.value('(row/@AEMAIL)[1]','varchar(6)'),
       @data.value('(row/@ACOAGE)[1]','varchar(6)'),
       @data.value('(row/@AANNUL)[1]','varchar(6)'),
       @data.value('(row/@ANOMIG)[1]','varchar(15)');

Upvotes: 1

Related Questions