Add a comment for XML output from SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Add a comment for XML output from SQL

Author

Topic

MMHM
Starting Member

1 Post

Posted - 2011-12-09 : 04:04:31

Add a comment for XML output from SQL using "FOR XML EXPLICIT"

Input SQL Query:

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

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>

Expected Output:

<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>
.
.
.
.
.
.
and so on

lappin
Posting Yak Master

182 Posts

Posted - 2011-12-09 : 07:16:08

http://stackoverflow.com/questions/1154306/generate-xml-comments-with-sql-for-xml-statement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources