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?

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?

FOR XML / stored procedures

Does anyone know how to generate a resultset using FOR XML
and save that result (XML document) into a column without
leaving SQL Server to render the document?I dont think this is possible as the FOR XML clause sends a stream data out
and it is not possible to save it into a varible at the SQL Server in the
current version atleast ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
www.extremeexperts.com
"T. Wade" <tnolte@.foundrysoftware.com> wrote in message
news:04ac01c366a0$fe916d90$a401280a@.phx.gbl...
> Does anyone know how to generate a resultset using FOR XML
> and save that result (XML document) into a column without
> leaving SQL Server to render the document?|||Hello Wade,
Thanks for posting to MSDN Managed Newsgroup. I will look into this issue
and
let you know as soon as I have update for you.
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
>Content-Class: urn:content-classes:message
>From: "T. Wade" <tnolte@.foundrysoftware.com>
>Sender: "T. Wade" <tnolte@.foundrysoftware.com>
>Subject: FOR XML / stored procedures
>Date: Tue, 19 Aug 2003 15:26:54 -0700
>Lines: 3
>Message-ID: <04ac01c366a0$fe916d90$a401280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Thread-Index: AcNmoP6RD2nsbfwKTKGJ1OA7aiz6og==>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:302172
>NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Does anyone know how to generate a resultset using FOR XML
>and save that result (XML document) into a column without
>leaving SQL Server to render the document?
>|||Hello Wade,
There isn?t a way to do this without going out to a client and back in.
The FOR XML formatting of the recordset is done as the last step when the
TDS output stream is created so the output has to leave the server.
One workaround could be to use link servers by linking a server back to
itself. Ken Henderson's book contains related information
http://btobsearch.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=2VOBU
N18XR&btob=Y&isbn=0201700468&itm=1
[ Disclaimer: This is a third party info and Microsoft does not guarantee
the accuracy of it]
In above case, the data is still being streamed out of the server through
the OLEDB provider so you might wanna watch the performance.
Thanks for posting to MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
>Content-Class: urn:content-classes:message
>From: "T. Wade" <tnolte@.foundrysoftware.com>
>Sender: "T. Wade" <tnolte@.foundrysoftware.com>
>Subject: FOR XML / stored procedures
>Date: Tue, 19 Aug 2003 15:26:54 -0700
>Lines: 3
>Message-ID: <04ac01c366a0$fe916d90$a401280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Thread-Index: AcNmoP6RD2nsbfwKTKGJ1OA7aiz6og==>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:302172
>NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Does anyone know how to generate a resultset using FOR XML
>and save that result (XML document) into a column without
>leaving SQL Server to render the document?
>