In SQL 2000, I have 2 tables, in a 1-to-many r/ship, like this:A standard query to get Order and Item rows would be:SELECT o.ordernumber, i.itemnumber, i.descriptionFROM SalesOrder o INNER JOIN Item i ON o.ordernumber = i.ordernumber
Which of course results in 1 row returned per Item.However, I'd like to "flatten" the results, so there is 1 row returned *per SalesOrder*, with each Item row turned into a single XML string field.For example, instead of this result:ordernumber itemnumber description----------- ---------- ----------- 1 1 foo 1 2 bar 2 3 morefooetc
I'd like to get this result:ordernumber ItemXML----------- ------------------- 1 <row itemnumber="1" description="foo"/><row itemnumber="2" description="bar"/> 2 <row itemnumber="3" description="morefoo"/>etc
Can this be done in SQL 2000 using the FOR XML function?