Load XML Fragments

DataStream

LANGUAGES:XML | SQL | C#

ASP.NETVERSIONS: 1.0 | 1.1

Load XML Fragments

Learn how to re-hydrate an XML Fragment after gettingone from a SQL XML Query or Web Service.

By Brian Noyes

There are many situations where you might find yourselfholding an XmlReader that contains XML fragments representing data rows orpieces of an XML document. One of the most common is if you perform a queryagainst SQL Server with a FOR XML clause. Another might be if you get that rowset from a Web services call. Unless you just rip through the nodes in aforward-only, read-only, node-level manner, you will probably want to get thatdata in a more usable form for you application. You may want to get the datainto an XmlDocument so that you can work with it using the XML Document ObjectModel (DOM), or you may want to get it into a DataSet so that you can easilydata-bind against it. You may want to do both - be able to run XPath queriesagainst 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, Iam going to be working with the Customers table from the Northwind database. Ifyou 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 lookslike Figure 1.

    ALFKI

    Alfreds Futterkiste

    Maria Anders

    Sales Representative

Obere Str.57

    Berlin

    12209

    Germany

    030-0074321

    030-0076545

    ANATR

    AnaTrujillo Emparedados y helados

    AnaTrujillo

    Owner

Avda.de la Constituci n 2222

    M xicoD.F.

    05021

    Mexico

    (5)555-4729

    (5)555-3745

...

Figure 1. FOR XMLqueries against SQL Server return the result set in the form of XML Elementswithout an enclosing document root element.

Although you obviously get back nice clean XML, the nodesreturned are not enclosed within a document element, so cannot be treated as avalid XML document without doing something else. You could easily end up withthe same kind of results from a method call to a Web service as well. Whenmaking the above query against SQL Server, you'll want to use theExecuteXmlReader method of the SqlCommand object, which returns an XmlReaderwith the current position set to the first element representing the first rowof the result set.

Wrap it in an XmlDocument or XPathDocument

If you want to get that data into an XmlDocument, yourfirst 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 thattells you the document already has a DocumentElement node. The reason is thatas the reader tries to push the results into the XmlDocument, it will first addthe first row as an element. But then the next row comes along and it lookslike you are adding a second root element into the document, which is not validXML, and wham, along comes an exception.

To get it to work the way you expect, you will want tofirst add a root node to the XmlDocument to enclose the result set rowelements, and then add the rows in from the reader. You could do this in a rawform, iterating through the reader and adding the elements based on theirstring form or contents, but there is a much easier and simpler way. Figure 2 showsthe code to quickly add the rows on a node by node basis, letting the XmlReaderand the XmlDocument.ReadNode method do the dirty work.

private void btnGetXmlDoc_Click(object sender, System.EventArgse)

{

   // Set up the query

   SqlConnection conn =new SqlConnection(

    "server=localhost;database=Northwind;trusted_connection=true");

   SqlCommand cmd = newSqlCommand(

      "SELECT * FROMCustomers FOR XML AUTO, ELEMENTS",conn);

   try

   {

      // Execute the query

      conn.Open();

      XmlReader reader =cmd.ExecuteXmlReader();

      // Create thedocument object to contain the data

      XmlDocument doc =new XmlDocument();

      XmlElement root =doc.CreateElement("Data");

      doc.AppendChild(root);

      // Start reading thedata into the document,

      // node by node

      XmlNode node =doc.ReadNode(reader);

      while (node != null)

      {

         root.AppendChild(node);

         node =doc.ReadNode(reader);

      }

      // Bind to an XMLWeb control on the form

      Xml1.Document = doc;

   }

   finally

   {

      conn.Close();

   }

}

Figure 2. This method takes the XmlReaderreturned from a FOR XML SQL query and uses it to populate an XmlDocument thatis then bound to an XML Web control on the sample application page.

Once you have it in an XmlDocument, you could navigate thenode tree, make modifications, or transform the results using XSLT. If you justneed to navigate the results using XPath, or in a read-only fashion, you'll bebetter off using an XPathDocument for performance and flexibility reasons. Ifyou are unfamiliar with the XPathDocument, it is a light-weight container forXML that you use with the XPathNavigator object. The XPathNavigator is thepreferred approach for querying and navigating an XML node set in .NET when youdo not need to make modifications to the tree. It will give you faster queriesand navigation through the document, is quicker to construct, and takes up lessmemory 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 XMLfragment passed into the constructor in a root node to make it easier to workwith. 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 theCreateNavigator method on the document to get an XPathNavigator, which is thereal working object for getting at the underlying data and object model.

