Add attributes to a xml root element



I'm developing using SQL Server 2012 express and developer solution.


I need to generate this xml:



<Co BNumber="B001" Username="" Annothation="Nothing">
<Item PId="57151" IId="005CYEHE19N35B" ILevelId="0" IFlag="20" Time="2014-12-16T14:34:03.743"/>
</Co>


But at this moment I'm generating this:



<Co>
<Item PId="57151" IId="005CYEHE19N35B" ILevelId="0" IFlag="20" Time="2014-12-16T14:34:03.743" Username="" Source="PLS"/>
</Co>


This is my SQL code:



DECLARE @xml_var XML
SET @xml_var =
(
SELECT ID_CODE AS '@PId',
CODE AS '@IId',
CODE_LEVEL AS '@ILevelId',
COMMISIONING_FLAG AS '@IFlag',
TIMESPAN AS '@Time',
USERNAME AS '@Username',
SOURCE AS '@Source'
FROM #CODES
FOR XML PATH('Item'),
ROOT('Co')
)


Temp #CODES table is structured like this:



CREATE TABLE #codes
(
ID_CODE bigint,
CODE_LEVEL tinyint,
CODE nvarchar(20),
BNUMBER nvarchar(50),
COMMISIONING_FLAG tinyint,
IS_TRANSMITTED bit,
TIMESPAN datetime,
USERNAME nvarchar(50),
SOURCE nvarchar(50)
)


What do I have to do add properties to the <Co> root element?


No comments:

Post a Comment