Working with xml output in SQL Server.

Today I came across a nice article on MSSQLtips.com .
It says "Running a Dynamic Query against SQL Server without using Dynamic SQL".
This introduced me to the idea, that I could create xml output from a select query that I fire on a SQl Server (2000 & above).

Off-course this was not enough and I googled for more and found out many other links. A few noteworthy are as follows :

1) Nice Blog for XML Explicit : This blog shows the basic usage of 'For XML Explicit' syntax and example used for retrieving XML output from a simple Select query.

2) Retrieving XML from SQL Server Using SELECT ... FOR XML : Very good article. Explains Raw Mode, Auto Mode and Explicit Mode. Simple to understand.

3) How to export data from SQL Server 2000 to XML : Explains how to write the output to an Xml File. The idea is to create view based on the query used and then use "BCP QUERYOUT" to write the ourpur to an xml file with the help of "FOR XML" command.

4) XML in SQL Server 2000 and SQLXML :
Good explaination of the "For XML" Syntax. Along with some code in .NET with ADO objects.
I will say its a must read.

After going through the database I created some output with in my database. The Best query I found was on link number 4. I used bcp Queryout to create xml file. But I also took two approaches 1) to use FOR XML EXPLICIT and 2) to use FOR XML Auto, Bith of these create different outputs, though I am sure we can modify the query for EXPLICIT for a desired ouput.

Assume that there is a table which stores if the days of the current month are holidays in a particular country.
Now assume that there are three columns which are used to store this information. Namely "CountryId", "DAYID" and "Holiday"
Then using

1)
Create view XMlOutput as
(
Select 1 as [TAG], NULL as [Parent] , NULL as [Countries!1!],
NULL as [Country!2!CountryID] ,
NULL as [DAY!3!DAYID],
NULL as [DAY!3!Holiday!Element],

Union

(SELECT 2 as [TAG] , 1 as [Parent], NULL, CountryID , NULL , NULL, NULL
from CountryHolidays )

UNION
(SELECT 3 as [TAG] , 2 as [Parent], NULL, NULL , DAYID , Holiday from CountryHolidays )
)

now use this view to bcp Queryout the result to a XML file.

bcp "select * from TABLENAME..XMlOutput FOR XML EXPLICIT" queryout "D:\Akshay\data.xml" -w -r "" -T

Please find the output of the file here.

2) Using the For XML Auto command

bcp "Select 1 as TAG ,* from Country..CountryHolidays FOR XML AUTO, XMLDATA, BINARY BASE64, ELEMENTS" queryout "D:\Akshay\data.xml" -w -r "" -T
This will consider each row as a tag and each column will be a tag with the row as a parent.