private void btnGetXpathDoc_Click(object sender, System.EventArgse)

{

   // Clear the drop downlist 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 * FROMCustomers FOR XML AUTO, ELEMENTS",conn);

   try

   {

      // Perform the query

      conn.Open();

      XmlReader reader =cmd.ExecuteXmlReader();

      // Load the resultsinto the XPathDoc

      XPathDocument doc =new XPathDocument(reader);

      // Get a navigatorand perform an XPath query

      // for the nodes ofinterest

      XPathNavigator nav =doc.CreateNavigator();

      XPathNodeIterator it= nav.Select(

         "//Customers/CompanyName[../City='" +

         txtCity.Text +"']");

      // Iterate throughthe results, adding to a combobox

      while(it.MoveNext())

      {

         DropDownList1.Items.Add(it.Current.Value);

      }

   }

   finally

   {

      conn.Close();

   }

}

Figure 3. LoadingXML row elements into an XPathDocument is a simple matter of passing theXmlReader to the constructor. You can then obtain an XPathNavigator and executeXPath queries to locate nodes of interest or use the MoveXXX methods of thenavigator to iterate through the node tree.

Gimme Back My Relational Data!

If you want to work with that XML on the client side asrelational data, perhaps to bind to a grid, you will probably want to get itback into the form of a DataSet. Of course, if that is all you are doing, youshould not be using a FOR XML query, but that is just one example of a scenariowhere 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 thatinherits from the XmlDocument class and thus inherits all the behavior of theXmlDocument to manage the underlying data as XML, but it also exposes a DataSetproperty that lets you treat and act upon the underlying data as a set ofrelational tables.

You need to do two things to load the XML result set intoan XmlDataDocument. First, you need to load the XmlDataDocument with enoughschema information so it knows where to put the row-level result set data whenit gets it. Then you need to pass the result set into the XmlDataDocument forstorage.

To load schema-only information into a DataSet, you gothrough the same steps you would to load actual data - with one exception. Youneed a connection, command, and DataAdapter object that represent the SELECTquery that you would use to populate the DataSet. You then call the FillSchemamethod on the DataAdapter to just get the schema info. This initializes theDataSet 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 itsDataSet 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 * FROMCustomers",conn);

   SqlCommand cmdXml = newSqlCommand(

     "SELECT * FROMCustomers FOR XML AUTO, ELEMENTS",conn);

   SqlDataAdapter da = newSqlDataAdapter(cmdSchema);

   try

   {

      // Execute thequeries

      conn.Open();

      XmlDataDocumentdataDoc = new XmlDataDocument();

      da.FillSchema(dataDoc.DataSet,

         SchemaType.Source,"Customers");

      XmlReader reader =cmdXml.ExecuteXmlReader();

      // Push the datainto 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 formof an XmlReader into a DataSet, use the XmlDataDocument object. You firstpopulate the XmlDataDocument with schema information.

Once the schema is initialized, you can pump the data intothe XmlDataDocument using the ReadXml method of the DataSet property on theXmlDataDocument, passing an argument of XmlReadMode.Fragment for the secondparameter (see Figure 4). You then have an object that you could either do XMLstuff against (i.e. XPath queries, node tree navigation), or you can do DataSetstuff against (i.e. data-bind, create DataViews, etc.).

The download code for this article includes a simple Webapp (in both C# and VB .NET versions) that exercises the code shown in Figures2-4. It takes the results of putting the XML fragment node set into the threeobject types discussed (XmlDocument, XPathDocument, and XmlDataDocument) anduses them to render some display controls. Using these techniques, you canquickly turn that XmlReader containing an XML fragment into something you canuse for a variety of tasks.

The sample code in this article is available for download.

Brian Noyes is a consultant, trainer, speaker, andwriter with IDesign, Inc. (http://www.idesign.net),a .NET-focused architecture and design consulting firm. Brian specializes indesigning and building data-driven distributed applications. He has over 12years experience in programming, engineering, and project management, and is acontributing editor for asp.netPRO andother publications. Contact him at mailto:[email protected].