Scott Hanselman

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

September 12, '04 Comments [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
Sponsored By
Hosting By
Dedicated Windows Server Hosting by SherWeb
Sunday, September 12, 2004 9:54:41 AM UTC
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.
Sunday, September 12, 2004 6:43:32 PM UTC
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!
Scott Hanselman
Comments are closed.

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