group columns from xml field - 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)

group columns from xml field

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-07-02 : 07:36:52

Hi

I have this query that query some columns an a xml column. I need to group the output based on the number of rows that have the same country value. Can someone help me out here?


SELECT t.u.value('(country/text())[1]', 'nvarchar(30)') as [country] ,
t.u.value('(address/text())[1]', 'nvarchar(30)') as [address],


tbl.DateAdded, ISNULL(tbl.BID, 0) AS BID, tbl.ID AS PID, tbl.DateDelivered


FROM tbl_FBook tbl
CROSS APPLY XmlData.nodes('/order/card')t(u) 
WHERE     (tbl.DateAdded BETWEEN CONVERT(DATETIME, '2008-05-20', 102) AND CONVERT(DATETIME, '2009-05-28', 102))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 11:39:51

[code]SELECT country, COUNT(*) AS NoOfRows, other values that you want
FROM
(
SELECT t.u.value('(country/text())[1]', 'nvarchar(30)') as [country] ,
t.u.value('(address/text())[1]', 'nvarchar(30)') as [address],

tbl.DateAdded, ISNULL(tbl.BID, 0) AS BID, tbl.ID AS PID, tbl.DateDelivered

FROM tbl_FBook tbl
CROSS APPLY XmlData.nodes('/order/card')t(u)
WHERE (tbl.DateAdded BETWEEN CONVERT(DATETIME, '2008-05-20', 102) AND CONVERT(DATETIME, '2009-05-28', 102))
)t
GROUP BY country
[/code]

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-07-02 : 11:53:13

Wonderful, Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 12:03:17

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources