Looking at the example below, I want to create an output that looks like this:
<
Customers>
<Customer>
<id>1</id>
<type>Customer</type>
<name>John Smith</name>
</Customer>
<Customer>
<id>2</id>
<type>Customer</type>
<name>Steve Hind</name>
</Customer>
<Customer>
<id>3</id>
<type>Customer</type>
<name>Da Vinci</name>
</Customer>
</Customers>
The FOR XML AUTO just gives me the <Customer></Customer> bit. To wrap it in the <Customers> element, we have to UNION the root section with the detail row using a FOR XML EXPLICIT clause. The example below is self-explanatory and has been tested on Sql Server 2000.
Example:
create table #temp(
field1 int identity(1,1),
field2 varchar(50),
field3 varchar(50))
go
insert into #temp(field2, field3)
select 'Customer', 'John Smith'
insert into #temp(field2, field3)
select 'Customer', 'Steve Hind'
insert into #temp(field2, field3)
select 'Customer', 'Da Vinci'
SELECT 1 as Tag,
NULL as Parent,
NULL as [Customers!1!cid],
NULL as [Customers!1!name],
NULL as [Customer!2!id!Element],
NULL as [Customer!2!type!Element],
NULL as [Customer!2!name!Element]
UNION ALL
SELECT 2 as Tag,
1 as Parent,
NULL,
NULL,
t.field1,
t.field2,
t.field3
FROM #temp t
FOR XML EXPLICIT