FOR XML AUTO, ELEMENTS

[Brett Robichaud]

I am trying to get data as XML from my SQL 2k server.

My select statement is:
SELECT TOP 10 * FROM IMAGE FOR XML AUTO, ELEMENTS

I then use this code to wrtie it to a file:

XmlWriter w = new XmlTextWriter(@"foo.xml", Encoding.UTF8);
w.WriteStartDocument();
w.WriteStartElement("Test");
rdr.MoveToContent();
while (rdr.Read())
w.WriteNode(rdr, false);
w.WriteEndElement();
w.WriteEndDocument();
w.Close();

I am consistently getting this exception. It seems like the xml is garbled.
Any ideas?

System.InvalidOperationException: Token StartElement in state Epilog would
result in an invalid XML document.
at System.Xml.XmlTextWriter.AutoComplete(Token token)
at System.Xml.XmlTextWriter.WriteStartElement(String prefix, String
localName, String ns)
at System.Xml.XmlWriter.WriteNode(XmlReader reader, Boolean defattr)
at MyImageBridge.Data.DBLayerApp.XmlGetSpiderData(String sCustID,
DateTime dtFrom, DateTime dtTo) in
c:\dev\ice\apps\web\corporate\myimagebridge\codebehind\dblayerapp.cs:line
371

 

[William Ryan eMVP]

What are the values of rdr after the read? I'd put a debug writeline there
and see if anything's coming out and what. Try calling MoveToContent, then
just rdr.Read using (basically take out the while loop. See if that parses,
that looks like it's the likely problem but its hard to tell from here.

 

[Kevin Yu [MSFT]]

Thanks for Bill's quick response!

Hi Brett,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you are receiving an
InvalidOperationException when trying to write Xml data from the SQL server
into a file. If there is any misunderstanding, please feel free to let me
know.

I have tried it on my machine and can reproduce it. It seem that the
XmlWriter in writing the root element twice in this case. Here I have found
a workaround for you. Please try the following code instead.

XmlWriter w = new XmlTextWriter(@"foo.xml", Encoding.UTF8);
w.WriteStartDocument();
w.WriteStartElement("Test");
rdr.Read();
while(rdr.ReadState!=System.Xml.ReadState.EndOfFile)
{
string s=rdr.ReadOuterXml();
w.WriteRaw(s);
}
w.WriteEndElement();
w.WriteEndDocument();
w.Close();

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Brett Robichaud]

Perfect. You're a star Kevin. I probably would have struggled for hours on
this.

Thanks a million.

-Brett-

 

[Kevin Yu [MSFT]]

Hi Brett,

You're welcome. Thanks for sharing your experience with all the people
here. If you have any questions, please feel free to post them in the
community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."