This post originated from an RSS feed registered with .NET Buzz by Scott Hanselman. Original Post: A better way to get an XmlDocument or XPathDocument from FOR XML AUTO without using SqlXmlCommand? Feed Title: Scott Hanselman's ComputerZen.com Feed URL: http://radio-weblogs.com/0106747/rss.xml Feed Description: Scott Hanselman's ComputerZen.com is a .NET/WebServices/XML Weblog. I offer details of obscurities (internals of ASP.NET, WebServices, XML, etc) and best practices from real world scenarios. Latest .NET Buzz Posts Latest .NET Buzz Posts by Scott Hanselman Latest Posts From Scott Hanselman's ComputerZen.com

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.