You may receive the error "This document already has a DocumentElement node." while trying to read XML Data retrieved using the FOR XML clause

You may receive the error "This document already has a DocumentElement node." while trying to read XML Data retrieved using the FOR XML clause.

SQL Server 2000's FOR XML Clause provides great support in querying SQL Server Data and retrieving it in the form of XML. In other words, you can get an XML representation of the Result Set using the FOR XML Clause.

So, if a select statement of select * from customers retrieves results in a sequential way, select * from customers FOR XML AUTO will retrieve the same in a XML Format.

There are different methods to retrieve XML representation of Data using SQL Server 2000's FOR XML clause which is beyond the scope of this article.

However, FOR XML doesn't actually return an XML document; instead, it returns an XML fragmant, with no XML declaration or root element. This is a problem if you want to feed this XML to classes that expect actual XML.

So, when we try to use XmlReader or an XmlDocument to load the XML Data returned by the FOR XML, it won't contain the Root element and hence would return the error, "This document already has a DocumentElement node."

To resolve this issue, we can adopt either of the following ways:-

1. Using a DataSet to read the results and then use XmlDataDocument or the WriteXml() method of DataSet to save the XML File

2. Using the SQL XML Managed Classes by downloading the Microsoft SQL XML Managed Provider (Available for free download from Microsoft)

3. Using the FOR XML EXPLICIT mode of SQL Server, where we can have total control over the format in which the XML is returned.

We will examine each of these methods in the forthcoming articles.

The articles are now up !!!

Read, Part II, Part III, Part IV of the series.

Cheers and Happy Programming !!!

Share This Post: