i purchase xml database;
 using mssql 2000
this code gives me an error (Incorrect syntax near 'XML'.)


select region.region as name, region.population , region.area,
 (Select Country.country as name, Country.currency,
   (Select state.state as name, state.nickname
    from state where state.country_id = Country.country_id FOR XML AUTO)
    from Country where Country.region_id = region.region_id FOR XML AUTO)
from region FOR XML AUTO, ROOT, elements

As far as I understood SQL 2000 does not support nested FOR XML queries, only SQL 2005 does that. For what you need you will have to use FOR XML EXPLICIT.

--

Joe (Microsoft MVP - XML)