SQL Server XML - setting node names from SQL value

Question

How do I generate node names in a SELECT FOR XML statement?

Say I have the 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 want the following XML output:

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

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

Answer 1

Well, I don't think you can do that with FOR XML PATH commands in straight forward way.

So after thinking a lot i came up with following solution which is something 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