SQL Server SELECT FOR XML How to get two elements at the same level

select-for-xml, sql-server

Solution

There is no "simple" way to do it. `FOR XML PATH|EXPLICIT|AUTO` will all require each top-level, output element to have the same name. And you can't UNION multiple `FOR XML` queries together (Sql Server 2012).

The direction you went in is the most reliable and flexible. Essentially, you have to add a separate column for each different element type you want to include. You could simplify your final attempt to this to get what you wanted:

SELECT
    (
        SELECT 1 AS [@OrdNum], 'Abc' AS [@Name]
            WHERE 1=1
            FOR XML PATH ('a'), TYPE
    )
    , 
    (
        SELECT 4 AS [@Age], 'M' AS [@Sex], 'John' AS [@FirstName]
            WHERE 1=1
            FOR XML PATH ('b'), TYPE
    )
    FOR XML PATH ('Data'), TYPE;

The above query outputs:

<Data>
  <a OrdNum="1" Name="Abc" />
  <b Age="4" Sex="M" FirstName="John" />
</Data>

When you use `FOR XML PATH`, the column aliases are XPaths. So to make it an attribute name, you have to prefix with '@'---which then requires you to escape the alias (hence the `[]`). The parameter on `PATH` dictates the name of each row's Xml element. The `TYPE` option says to keep the output as type Xml instead of nvarchar(max), which means that the outer query can merge it better. And the outer query just has 2 columns to stuff into the single element it represents. Finally, I like the `WHERE 1=1`, but it's not syntactically required.

A tangent: I know your example is simplified, so you may wish to know that Xml data types can have "methods" applied to them. For example, say you wanted the above, but an outer query only needed the "b" elements. You could use the `query()` method to select only parts of the Xml to merge into some outer query.

SELECT 
    (
        SELECT
            (
                SELECT 1 AS [@OrdNum], 'Abc' AS [@Name]
                    WHERE 1=1
                    FOR XML PATH('a'), TYPE
            )
            , 
            (
                SELECT 4 AS [@Age], 'M' AS [@Sex], 'John' AS [@FirstName]
                    WHERE 1=1
                    FOR XML PATH('b'), TYPE
            )
            FOR XML PATH('Data'), TYPE
    ).query('Data/b');

Which produces this:

<b Age="4" Sex="M" FirstName="John" />

Problem

Original source