Monday, September 19, 2005
#
In continuation to Part I of this article, we will examine how we can load the XML Fragment returned by the SQL FOR XML query, into a DataSet, to form a Well Formed XML Document.
SqlConnection objCon = new SqlConnection("Connection String");
SqlCommand objCmd = new SqlCommand("select * from customers for xml auto", objCon);
XmlReader xr = null;
XmlDocument xDoc = new XmlDocument();
DataSet ds = new DataSet();
try
{
objCon.Open();
xr = objCmd.ExecuteXmlReader();
ds.ReadXml(xr);
xDoc.LoadXml(ds.GetXml());
xDoc.Save(Server.MapPath("Test.xml"));
}
catch(Exception ex)
{
Response.Write(ex.Message);
}
finally
{
objCon.Close();
xr.Close();
}
As we can see the above code uses a DataSet to load the XML Fragment returned by the FOR XML Select query. The DataSet object is intelligent enough to append a ROOT Node to all XML Data that is being read into/written by it and hence the error "This document already has a DocumentElement node." can be avoided.
We will examine the other approaches in the next articles.
Read Part III, Part IV of the series.
Cheers and Happy Programming !!!
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 MSDNRead Part IV of the series.
Cheers and Happy Programming !!!
This article is in continuation with Part I, Part II & Part III of the series related to solving the error "This document already has a DocumentElement node." which arises when we try to use XmlReader or an XmlDocument to load the XML Data returned by the FOR XML SQL Query.
The FOR XML EXPLICIT is one of the toughest techniques in returning XML Data from SQL Server using FOR XML. But it also has the greatest flexibility in controlling the XML Format that is being returned contrary to the XML Fragment format that gets returned with FOR XML RAW / FOR XML AUTO queries.
The EXPLICIT modes depends on the concept of Universal Table and manual creation of the XML Structure such that it is returned as such and not simply as a record set.
An introductory article on using FOR XML EXPLICIT is available at TopXML - SQLXML - FOR XML EXPLICIT
A step-by-step detailed article on returning a hierarchial parent-tree relationship XML from SQL Server is available at Windows IT Pro - XML Trees - Step by StepI felt that the above articles cover the indepth of the subject matter and hence didnt go into the explanation of the same over here.
This ends the series of articles on working with XML Fragment data returned by FOR XML Queries and utilizing them in your applications.
Cheers and Happy Programming !!!