Re: FOR XML AUTO, Elements and blank cell

Tech-Archive recommends: Fix windows errors by optimizing your registry

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@xxxxxxxxxxxxxxxx> wrote in message
news:%23QJCINERGHA.2088@xxxxxxxxxxxxxxxxxxxxxxx
"JR" <jriker1@xxxxxxxxx> wrote in message
news:1141948738.843007.54400@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

https://mvp.support.microsoft.com/profile=8AA9D5F5-E1C2-44C7-BCE8-8741D22D17A5



.

Relevant Pages

  • Re: For XML Problem with IIS6 and W2k3
    ... Can you run the FOR XML query directly on the database? ... > dim adoConn ... > dialect for the command stream to be a SQL query. ...
    (microsoft.public.sqlserver.xml)
  • Re: 8192 character limit in Query Analyser when returning data, how to save result without newline e
    ... You can write a small VB Script using ADO that will use the ADO result ... stream to write the FOR XML result directly to a file. ... The Query Analyzer used ODBC, that had no streaming result support ...
    (microsoft.public.sqlserver.xml)
  • Re: Getting to the root node of an xml document
    ... I have some old code around here that reads and then prints out an XML file. ... It's handy because it lets you see the whole structure of the XML file; I think the root node of the tree structure isn't the root node of the document itself. ... public EditorJFrame(String filename) ... private void preOrderXml(org.w3c.dom.Node docNode, DefaultMutableTreeNode treeNode, int level) ...
    (comp.lang.java.help)
  • Re: BizTalk 2004 :: merging a multiple root node message as output
    ... If you really have a legacy system that is not using well-formed xml (or ... > I have a typical xml message as input (single root node) and I need to map ... > to a message which contains multiple root nodes. ...
    (microsoft.public.biztalk.general)
  • Re: Extract Strings
    ... Keep in mind it's not well-formed XML (needs a root node), ... XPath query on it, you can iterate the nodes like this: ... > Ok, well this isn't an ordinary string, its the string representation of ...
    (microsoft.public.dotnet.languages.csharp)