Force order of XML elements in FOR XML EXPLICIT

Hello,

I need to generate XML that matches an existing XSD. The XSD has the elements in a sequence requiring the XML elements to be in a specific order.

I want to generate XML like the following:

<employee>
<id>1</a>
<name>
<first>Nancy</first>
<last>Davolio</last>
</name>
<title>Sales Representative</title>
</employee>

When I perform my query using FOR XML EXPLICIT, how do I get the name element to be after the id element and before the title element?

Here is an example query (does not work, but illustrates what I would like):

select 1 as tag, null as parent,
EmployeeId as [employee!1!id!element],
null as [name!2!first!element],
null as [name!2!last!element],
Title as [employee!1!title!element]
from employees
where EmployeeId = 1

union all

select 2 as tag, 1 as parent,
EmployeeId as [employee!1!id!element],
FirstName as [name!2!first!element],
LastName as [name!2!last!element],
null as [employee!1!title!element]
from employees
where EmployeeId = 1

order by [employee!1!id!element], tag

for xml explicit

I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries.

Any ideas?

Thanks!

Trev

I have the same problem. Is there a solution?

Travallion said "I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries."

Could someone post an example of how to do it this way please?