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

Question

I have two different pieces of XML to put together.

For example, SQL for the first piece looks like this:

SELECT
  *
FROM
  (
    SELECT 1 AS OrdNum, 'Abc'       AS Name
  ) a
FOR XML
  AUTO,
  TYPE

Once executed, you'll get this:

<a OrdNum="1" Name="Abc" />

The second one is here:

SELECT
  *
FROM
  (
     SELECT 4  AS Age, 'M'  AS Sex,   'John'      AS FirstName
  ) b
FOR XML
  AUTO,
  TYPE

You'll get this:

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

Now I'll put the two pieces together:

SELECT
  *
FROM
  (

    SELECT
      (
        SELECT
          *
        FROM
          (
            SELECT 1 AS OrdNum, 'Abc'       AS Name
          ) a
        FOR XML
          AUTO,
          TYPE
      ) AS aa

      ,

      (
        SELECT
          *
        FROM
          (
             SELECT 4  AS Age, 'M'  AS Sex,   'John'      AS FirstName
          ) b
        FOR XML
          AUTO,
          TYPE
      ) AS bb

  ) Data
FOR XML
  AUTO,
  ELEMENTS

The result is as follows:

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

But I do not want to have the elements "aa" and "bb" there. I'd love to get this:

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

But I have no idea how to achieve that.

Any hints?

Answer 1

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" />

Answer 2

You need to look at the FOR XML PATH option that SQL Server 2005 introduced - see the What's New in FOR XML in Microsoft SQL Server 2005 document for more information.

Basically, with FOR XML PATH, you can define the shape of your XML very easily. You can define certain structures, you can define certain columns to be output as attributes, and others as elements - totally under your control.

You can get more information on how to format that here:

enter link description here