A customer asked, "How to format [or structure] XML Output to include a sub-group inside the XML". The customer is using a SELECT statement with FOR XML, and wants to nest an XML element such as the following:
One way to nest an XML element in T-SQL is to use a subquery. The query for the flat XML output looks something like this:
SELECT [key], name FROM BusinessPartnerTable FOR XML PATH('BusinessPartner')
To create the nested CompanyDetails element, you can use a subquery:
(SELECT name as 'name'
FROM BusinessPartnerTable c
WHERE c.[key] = bp.[key]
ORDER BY [key]
FOR XML PATH(''), type
) as 'CompanyDetails'
FROM BusinessPartnerTable bp
GROUP BY [key]
FOR XML path('BusinessPartner'), root('root')
Notice the use of a subquery to nest the CompanyDetails element.
The attached Nested XML Example.sql file shows an example of how to nest XML in a SQL SELECT statement.
Alternatively, you can fine tune a SELECT column to control the output. For example, 'name' as 'CompanyDetails/Name' will nest the 'name' column in a CompanyDetails element.
SELECT [key], 'name' as 'CompanyDetails/Name'FROM @BusinessPartnerFOR XML PATH('BusinessPartner')