SQL 2000 XML Query

Hi folks,

I am perplexed. After lots of searching, reading, mimicking the examples, I still have not successfully achieved the results.

My query is simple: SELECT ProductLineCode, Description FROM tblProduct ORDER BY sortid FOR XML AUTO, ELEMENTS

When the XML comes back, it has multiple root level elements. I am at a loss as to why and how to fix it. I would greatly appreciate any help.

It is SQL 2000 SP 4.

In addition, if anyone has any advice as to the next task I have: In a Windows Form app, there is a button that our network guy will press to synch current data in our SQL Server to an XML file and download it to a specific directory on a laptop computer.

The higher priority though is trying to get the query above to not give me the multiple root level elements.

Thank you in advance!

more ▼

asked Feb 10, 2010 at 12:41 PM in Default

Tony 2 gravatar image

Tony 2
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I think that is expected behaviour - FOR XML does return data with multiple top level elements. When I render out XML result sets in my apps, I always add a 'resultSet' root node which wraps the returned content.

more ▼

answered Feb 10, 2010 at 01:18 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

+1 - Yeah, that's my experience too. PS congrats on 6k now Matt. Looks like I'll never catch you!!
Feb 10, 2010 at 06:14 PM Fatherjack ♦♦
Yeah you will - I'm off contracting for 3 months on Monday - after spending 8 months on my products I need some money coming in! :)
Feb 10, 2010 at 06:29 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 10, 2010 at 12:41 PM

Seen: 737 times

Last Updated: Feb 10, 2010 at 12:41 PM