sql server 2000 and for xml explicit

Question

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

Answer 1

Does your query include an order by clause? In for xml explicit mode, it's important that information appears in the sequence it's needed. For example, in the Northwind example database, you can generate a nested XML for employee territories like:

select
    1 as Tag,
    Null as Parent,
    e.EmployeeId as [Employee!1!EmployeeId],
    e.LastName as [Employee!1!LastName],
    Null as [EmployeeTerritories!2!TerritoryID]
from dbo.Employees e
union all
select 2 as Tag,
    1 as Parent,
    et.EmployeeId as [Employee!1!EmployeeId],
    Null as [Employee!1!LastName],
    et.TerritoryId as [EmployeeTerritories!2!TerritoryID]
from dbo.EmployeeTerritories et
order by [Employee!1!EmployeeId], Parent
for xml explicit

This results in an XML like:

<Employee EmployeeId="1" LastName="Davolio">
  <EmployeeTerritories TerritoryID="06897" />
  <EmployeeTerritories TerritoryID="19713" />
</Employee>
<Employee EmployeeId="2" LastName="Fuller">
...

To get an idea of how it works, run the query without the for xml explicit.