Clever Geek Handbook

SQL Server XML - setting node names from SQL value

How do I generate the node names in the statement SELECT FOR XML

?

Let's say I have a table:

declare @Products table (ID int, Name varchar(100))
insert into @Products (1, 'Balls')
insert into @Products (2, 'Paper')
insert into @Products (3, 'Beer')

I need the following XML output:

<Products>
    <Balls @ID=1/>
    <Paper @ID=2/>
    <Beer @ID=3/>
</Products>

If this is not possible, can I use SQL Server XML DML to accomplish this?

+3


source to share


1 answer


Well, I don't think you can do it with commands in a FOR XML PATH

straightforward manner.

So after a lot of thinking, I came up with the following solution, which is a bit awkward, but it works:

DECLARE @Products TABLE (ID int, Name varchar(100))

INSERT INTO @Products (id, name) 
VALUES (1, 'Balls'),
       (2, 'Paper'),
       (3, 'Beer')

SELECT 
    CAST('<' + NAME + ' id="' + CAST(id AS VARCHAR) + '" />' AS XML)
FROM 
    @Products
FOR XML PATH(''), ROOT('Products')


Output:

<Products>
  <Balls id="1" />
  <Paper id="2" />
  <Beer id="3" />
</Products>

And here is the SQL Fiddle

+3


source






More articles:


All Articles