FOR XML EXPLICIT – Part 2
Posted on December 5th, 2007 | by Admin |Continued from Part 1
Let us move ahead. Under each agent, we need a node named AddressCollection. Let us add the code for that.
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’,
NULL AS ‘AddressCollection!3!Element’
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
NULL, NULL, NULL, NULL,
NULL
FROM @agent
FOR XML EXPLICIT
We added a new level for AddressCollection element. I used FROM @agent because we need an AddressCollection element for each agent record. Here is the output.
Vimal
123-23-4521
Jacob
321-52-4562
Tom
252-52-4563
Wait a second! we have a problem. Note that the 3 AddressCollection elements were created as part of the last node. Why does this happen? To understand that we need to look at the query results that we passed to FOR XML EXPLICIT. Let us run the query without FOR XML EXPLICIT.
Let us move ahead. Under each agent, we need a node named AddressCollection. Let us add the code for that.
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’,
NULL AS ‘AddressCollection!3!Element’
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
NULL, NULL, NULL, NULL,
NULL
FROM @agent
FOR XML EXPLICIT
We added a new level for AddressCollection element. I used FROM @agent because we need an AddressCollection element for each agent record. Here is the 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>
<AddressCollection />
<AddressCollection />
<AddressCollection />
</Agent>
</Agents>
Wait a second! we have a problem. Note that the 3 AddressCollection elements were created as part of the last node. Why does this happen? To understand that we need to look at the query results that we passed to FOR XML EXPLICIT. Let us run the query without FOR XML EXPLICIT.
| Tag | Parent | Agents!1! | Agent!2!AgentID | Agent!2!Fname | Agent!2!ssn | AddressCollection!3 |
| 1 | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | NULL | 1 | Vimal | 123-23-4521 | NULL |
| 2 | 1 | NULL | 2 | Jacob | 321-52-4562 | NULL |
| 2 | 1 | NULL | 3 | Tom | 252-52-4563 | NULL |
| 3 | 2 | NULL | NULL | NULL | NULL | NULL |
| 3 | 2 | NULL | NULL | NULL | NULL | NULL |
| 3 | 2 | NULL | NULL | NULL | NULL | NULL |
Note the rows in yellow . These are the records with tag 3. Note that they appear at the bottom of the result set. That is the reason why they appear at the bottom of the XML result. So to fix this, we need to change the order of the rows. So to get the correct XML we need to have the query results in the following order.
| Tag | Parent | Agents!1! | Agent!2!AgentID | Agent!2!Fname | Agent!2!ssn | AddressCollection!3 |
| 1 | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | NULL | 1 | Vimal | 123-23-4521 | NULL |
| 3 | 2 | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | NULL | 2 | Jacob | 321-52-4562 | NULL |
| 3 | 2 | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | NULL | 3 | Tom | 252-52-4563 | NULL |
| 3 | 2 | NULL | NULL | NULL | NULL | NULL |
So at this stage, we need to write some kind of code to alter the sort order of the records. There might be different ways to do that. What I did was to add a calculated column for the sort order based on the AgentID. Here is the new code.
SELECT
1 AS Tag,
NULL AS Parent,
0 AS Sort,
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’
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
AgentID * 100 AS Sort,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
AgentID * 100 + 1 AS Sort,
NULL, NULL, NULL, NULL,
NULL
FROM @agent
ORDER BY Sort
| Tag | Parent | Sort | Agents!1! | Agent!2!AgentID | Agent!2!Fname | Agent!2!ssn | AddressCollection!3 |
| 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | 100 | NULL | 1 | Vimal | 123-23-4521 | NULL |
| 3 | 2 | 101 | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | 200 | NULL | 2 | Jacob | 321-52-4562 | NULL |
| 3 | 2 | 201 | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | 300 | NULL | 3 | Tom | 252-52-4563 | NULL |
| 3 | 2 | 301 | NULL | NULL | NULL | NULL | NULL |
Well, that worked. Note that the sort order holds correct values so that we get the records in the desired order. There might be different ways to generate the sort order column. For the purpose of this example, I made it by multiplying the AgentID with 100, 101 etc. This approach may not work in a different situation. It worked for the example. The KEY here is to sort the records in the correct order (exactly in the order that we need them in the XML results). You can apply your own logic that you feel right, to achieve this.
Let us generate the XML now.
SELECT
1 AS Tag,
NULL AS Parent,
0 AS Sort,
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’
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
AgentID * 100 AS Sort,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
AgentID * 100 + 1 AS Sort,
NULL, NULL, NULL, NULL,
NULL
FROM @agent
ORDER BY Sort
FOR XML EXPLICIT
unfortunately, this code will not work. If you try to run this, you will get the following error.
TOSHIBA-USER\SQL2005(TOSHIBA-USER\Jacob Sebastian): Msg 6802, Level 16, State 1, Line 33
FOR XML EXPLICIT query contains the invalid column name ‘Sort’. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive integer.
The error is caused by the “Sort” column that we just added. When we use FOR XML EXPLICIT, all columns other than “Tag” and “Parent” should be in the form of “[TAG]![TAGID]!ATTRIBUTE…”. We need to hide the “Sort” column. Lets create an outer query to do this.
SELECT Tag, Parent,
[Agents!1!],
[Agent!2!AgentID],
[Agent!2!Fname!Element],
[Agent!2!SSN!Element],
[AddressCollection!3!Element]
FROM (
SELECT
1 AS Tag,
NULL AS Parent,
0 AS Sort,
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’
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
AgentID * 100 AS Sort,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
AgentID * 100 + 1 AS Sort,
NULL, NULL, NULL, NULL,
NULL
FROM @agent
) A
ORDER BY Sort
FOR XML EXPLICIT
Here is the result.
<Agents>
<Agent AgentID=“1“>
<Fname>Vimal</Fname>
<SSN>123-23-4521</SSN>
<AddressCollection />
</Agent>
<Agent AgentID=“2“>
<Fname>Jacob</Fname>
<SSN>321-52-4562</SSN>
<AddressCollection />
</Agent>
<Agent AgentID=“3“>
<Fname>Tom</Fname>
<SSN>252-52-4563</SSN>
<AddressCollection />
</Agent>
</Agents>
Note the rows in yellow . These are the records with tag 3. Note that they appear at the bottom of the result set. That is the reason why they appear at the bottom of the XML result. So to fix this, we need to change the order of the rows. So to get the correct XML we need to have the query results in the following order.
Tag Parent Agents!1! Agent!2!AgentID Agent!2!Fname Agent!2!ssn AddressCollection!3 1 NULL NULL NULL NULL NULL NULL 2 1 NULL 1 Vimal 123-23-4521 NULL 3 2 NULL NULL NULL NULL NULL 2 1 NULL 2 Jacob 321-52-4562 NULL 3 2 NULL NULL NULL NULL NULL 2 1 NULL 3 Tom 252-52-4563 NULL 3 2 NULL NULL NULL NULL NULL
So at this stage, we need to write some kind of code to alter the sort order of the records. There might be different ways to do that. What I did was to add a calculated column for the sort order based on the AgentID. Here is the new code.
SELECT
1 AS Tag,
NULL AS Parent,
0 AS Sort,
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’
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
AgentID * 100 AS Sort,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
AgentID * 100 + 1 AS Sort,
NULL, NULL, NULL, NULL,
NULL
FROM @agent
ORDER BY Sort
Let us move ahead. Under each agent, we need a node named AddressCollection. Let us add the code for that.
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’,
NULL AS ‘AddressCollection!3!Element’
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
NULL, NULL, NULL, NULL,
NULL
FROM @agent
FOR XML EXPLICIT
We added a new level for AddressCollection element. I used FROM @agent because we need an AddressCollection element for each agent record. Here is the 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>
<AddressCollection />
<AddressCollection />
<AddressCollection />
</Agent>
</Agents>
Wait a second! we have a problem. Note that the 3 AddressCollection elements were created as part of the last node. Why does this happen? To understand that we need to look at the query results that we passed to FOR XML EXPLICIT. Let us run the query without FOR XML EXPLICIT.
| Tag | Parent | Agents!1! | Agent!2!AgentID | Agent!2!Fname | Agent!2!ssn | AddressCollection!3 |
| 1 | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | NULL | 1 | Vimal | 123-23-4521 | NULL |
| 2 | 1 | NULL | 2 | Jacob | 321-52-4562 | NULL |
| 2 | 1 | NULL | 3 | Tom | 252-52-4563 | NULL |
| 3 | 2 | NULL | NULL | NULL | NULL | NULL |
| 3 | 2 | NULL | NULL | NULL | NULL | NULL |
| 3 | 2 | NULL | NULL | NULL | NULL | NULL |
Note the rows in yellow . These are the records with tag 3. Note that they appear at the bottom of the result set. That is the reason why they appear at the bottom of the XML result. So to fix this, we need to change the order of the rows. So to get the correct XML we need to have the query results in the following order.
| Tag | Parent | Agents!1! | Agent!2!AgentID | Agent!2!Fname | Agent!2!ssn | AddressCollection!3 |
| 1 | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | NULL | 1 | Vimal | 123-23-4521 | NULL |
| 3 | 2 | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | NULL | 2 | Jacob | 321-52-4562 | NULL |
| 3 | 2 | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | NULL | 3 | Tom | 252-52-4563 | NULL |
| 3 | 2 | NULL | NULL | NULL | NULL | NULL |
So at this stage, we need to write some kind of code to alter the sort order of the records. There might be different ways to do that. What I did was to add a calculated column for the sort order based on the AgentID. Here is the new code.
SELECT
1 AS Tag,
NULL AS Parent,
0 AS Sort,
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’
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
AgentID * 100 AS Sort,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
AgentID * 100 + 1 AS Sort,
NULL, NULL, NULL, NULL,
NULL
FROM @agent
ORDER BY Sort
| Tag | Parent | Sort | Agents!1! | Agent!2!AgentID | Agent!2!Fname | Agent!2!ssn | AddressCollection!3 |
| 1 | NULL | 0 | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | 100 | NULL | 1 | Vimal | 123-23-4521 | NULL |
| 3 | 2 | 101 | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | 200 | NULL | 2 | Jacob | 321-52-4562 | NULL |
| 3 | 2 | 201 | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | 300 | NULL | 3 | Tom | 252-52-4563 | NULL |
| 3 | 2 | 301 | NULL | NULL | NULL | NULL | NULL |
Well, that worked. Note that the sort order holds correct values so that we get the records in the desired order. There might be different ways to generate the sort order column. For the purpose of this example, I made it by multiplying the AgentID with 100, 101 etc. This approach may not work in a different situation. It worked for the example. The KEY here is to sort the records in the correct order (exactly in the order that we need them in the XML results). You can apply your own logic that you feel right, to achieve this.
Let us generate the XML now.
SELECT
1 AS Tag,
NULL AS Parent,
0 AS Sort,
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’
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
AgentID * 100 AS Sort,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
AgentID * 100 + 1 AS Sort,
NULL, NULL, NULL, NULL,
NULL
FROM @agent
ORDER BY Sort
FOR XML EXPLICIT
unfortunately, this code will not work. If you try to run this, you will get the following error.
TOSHIBA-USER\SQL2005(TOSHIBA-USER\Jacob Sebastian): Msg 6802, Level 16, State 1, Line 33
FOR XML EXPLICIT query contains the invalid column name ‘Sort’. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive integer.
The error is caused by the “Sort” column that we just added. When we use FOR XML EXPLICIT, all columns other than “Tag” and “Parent” should be in the form of “[TAG]![TAGID]!ATTRIBUTE…”. We need to hide the “Sort” column. Lets create an outer query to do this.
SELECT Tag, Parent,
[Agents!1!],
[Agent!2!AgentID],
[Agent!2!Fname!Element],
[Agent!2!SSN!Element],
[AddressCollection!3!Element]
FROM (
SELECT
1 AS Tag,
NULL AS Parent,
0 AS Sort,
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’
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
AgentID * 100 AS Sort,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
AgentID * 100 + 1 AS Sort,
NULL, NULL, NULL, NULL,
NULL
FROM @agent
) A
ORDER BY Sort
FOR XML EXPLICIT
Here is the result.
<Agents>
<Agent AgentID=“1“>
<Fname>Vimal</Fname>
<SSN>123-23-4521</SSN>
<AddressCollection />
</Agent>
<Agent AgentID=“2“>
<Fname>Jacob</Fname>
<SSN>321-52-4562</SSN>
<AddressCollection />
</Agent>
<Agent AgentID=“3“>
<Fname>Tom</Fname>
<SSN>252-52-4563</SSN>
<AddressCollection />
</Agent>
</Agents>
Sorry, comments for this entry are closed at this time.