Search code examples
sql-serverxmlt-sqlxpathfor-xml-path

SQL Server for xml path with double elements


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>

Solution

  • 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...