With SQL Server 2005 we can generate XML output using different methods. Using TSQL keyword FOR XML along with AUTO, RAW, PATH and EXPLICIT we could generate almost any XML structure that we might need. PATH is a very powerful keyword which allows a great deal of customization on the structure of the generated XML and is relatively easy to use. EXPLICIT provides more control over the generated XML structure but it is much more complex then other methods. Most of the times, we could generate the same output as EXPLICIT by using PATH. But some times, the structure of the XML output might be too complex for PATH to generate, and we will have to go with EXPLICIT. PATH is available only in SQL Server 2005. If you are working with SQL server 2000, you will have to work with EXPLICIT if you need control over the XML structure being generated.
I had been helping some people on writing TSQL queries with EXPLICIT recently, at some of the Internet forums. My observation is that most of the times people get an error because of the sort order of the result set being passed to FOR XML EXPLICIT I worked with Vimal Rughani recently on such a query. After we wrote the query, he asked me if I could explain the flow of the code. I thought that it would be a good idea to write down the steps I went through while writing the query, so that it will help other people around too. He wanted to generate the following XML output using FOR XML EXPLICIT.
<Agents>
<Agent AgentID="1">
<Fname>Vimal</Fname>
<SSN>123-23-4521</SSN>
<AddressCollection>
<Address>
<AddressType>Home</AddressType>
<Address1>abc</Address1>
<Address2>xyz road</Address2>
<City>RJ</City>
</Address>
<Address>
<AddressType>Office</AddressType>
<Address1>temp</Address1>
<Address2>ppp road</Address2>
<City>RJ</City>
</Address>
</AddressCollection>
</Agent>
<Agent AgentID="2">
<Fname>Jacob</Fname>
<SSN>321-52-4562</SSN>
<AddressCollection>
<Address>
<AddressType>Home</AddressType>
<Address1>xxx</Address1>
<Address2>aaa road</Address2>
<City>NY</City>
</Address>
<Address>
<AddressType>Office</AddressType>
<Address1>ccc</Address1>
<Address2>oli Com</Address2>
<City>CL</City>
</Address>
<Address>
<AddressType>Temp</AddressType>
<Address1>eee</Address1>
<Address2>olkiu road</Address2>
<City>CL</City>
</Address>
</AddressCollection>
</Agent>
<Agent AgentID="3">
<Fname>Tom</Fname>
<SSN>252-52-4563</SSN>
<AddressCollection>
<Address>
<AddressType>Home</AddressType>
<Address1>ttt</Address1>
<Address2>loik road</Address2>
<City>NY</City>
</Address>
</AddressCollection>
</Agent>
</Agents>
The data should come from two tables Agents and Addresses. Before we write the query, we need to create those tables and populate them with some data. For the purpose of this example, we may not need any physical tables. We could go with memory tables. The following code will create two memory tables and fill them with data. The code is written by Kent in one of the MSDN forums.
/*
Borrowed from Kent's code
*/
declare @agent table
(
AgentID int,
Fname varchar(5),
SSN varchar(11)
)
insert into @agent
select 1, 'Vimal', '123-23-4521' union all
select 2, 'Jacob', '321-52-4562' union all
select 3, 'Tom', '252-52-4563'
declare @address table
(
AddressID int,
AddressType varchar(12),
Address1 varchar(20),
Address2 varchar(20),
City varchar(25),
AgentID int
)
insert into @address
select 1, 'Home', 'abc', 'xyz road', 'RJ', 1 union all
select 2, 'Office', 'temp', 'ppp road', 'RJ', 1 union all
select 3, 'Home', 'xxx', 'aaa road', 'NY', 2 union all
select 4, 'Office', 'ccc', 'oli Com', 'CL', 2 union all
select 5, 'Temp', 'eee', 'olkiu road', 'CL', 2 union all
select 6, 'Home', 'ttt', 'loik road', 'NY', 3
Let us start writing the query. Because we write this query for learning purpose, I would like to take an approach by which we will progressively develop the complete query.
So let us start with the root node. Let us first create the query for generating the root node.
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'Agents!1!'
FOR XML EXPLICIT
This will generate the root node that we need.
Now let us write the code for generating next level. The next level is the agent node. This information should come from the agent table. Let us add the code for that.
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'Agents!1!',
NULL AS 'Agent!2!AgentID'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
AgentID
FROM @agent
FOR XML EXPLICIT
Note the code in yellow. This is what we added to the previous version. This query generates the following output.
<Agents>
<Agent AgentID="1" />
<Agent AgentID="2" />
<Agent AgentID="3" />
</Agents>
Good so far. Let us add fname and ssn under the agent node as child elements.
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'Agents!1!',
NULL AS 'Agent!2!AgentID',
NULL AS 'Agent!2!Fname!Element',
NULL AS 'Agent!2!SSN!Element'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
AgentID,
Fname,
SSN
FROM @agent
FOR XML EXPLICIT
This version will give us the following output.
<Agents>
<Agent AgentID="1">
<Fname>Vimal</Fname>
<SSN>123-23-4521</SSN>
</Agent>
<Agent AgentID="2">
<Fname>Jacob</Fname>
<SSN>321-52-4562</SSN>
</Agent>
<Agent AgentID="3">
<Fname>Tom</Fname>
<SSN>252-52-4563</SSN>
</Agent>
</Agents>
FOR XML EXPLICIT Tutorial- Part 1
FOR XML EXPLICIT Tutorial - Part 2
FOR XML EXPLICIT Tutorial – Part 3
FOR XML EXPLICIT Tutorial – Part 4
FOR XML Tutorials