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