Hi everyone, I've got a problem with using for xml explicit in SQL Server 2000 (so I can't use the new path() stuff from sql 2005/8)

Essentially I have two tables and the XML structure I want to have is

<xml>
  <table_1 field1="foo" field2="foobar2" field3="foobar3">
      <a_row_from_table_2 field1="goo" field2="goobar2" field3="goobar3" />
      <a_row_from_table_2 field1="hoo" field2="hoobar2" field3="hoobar3" />
  </table_1>
</xml>

That is, table_1 has a one-to-many relationship with table_2, and I want to make a hierarchy of it.

So far I can't seem to get it, the closest I've managed to get is all the records from table1, with all the records from table2 appended to the very last element of table1

Any help with setting up this kind of relationship would be greatly appreciated.

-Marcin