FOR XML EXPLICIT – Part 3
Posted on December 5th, 2007 | by Admin |Having fixed the problem with the sort order, let us go ahead with the rest of the code. Let us add Addresses under the AddressCollection node and come up with the final version of the code. We need to add a new level, Tag 4. Note that I used AgentID * 102 to make sure that this record will come right below the AddressCollection row of each Agent.
SELECT Tag, Parent,
[Agents!1!],
[Agent!2!AgentID],
[Agent!2!Fname!Element],
[Agent!2!SSN!Element],
[AddressCollection!3!Element],
[Address!4!AddressType!Element],
[Address!4!Address1!Element],
[Address!4!Address2!Element],
[Address!4!City!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’,
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
) A
ORDER BY Sort
FOR XML EXPLICIT
Here is the complete listing of the code.
/*
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
/*
End Borrow
*/
SELECT Tag, Parent,
[Agents!1!],
[Agent!2!AgentID],
[Agent!2!Fname!Element],
[Agent!2!SSN!Element],
[AddressCollection!3!Element],
[Address!4!AddressType!Element],
[Address!4!Address1!Element],
[Address!4!Address2!Element],
[Address!4!City!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’,
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
) A
ORDER BY Sort
FOR XML EXPLICIT
/*
OUTPUT:
<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>
*/
Sorry, comments for this entry are closed at this time.