FOR XML AUTO, Elements and blank cell

When doing a basic select one of the SQL Server 2000 sample databasaes
with "FOR XML AUTO, Elements" the output is not well formed. Nothing
can parse it. Sometimes if there is no data in the cell it will have
an opening tag like <city> but no closing tag. Other times it will
completly eliminate data if there is nothing in it so the different
nodes in the xml all have different sets of data. How do you get SQL
Server to output the query well formed and have all the cell data in
each row in each node regardless of if it's blank or not?
Thanks.
JR"JR" <jriker1@.yahoo.com> wrote in message
news:1141948738.843007.54400@.e56g2000cwe.googlegroups.com...
> When doing a basic select one of the SQL Server 2000 sample databasaes
> with "FOR XML AUTO, Elements" the output is not well formed. Nothing
> can parse it. Sometimes if there is no data in the cell it will have
> an opening tag like <city> but no closing tag. Other times it will
> completly eliminate data if there is nothing in it so the different
> nodes in the xml all have different sets of data. How do you get SQL
> Server to output the query well formed and have all the cell data in
> each row in each node regardless of if it's blank or not?
> Thanks.
> JR
>
Can you post the query?
Joe Fawcett - XML MVP
[url]https://mvp.support.microsoft.com/profile=8AA9D5F5-E1C2-44C7-BCE8-8741D22D17A5[/ur
l]|||First FOR XML results are never guaranteed to be fully well-formed (they can
have multiple top-level nodes). You can add a root node by setting the Root
name property on your command stream object to get the result wrapped into a
document.
Secondly, you need to use the correct API to get the XML back as a stream:
The ICommandStream object in ADO or the correct API in ADO.Net to get the
XML back as a stream and not just the first 2k block...
The documentation has samples that should help you further...
Best regards
Michael
"Joe Fawcett" <joefawcett@.newsgroup.nospam> wrote in message
news:%23QJCINERGHA.2088@.TK2MSFTNGP14.phx.gbl...
> "JR" <jriker1@.yahoo.com> wrote in message
> news:1141948738.843007.54400@.e56g2000cwe.googlegroups.com...
> Can you post the query?
> --
> Joe Fawcett - XML MVP
> [url]https://mvp.support.microsoft.com/profile=8AA9D5F5-E1C2-44C7-BCE8-8741D22D17A5[/
url]
>