I'm trying to create a xml output in SQL Server 2012 using FOR XML PATH.
What can be done to get the desired output?
I would like to output to be:
<types>
<type>
<type>FirstType</type>
<attribute>FirstAttribute</attribute>
</type>
</types>
<types>
<type>
<type>SecondType</type>
<attribute>SecondAttribute</attribute>
</type>
</types>
<types>
<type>
<type>ThirdType</type>
<attribute>ThirdAttribute</attribute>
</type>
</types>
My code:
DECLARE @table TABLE (
type VARCHAR(50)
, attribute VARCHAR(50)
)
SELECT T1.type
, T1.attribute
FROM @table AS T1
FOR XML path('type'), root('types')
Gives me wrong output:
<types>
<type>
<type>FirstType</type>
<attribute>FirstAttribute</attribute>
</type>
<type>
<type>SecondType</type>
<attribute>SecondAttribute</attribute>
</type>
<type>
<type>ThirdType</type>
<attribute>ThirdAttribute</attribute>
</type>
</types>
This returns what you want:
DECLARE @table TABLE (
[type] VARCHAR(50)
, attribute VARCHAR(50)
)
INSERT INTO @table VALUES('FirstType','FirstAttribute')
,('SecondType','SecondAttribute')
SELECT T1.type AS [type/type]
, T1.attribute AS [type/attribute]
FROM @table AS T1
FOR XML path('types');
But be warned: It is invalid due to the missing root node. SQL-Server's XML engine can deal with this, but other engines might fail...
I think, SQL Server is not so strict, as - very often - XML is built out of several fragments. And such a result could be one of these fragments without any problems...