|Subject:||XML and GROUP BY|
|Posted by:||LsK_Lele (email@example.com)|
|Date:||Thu, 20 Oct 2011|
I don't know how to solve the following problem: I need to retrieve data
from two tables with the following structures:
| ID | Name |
| 1 | Name 1 |
| 2 | Name 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...