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?
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
