You may receive the error "This document already has a DocumentElement node." - Part III

This article is in continuation with Part I and Part II.

In
Part II, we examined how we can utilize the DataSet's intelligence to handle XML Format to overcome the "Document Element" issue while reading XML Data from SQL Server using FOR XML queries.

We will examine a much more efficient way of handling SQL XML using the SQL XML Managed classes provided with the Microsoft SQL XML Managed Provider (Available for free download from Microsoft).

Microsoft SQL XML introduces powerful set of Properties/Methods to manipulate XML Data from SQL Server FOR XML Result Sets. It leverages the powerful XML Support provided with SQL Server 2000 and provides easy accessible methods for developers from .NET coding perspective to accomplish tasks easier.

We will examine the following sample code which does the same as what we did with DataSet.


using Microsoft.Data.SqlXml;

SqlXmlCommand comm = new SqlXmlCommand("Connection String");
XmlDocument xDoc = new XmlDocument();
XmlReader xr = null;
comm.CommandType = SqlXmlCommandType.Sql;
comm.CommandText = "select * from customers for xml auto";
comm.OutputEncoding = "UTF-8";
comm.RootTag = "Customers";
try
{
xr = comm.ExecuteXmlReader();
xDoc.Load(xr);
xDoc.Save(Server.MapPath("Test.xml"));
}
catch(Exception ex)
{
Response.Write(ex.Message);
}
finally
{
xr.Close();
}


While the above code accomplishes the same result as we achieved from the DataSet (as explained in Part I), note that we have a great control in the format of the XML Document.

We will examine the changes to the code line by line:-

SqlXmlCommand object is used to create an SQLXMLCommand. It requires that the Connection String provided has the DataSource specified even while working with SQLClient unlike the traditional connection string where you dont need to mention the provider if accessing SQL Server Database.

The CommandType property specifies whether it is a SQL Query or an XPATH Expression, XML Template etc.,

OutputEncoding specifies the encoding format.

The most important property is the RootTag where we specify the Root Node as "Customers". This gives us the flexibility to specify the ROOT Node for the XML Fragment returned by the FOR XML Query. This gives us the full control over the XML Document format and hence provides better way for manipulating/saving XML Data returned by FOR XML SQL Queries.

The Microsoft SQL XML Managed Provider DLL can be downloaded from
Microsoft Download Center for free.

We have to import the Microsoft.Data.SqlXml Reference after downloading the same from Microsoft Download Center.

More information on SQL XML can be found at
MSDN

Read Part IV of the series.

Cheers and Happy Programming !!!

Print | posted on Monday, September 19, 2005 9:02 AM

Comments on this post

# re: You may receive the error "This document already has a DocumentElement node." - Part III

Requesting Gravatar...
cok guzel olmus eline saglik
Left by mario oyunları on Sep 22, 2009 12:09 AM

# re: You may receive the error "This document already has a DocumentElement node." - Part III

Requesting Gravatar...
veryy goodd
Left by meksika biberi zayiflama hapi on Jun 01, 2010 12:29 AM

# re: You may receive the error "This document already has a DocumentElement node." - Part III

Requesting Gravatar...
woooowww.. good news
Left by high Platelet Count on Mar 04, 2011 8:27 PM

Your comment:

 (will show your gravatar)