'FOR XML PATH' to create hierarchy for report

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)

'FOR XML PATH' to create hierarchy for report

Author

Topic

hamble18
Starting Member

12 Posts

Posted - 2008-09-04 : 18:21:56

I am trying to generate heirarchical XML (grouped report style) from a single table.

This looks like it should be simple, but I can't see a way to do it. The only solution I've found looking around is to create a function for the sub-query, which I'd like to avoid.

A sample table is created as follows:


DECLARE @Temp TABLE (date datetime, country char(2))
INSERT INTO @Temp VALUES ('20080801', 'GB')
INSERT INTO @Temp VALUES ('20080801', 'GB')
INSERT INTO @Temp VALUES ('20080801', 'FR')
INSERT INTO @Temp VALUES ('20080801', 'FR')
INSERT INTO @Temp VALUES ('20080802', 'DE')
INSERT INTO @Temp VALUES ('20080802', 'GB')
INSERT INTO @Temp VALUES ('20080802', 'FR')
INSERT INTO @Temp VALUES ('20080802', 'FR')

The following returns XML with one line per row:


SELECT 
   date AS [@date],
   country AS [@Country],
   COUNT(*) AS [@qty] 
FROM @temp 
GROUP BY date,country 
ORDER BY date,country 
FOR XML PATH('Row'),TYPE

<Row date="2008-08-01T00:00:00" Country="FR" qty="2" />
<Row date="2008-08-01T00:00:00" Country="GB" qty="2" />
<Row date="2008-08-02T00:00:00" Country="DE" qty="1" />
<Row date="2008-08-02T00:00:00" Country="FR" qty="2" />
<Row date="2008-08-02T00:00:00" Country="GB" qty="1" />

However, I'd like to group by date, to return:

<date date="2008-08-01T00:00:00">
<Row Country="FR" qty="2" />
<Row Country="GB" qty="2" />
</date>
<date date="2008-08-02T00:00:00">
<Row Country="DE" qty="1" />
<Row Country="FR" qty="2" />
<Row Country="GB" qty="1" />
</date>

Surely it must be possible to do this in a single query - all the information is there, it is simply a matter of presenting it.

Any ideas greatly received.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 04:33:07

[code]DECLARE @Temp TABLE (date datetime, country char(2))
INSERT INTO @Temp VALUES ('20080801', 'GB')
INSERT INTO @Temp VALUES ('20080801', 'GB')
INSERT INTO @Temp VALUES ('20080801', 'FR')
INSERT INTO @Temp VALUES ('20080801', 'FR')
INSERT INTO @Temp VALUES ('20080802', 'DE')
INSERT INTO @Temp VALUES ('20080802', 'GB')
INSERT INTO @Temp VALUES ('20080802', 'FR')
INSERT INTO @Temp VALUES ('20080802', 'FR')

SELECT t.[@date],
g.Country,
g.qty
FROM (
SELECT date AS [@date]
FROM @Temp
GROUP BY date
) AS t
CROSS APPLY (
SELECT Country,
COUNT(*) AS [qty]
FROM @Temp AS s
WHERE s.date = t.[@date]
GROUP BY Country
) AS g
FOR XML PATH('date'), type[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 04:33:17

[code]DECLARE @Temp TABLE (date datetime, country char(2))
INSERT INTO @Temp VALUES ('20080801', 'GB')
INSERT INTO @Temp VALUES ('20080801', 'GB')
INSERT INTO @Temp VALUES ('20080801', 'FR')
INSERT INTO @Temp VALUES ('20080801', 'FR')
INSERT INTO @Temp VALUES ('20080802', 'DE')
INSERT INTO @Temp VALUES ('20080802', 'GB')
INSERT INTO @Temp VALUES ('20080802', 'FR')
INSERT INTO @Temp VALUES ('20080802', 'FR')

select p
from (
SELECT '<date date="' + CONVERT(CHAR(19), date, 126) + '">' AS p,
date AS t,
0 AS w
FROM (
SELECT DISTINCT
Date
FROM @Temp
) AS d

UNION ALL

SELECT '</date>',
date AS t,
2 AS w
FROM (
SELECT DISTINCT
Date
FROM @Temp
) AS d

UNION ALL

SELECT '<Row Country="' + Country + '" qty="' + CAST(qty AS VARCHAR(11)) + '">',
date AS t,
1 AS w
FROM (
SELECT date,
Country,
COUNT(*) AS qty
FROM @Temp
GROUP BY date,
Country
) AS e
) AS r
ORDER BY t, w[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources