Jaa

How to nest XML output in a SQL SELECT statement

  • Artikkeli

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:

Before

<BusinessPartner>

    <Key>1234</Key>

    <Name>Test Company</Name>  

</BusinessPartner>

After

<BusinessPartner>

    <Key>1234</Key>

    <CompanyDetails>  

        <Name>Test Company</Name>

    </CompanyDetails> 

</BusinessPartner>

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 [key],

    (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 @BusinessPartner
FOR XML PATH('BusinessPartner')

Nested XML Example.sql

Comments

  • Anonymous
    August 25, 2014
    Good sample!!! Thanks for sharing