How to make an actual node around your Xml output

Looking at the example below, I want to create an output that looks like this:

<Customers>

<Customer>

<id>1id>

<type>Customertype>

<name>John Smithname>

Customer>

<Customer>

<id>2id>

<type>Customertype>

<name>Steve Hindname>

Customer>

<Customer>

<id>3id>

<type>Customertype>

<name>Da Vinciname>

Customer>

Customers>

The FOR XML AUTO just gives me the bit. To wrap it in the 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

SPONSORS: