This is a late addition to the 3 part FOR XML TUTORIAL I wrote last year. You can find Part 1 here, Part 2 here and Part 3 here.
When generating the XML document, FOR XML EXPLICIT processes rows in the same order as they are returned by the query. So, most of the times, you need to specify an ORDER BY clause in your query, so that the XML output will contain information in the desired order. In Part 3, we used a calculated column to generate certain values and used those values to order the result. Since we did not want the 'artificial sort column' in the XML output, we used an outer query to filter out the sort column.
Here is the new version of the query using the 'hide' directive.
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
SELECT
1 AS Tag,
NULL AS Parent,
0 AS 'Agents!1!Sort!hide',
NULL AS 'Agents!1!',
NULL AS 'Agent!2!AgentID',
NULL AS 'Agent!2!Fname!Element',
NULL AS 'Agent!2!SSN!Element',
NULL AS 'AddressCollection!3!Element',
NULL AS 'Address!4!AddressType!Element',
NULL AS 'Address!4!Address1!Element',
NULL AS 'Address!4!Address2!Element',
NULL AS 'Address!4!City!Element'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
AgentID * 100,
NULL, AgentID, Fname, SSN,
NULL,NULL, NULL, NULL, NULL
FROM @Agent
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
AgentID * 100 + 1,
NULL,NULL,NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM @Agent
UNION ALL
SELECT
4 AS Tag,
3 AS Parent,
AgentID * 100 + 2,
NULL,NULL,NULL,NULL,NULL,
AddressType, Address1, Address2, City
FROM @Address
ORDER BY [Agents!1!Sort!hide]
FOR XML EXPLICIT
Note the usage of the "hide" directive on the column we generated for sorting. Columns marked with "hide" will be ignored by the XML processor. FOR XML EXPLICIT supports a few other interesting directives too. I will cover them in a future post.