XML PATH - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

XML PATH

Author

Topic

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2009-06-15 : 09:45:14

Hello,

I have been trying to get an output something like this using FOR XML PATH.

Here is test data and the script. Can someone help me?

Test data:
declare @tab table (col1 int, col2 int, col3 varchar(30))
insert into @tab (col1, col2, col3) values (1,10,'zzz')
insert into @tab (col1, col2, col3) values (2,25,'xxxx')
insert into @tab (col1, col2, col3) values (3,44,'yyyy')

select * from @tab

select
count(1) as empcount,
col1 as empid,
col2,
col3
from @tab
group by col1, col2, col3
for xml path('employee'), root ('employees')

And the above query gives me something like this,
<employees>
<employee>
<empcount>1</empcount>
<empid>1</empid>
<col2>10</col2>
<col3>zzz</col3>
</employee>
<employee>
<empcount>1</empcount>
<empid>2</empid>
<col2>25</col2>
<col3>xxxx</col3>
</employee>
<employee>
<empcount>1</empcount>
<empid>3</empid>
<col2>44</col2>
<col3>yyyy</col3>
</employee>
</employees>

Instead I need data the XML output like this.
<employees>
<empcount>3</empcount>
<employee>
<empid>1</empid>
<col2>10</col2>
<col3>zzz</col3>
</employee>
<employee>
<empid>2</empid>
<col2>25</col2>
<col3>xxxx</col3>
</employee>
<employee>
<empid>3</empid>
<col2>44</col2>
<col3>yyyy</col3>
</employee>
</employees>

Thanks in advance!

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 10:18:23

[code]declare @tab table (col1 int, col2 int, col3 varchar(30))
insert into @tab (col1, col2, col3) values (1,10,'zzz')
insert into @tab (col1, col2, col3) values (2,25,'xxxx')
insert into @tab (col1, col2, col3) values (3,44,'yyyy')

select * from @tab

SELECT count(*) AS empcount,
(
select col1 as empid,
col2,
col3
from @tab
for xml path('employee'), type
)
from @tab
FOR XML path(''),root('employee')[/code]

E 12°55'05.63"
N 56°04'39.26"

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2009-06-16 : 08:25:36

Thanks a bunch for the help. It works like a charm!

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources