Scott Hanselman

A better way to get an XmlDocument or XPathDocument from FOR XML AUTO without using SqlXmlCommand?

September 12, 2004 Comment on this post [2] Posted in XML
Sponsored By

When you use SqlCommand.ExecuteXmlReader() you get an XML fragment - you get no root node.  What's a good (clean, elegant, performant, etc.) way to get an XmlDocument or XPathDocument (for XSLT purposes) populated with the data returned - without using the SQLXML 3.0 SqlXmlCommand stuff?  Secondary question - what good is ExecuteXmlReader() anyway?

Is this gross? It feels odd:

        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            SqlCommand command = new SqlCommand("select * from Customers as Customer for XML AUTO, ELEMENTS", conn);
            XPathDocument xp = new XPathDocument();
            XPathEditableNavigator xpathnav = xp.CreateEditor();
            XmlWriter xw = xpathnav.PrependChild();
            xw.WriteStartElement("Customers");
            using(XmlReader xr = command.ExecuteXmlReader())
            {
                xw.WriteNode(xr,true);
            }
            xw.WriteEndElement();
            xw.Close();
            xp.WriteXml(XmlWriter.Create(Response.Output));
        }

It works though, and produces this.  Of course I wouldn't output it like this, I'd style the XPathDocument.

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Hosting By
Hosted in an Azure App Service
September 12, 2004 13:54
Yeah, but it's Whidbey Beta1 only code. AFAIK in Beta2 XPathDocument won't be editable again. Then the solution would be using custom XmlReader, which exposes synthetic root element and then given XML fragment.
September 12, 2004 22:43
Ah....I see...the XmlDocument will still be editable, but that's less useful for the XSLT Transformation. I like your custom XML Reader idea anyway, it'd be a fairly straightforward subclass of XmlReader, and it's a much cleaner OOPy solution.
Thanks Oleg!

Comments are closed.

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.