XML and GROUP BY

Giganews Newsgroups
Subject: XML and GROUP BY
Posted by:  LsK_Lele (nospam@nospam.com)
Date: Thu, 20 Oct 2011

Hi,

I don't know how to solve the following problem: I need to retrieve data
from two tables with the following structures:

Table 1
_______________
| ID | Name  |
_______________
| 1  | Name 1 |
| 2  | Name 2 |
_______________

Table 2

___________________________
| ID | Parent ID | Value  |
___________________________
| 1  | 1        | aaaa  |
| 2  | 1        | bbbb  |
| 3  | 1        | cccc  |
| 4  | 2        | dddd  |
| 5  | 2        | eeee  |
___________________________

I need to return a dataset like this:

ID | Name | XML

Where ID and Name come from Table 1, and the last cell contains the XML
representation of Table 2 (join on Table1.ID=Table2.ParentID).
I can run a sub-query, but it's expensive and will slow down my query
when the dataset is really large. So the best would be joining the two
tables, using something like a "GROUP BY ParentID" clause on Table 2.
But I can't find a way to do this.
My idea was something like:

SELECT ID, ParentID, XmlField FROM
    Table1 t LEFT JOIN (
      SELECT ParentID, ID "id", Value "value"
          FROM Table2 GROUP BY ParentID
          FOR XML PATH('element'), ROOT('base')
    ) t2 ON t.ID=t2.ParentID

But the syntax is not correct...
Any suggestions?

Thank you

Replies