Custom XML from a Query

Most of us knows that SQL Server 2000 supports following xml clause:

1. FOR XML AUTO
2. FOR XML RAW
3. FOR XML EXPLICIT

Using any of these we can get the xml output from a query. We will be using FOR XML Explicit for getting the custom format of the xml, but it is little difficult in writing the query where we have to use set operator "UNION" for the set of queries.

Today one of my team member asked a question where he is looking for an xml from the Excel file and he said it is a one time implementation with the custom format. I thought of sharing with everyone through my blog:

Following is the Excel format:

Following is the output format they are looking for:
<root>
<sup id="101" name="101_Supplier">
<site id="S101">S101_Name</site>
<site id="S102">S102_Name</site>
<site id="S103">S103_Name</site>
</sup>
<sup id="102" name="102_Supplier">
<site id="S102">S102_Name</site>
<site id="S103">S103_Name</site>
<site id="S105">S105_Name</site>
</sup>
</root>

If we observer the xml format it is not a striaght farword, because each suplier has one to many relationship. We can achive it using FOR XML Path in SQL Server 2005 onwards. Here are the steps to follow:

We can create the table in sql server with the same format mentioned in the excel. Here is the sample script after loading the data into the sql server table using export import wizard:

CREATE TABLE [dbo].[SUPDATA](
[SUPPLIER_ID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUPPLIER_NAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SITE_DIVISION_ID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SITE_DIVISION_NAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Following is the query to get the data in the required xml format:

SELECT
SUPPLIER_ID '@id',
SUPPLIER_NAME '@name',
(
SELECT SITE_DIVISION_ID 'site/@id',
SITE_DIVISION_NAME 'site'
FROM SUPDATA I
WHERE I.SUPPLIER_ID = O.SUPPLIER_ID
FOR XML PATH (''), type
)

FROM
(SELECT DISTINCT SUPPLIER_ID, SUPPLIER_NAME FROM dbo.SUPDATA) O
FOR XML path('sup'),root('root')

If you observe carefully in the query, we used type keyword following FOR XML PATH to make sure that the inner query returns xml and should be available without any tag encryption.

This type of queries helps us in converting data into the required xml formats.