DataStream
LANGUAGES: XML | SQL | C#
ASP.NET VERSIONS: 1.0 | 1.1
Load XML Fragments
Learn how to re-hydrate an XML Fragment after getting one from a SQL XML Query or Web Service.
By Brian Noyes
There are many situations where you might find yourself holding an XmlReader that contains XML fragments representing data rows or pieces of an XML document. One of the most common is if you perform a query against SQL Server with a FOR XML clause. Another might be if you get that row set from a Web services call. Unless you just rip through the nodes in a forward-only, read-only, node-level manner, you will probably want to get that data in a more usable form for you application. You may want to get the data into an XmlDocument so that you can work with it using the XML Document Object Model (DOM), or you may want to get it into a DataSet so that you can easily data-bind against it. You may want to do both - be able to run XPath queries against the data, and data-bind to a grid in the UI.
Get the Data
To have some XML data in the form of row set fragments, I am going to be working with the Customers table from the Northwind database. If you run the following query against SQL Server:
SELECT * FROM Customers FOR XML AUTO, ELEMENTS
you'll end up with an XML node set returned that looks like Figure 1.
<Customers>
<CustomerID>ALFKI</CustomerID>
<CompanyName>Alfreds Futterkiste</CompanyName>
<ContactName>Maria Anders</ContactName>
<ContactTitle>Sales Representative</ContactTitle>
<Address>Obere Str. 57</Address>
<City>Berlin</City>
<PostalCode>12209</PostalCode>
<Country>Germany</Country>
<Phone>030-0074321</Phone>
<Fax>030-0076545</Fax>
</Customers>
<Customers>
<CustomerID>ANATR</CustomerID>
<CompanyName>Ana Trujillo Emparedados y helados</CompanyName>
<ContactName>Ana Trujillo</ContactName>
<ContactTitle>Owner</ContactTitle>
<Address>Avda. de la Constituci n 2222</Address>
<City>M xico D.F.</City>
<PostalCode>05021</PostalCode>
<Country>Mexico</Country>
<Phone>(5) 555-4729</Phone>
<Fax>(5) 555-3745</Fax>
</Customers>
...
Figure 1. FOR XML queries against SQL Server return the result set in the form of XML Elements without an enclosing document root element.
Although you obviously get back nice clean XML, the nodes returned are not enclosed within a document element, so cannot be treated as a valid XML document without doing something else. You could easily end up with the same kind of results from a method call to a Web service as well. When making the above query against SQL Server, you'll want to use the ExecuteXmlReader method of the SqlCommand object, which returns an XmlReader with the current position set to the first element representing the first row of the result set.
Wrap it in an XmlDocument or XPathDocument
If you want to get that data into an XmlDocument, your first instinct may be to try and call Load on the XmlDocument:
XmlReader reader = cmd.ExecuteXmlReader();
XmlDocument doc = new XmlDocument();
doc.Load(reader);
However, if you try this you'll get an exception that tells you the document already has a DocumentElement node. The reason is that as the reader tries to push the results into the XmlDocument, it will first add the first row as an element. But then the next row comes along and it looks like you are adding a second root element into the document, which is not valid XML, and wham, along comes an exception.
To get it to work the way you expect, you will want to first add a root node to the XmlDocument to enclose the result set row elements, and then add the rows in from the reader. You could do this in a raw form, iterating through the reader and adding the elements based on their string form or contents, but there is a much easier and simpler way. Figure 2 shows the code to quickly add the rows on a node by node basis, letting the XmlReader and the XmlDocument.ReadNode method do the dirty work.
private void btnGetXmlDoc_Click(object sender, System.EventArgs e)
{
// Set up the query
SqlConnection conn = new SqlConnection(
"server=localhost;database=Northwind;trusted_connection=true");
SqlCommand cmd = new SqlCommand(
"SELECT * FROM Customers FOR XML AUTO, ELEMENTS",conn);
try
{
// Execute the query
conn.Open();
XmlReader reader = cmd.ExecuteXmlReader();
// Create the document object to contain the data
XmlDocument doc = new XmlDocument();
XmlElement root = doc.CreateElement("Data");
doc.AppendChild(root);
// Start reading the data into the document,
// node by node
XmlNode node = doc.ReadNode(reader);
while (node != null)
{
root.AppendChild(node);
node = doc.ReadNode(reader);
}
// Bind to an XML Web control on the form
Xml1.Document = doc;
}
finally
{
conn.Close();
}
}
Figure 2. This method takes the XmlReader returned from a FOR XML SQL query and uses it to populate an XmlDocument that is then bound to an XML Web control on the sample application page.
Once you have it in an XmlDocument, you could navigate the node tree, make modifications, or transform the results using XSLT. If you just need to navigate the results using XPath, or in a read-only fashion, you'll be better off using an XPathDocument for performance and flexibility reasons. If you are unfamiliar with the XPathDocument, it is a light-weight container for XML that you use with the XPathNavigator object. The XPathNavigator is the preferred approach for querying and navigating an XML node set in .NET when you do not need to make modifications to the tree. It will give you faster queries and navigation through the document, is quicker to construct, and takes up less memory per node than the XmlDocument.
To load the results into an XPathDocument is even easier, because the constructor for XPathDocument is smart enough to wrap an XML fragment passed into the constructor in a root node to make it easier to work with. The code to load the data into an XPathDocument is shown in Figure 3. Once you have the data loaded into an XPathDocument, you will call the CreateNavigator method on the document to get an XPathNavigator, which is the real working object for getting at the underlying data and object model.
private void btnGetXpathDoc_Click(object sender, System.EventArgs e)
{
// Clear the drop down list we will populate
DropDownList1.Items.Clear();
// Set up the query
SqlConnection conn = new SqlConnection(
"server=localhost;database=Northwind;trusted_connection=true");
SqlCommand cmd = new SqlCommand(
"SELECT * FROM Customers FOR XML AUTO, ELEMENTS",conn);
try
{
// Perform the query
conn.Open();
XmlReader reader = cmd.ExecuteXmlReader();
// Load the results into the XPathDoc
XPathDocument doc = new XPathDocument(reader);
// Get a navigator and perform an XPath query
// for the nodes of interest
XPathNavigator nav = doc.CreateNavigator();
XPathNodeIterator it = nav.Select(
"//Customers/CompanyName[../City='" +
txtCity.Text + "']");
// Iterate through the results, adding to a combobox
while (it.MoveNext())
{
DropDownList1.Items.Add(it.Current.Value);
}
}
finally
{
conn.Close();
}
}
Figure 3. Loading XML row elements into an XPathDocument is a simple matter of passing the XmlReader to the constructor. You can then obtain an XPathNavigator and execute XPath queries to locate nodes of interest or use the MoveXXX methods of the navigator to iterate through the node tree.
Gimme Back My Relational Data!
If you want to work with that XML on the client side as relational data, perhaps to bind to a grid, you will probably want to get it back into the form of a DataSet. Of course, if that is all you are doing, you should not be using a FOR XML query, but that is just one example of a scenario where you might have an XML fragment with which you want to work.
To use that data in both an XML form and a DataSet form, you'll want to use the XmlDataDocument object. This is a hybrid object that inherits from the XmlDocument class and thus inherits all the behavior of the XmlDocument to manage the underlying data as XML, but it also exposes a DataSet property that lets you treat and act upon the underlying data as a set of relational tables.
You need to do two things to load the XML result set into an XmlDataDocument. First, you need to load the XmlDataDocument with enough schema information so it knows where to put the row-level result set data when it gets it. Then you need to pass the result set into the XmlDataDocument for storage.
To load schema-only information into a DataSet, you go through the same steps you would to load actual data - with one exception. You need a connection, command, and DataAdapter object that represent the SELECT query that you would use to populate the DataSet. You then call the FillSchema method on the DataAdapter to just get the schema info. This initializes the DataSet so that it is ready to receive data that fits into the schema provided. You can perform all these same steps against an XmlDataDocument, using its DataSet property for the argument to the FillSchema method (see Figure 4).
private void btnGetXmlDataDoc_Click(object sender, System.EventArgs e)
{
// Set up the queries
SqlConnection conn = new SqlConnection(
"server=localhost;database=Northwind;trusted_connection=true");
SqlCommand cmdSchema = new SqlCommand(
"SELECT * FROM Customers",conn);
SqlCommand cmdXml = new SqlCommand(
"SELECT * FROM Customers FOR XML AUTO, ELEMENTS",conn);
SqlDataAdapter da = new SqlDataAdapter(cmdSchema);
try
{
// Execute the queries
conn.Open();
XmlDataDocument dataDoc = new XmlDataDocument();
da.FillSchema(dataDoc.DataSet,
SchemaType.Source,"Customers");
XmlReader reader = cmdXml.ExecuteXmlReader();
// Push the data into the XmlDataDocument
dataDoc.DataSet.ReadXml(reader,XmlReadMode.Fragment);
// DataBind
DataGrid1.DataSource = dataDoc.DataSet.Tables[0];
DataGrid1.DataBind();
}
finally
{
conn.Close();
}
}
Figure 4. To load an XML fragment in the form of an XmlReader into a DataSet, use the XmlDataDocument object. You first populate the XmlDataDocument with schema information.
Once the schema is initialized, you can pump the data into the XmlDataDocument using the ReadXml method of the DataSet property on the XmlDataDocument, passing an argument of XmlReadMode.Fragment for the second parameter (see Figure 4). You then have an object that you could either do XML stuff against (i.e. XPath queries, node tree navigation), or you can do DataSet stuff against (i.e. data-bind, create DataViews, etc.).
The download code for this article includes a simple Web app (in both C# and VB .NET versions) that exercises the code shown in Figures 2-4. It takes the results of putting the XML fragment node set into the three object types discussed (XmlDocument, XPathDocument, and XmlDataDocument) and uses them to render some display controls. Using these techniques, you can quickly turn that XmlReader containing an XML fragment into something you can use for a variety of tasks.
The sample code in this article is available for download.
Brian Noyes is a consultant, trainer, speaker, and writer with IDesign, Inc. (http://www.idesign.net), a .NET-focused architecture and design consulting firm. Brian specializes in designing and building data-driven distributed applications. He has over 12 years experience in programming, engineering, and project management, and is a contributing editor for asp.netPRO and other publications. Contact him at mailto:[email protected].
