CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlsql-server-2012
32u-nd
32u-nd

Reputation: 239

Convert SQL row into XML tag with value and attibutes

I have a simple flat SQL table with three columns A, B and C:

CREATE TABLE [tmp]
(
  [A] NVARCHAR(32) NULL
, [B] NVARCHAR(32) NULL
, [C] NVARCHAR(32) NULL
);

I would like to convert each row to an XML object like

<Parameter name="A" description="B">666</Parameter>

If I use FOR XML AUTO, all columns are converted to an XML attribute. I can use FOR XML AUTO, ELEMENTS or use FOR XML RAW to get tags instead, but I would like to convert columns A and B to an attribute and use column C as the tag value.

How can I get this result?

Upvotes: 4

Views: 886

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

In almost all cases FOR XML PATH is the best approach (and the fastest)

CREATE TABLE [tmp]
(
  [A] NVARCHAR(32) NULL
, [B] NVARCHAR(32) NULL
, [C] NVARCHAR(32) NULL
);
INSERT INTO [tmp] VALUES
 ('A','B','666')
,('One more A','And one more B','777');

SELECT A AS [@name]
      ,B AS [@description]
      ,C AS [*]
FROM [tmp]
FOR XML PATH('Paramter');


--Clean Up
GO
DROP TABLE [tmp];

The result

<Paramter name="A" description="B">666</Paramter>
<Paramter name="One more A" description="And one more B">777</Paramter>

Upvotes: 5

Related Questions