Geek Answers Handbook

Convert SQL string to XML tag with value and attributes

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 line to an XML object like

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

If I use FOR XML AUTO, all columns are converted to an attribute XML. I can use FOR XML AUTO, ELEMENTSor use FOR XML RAWto get tags instead, but I would like to convert the columns to Aboth Ban attribute and use the column Cas the value of the tag.

How can I get this result?

+4
source share
1 answer

In almost all cases FOR XML PATH, 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];

Result

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

More articles:


All Articles