FOR XML AUTO Question

[dBjason]

Here's my query:
select letterid, lettername from ltrNames for xml auto, elements

Here's what I'm getting:
<ltrNames>
<letterid>1</letterid>
<lettername>First Letter</lettername>
</ltrNames>
<ltrNames>
<letterid>2</letterid>
<lettername>Second Letter</lettername>
</ltrNames>
<ltrNames>
<letterid>3</letterid>
<lettername>Third Letter</lettername>
</ltrNames>


Now here's what I'd like to get:
<ltrNames>
<letterid>1</letterid>
<lettername>First Letter</lettername>
<letterid>2</letterid>
<lettername>Second Letter</lettername>
<letterid>3</letterid>
<lettername>Third Letter</lettername>
</ltrNames>


How can I do this?

Thanks (again!),
Jason

 

[mrdenny]

What you are attempting to get isn't proper XML. Why the funky output requirements?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[dBjason]

I'm trying to pass the XML from SQL to a .NET Windows app through a WCF service application, but it will only accept a single root node in an XML object.

To be honest, I am probably barking up the wrong tree here.

What I need to do is pass multiple variable names and values (essentially two strings an integer). If it were VB6, I'd pass a dynamic array with 2 or 3 columns and be done with it. But I can't get a dynamic array through the WCF. Passing a table would be nice but the boss says "way too bulky". XML has been a 'futile experiment'.

Now at this point, I'm probably posting in the wrong form. Any ideas?

Thanks,
Jason

 

[gmmastros]

but it will only accept a single root node in an XML object.

You can easily add a root node to your xml, like this:

select letterid, lettername 
from   ltrNames 
for    xml auto, elements[!], root('MyRootNode')[/!]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrdenny]

Use Goerge's query to generate the XML document that you need.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[dBjason]

It's almost shameful to admit I've spent 3 days trying to work around such an easy fix

Thanks guys!