format of for xml auto

I am using sql server 2000 and want to know how to get xml out of the database that looks like this using for xml auto

<Clients>
<Client ID="1">
<Employer="Company1" />
<Employer="Company2" />
<Contact type="phone">
<contact type="email" value="test@.test.com">
<contact type="phone" value="555-5555">
</Client>
<Client ID="2">
<Employer="Company3" />
<Employer="Company4" />
<Contact type="phone">
<contact type="email" value="test@.test.com">
<contact type="phone" value="555-5555">
</Client>
</Clients>

The problem I am having is that Contact is nested inside employer when I select Employer before Contact and the opposite happens when I select Contact first. They both join to the Client table so I would assume they both should nest directly under Client. How do I get different fields to nest directly under the same element like above?

Show me your SQL so I can offer suggestions on how to change it.

Mark

|||

I was trying to create two different child elements under a parent. I figured out a way to do it using for xml explicit. If anyone else has a similiar need, here is a code sample I made for the Northwind database

SELECT

1

as Tag,nullas Parent,

c

.CustomerIDAs [c!1!CustomerID],

c

.ContactNameAs [c!1!CustomerName],NULLAs [cd!2!CustomerTypeID],NULLAs [o!3!OrderID]

FROM

customers c

UNION

SELECT

2

as Tag,

1

as Parent,

c

.CustomerID,NULL,

cd

.CustomerTypeID,NULL

FROM

customers c

left

join CustomerCustomerDemo cdon cd.customerid= c.customerID

UNION

SELECT

3

As Tag,

1

As Parent,

c

.CustomerID,NULL,NULL,

o

.OrderID

FROM

Customers c

join

Orders oon o.customerID= c.CustomerID

ORDER

BY CustomerID

for

xmlexplicit