XML Data

So far we’ve looked at how ADO.NET can be used to access data and how XML plays a big part. Whenever you serialize a DataSet object, you get XML, which the client uses to rebuild the DataSet object. If we repackage data or pass simple types, we still get XML.

Of course, sometimes the source data itself is XML. You might be storing data in an XML document rather than a database, or you might access a data source that is exposed as XML. In addition, you might want to expose legacy data as XML yourself rather than use a custom ADO.NET provider. Fortunately, the .NET Framework provides powerful tools for manipulating XML because XML is crucial to the functioning of .NET as a whole.

We’ll look at four topics related to XML data in relation to Web services:

• Querying XML data sources (getting data out of XML documents)

• Addressing a DataSet object as XML (using XML techniques to access data in an ADO.NET DataSet object)

• SQLXML (getting data out of a database in XML form)

• Exposing other data as XML (using an XML representation of legacy/ custom data as a data source)

Querying XML Data Sources

XML has been with us for a few years now, and in that time a wealth of new standards and technologies have emerged. Every time someone thinks of a question like “How can we individually address elements in an XML document?” or “How can we link XML documents together?,” someone comes up with an answer. The only problem is that the goalposts keep moving as standards evolve. The World Wide Web Consortium (http://www.w3.org/) maintains most XML standards, which go through a process of evaluation to move from draft form to recommendation form. As I write this sentence, in fact, I’ve learned that the XPointer framework (an extension of XPath that allows a finer- grained level of text selection) has become a recommendation. Not many implementations of XPointer are available right now—but by the time you read this, they’re sure to be more widespread.

For the moment, though, the main XML access technology that you can use in .NET is XPath. We won’t go into the details here because this isn’t the place for such a discussion, but we’ll give a quick example. The following code is taken from the XMLQuerying project in the code for this book, and it starts by loading an XML document that contains the same data as DansRecords.mdb: DansRecords.xml.

XmlDocument doc = new XmlDocument(); doc.Load(@"c:\WSCR\DansRecords.xml");

To access this data via XPath, we can create an XPathNavigator object from the document root:

XmlNode root = doc.DocumentElement; XPathNavigator navigator = root.CreateNavigator();

To get the results of an XPath expression, we create an XPathExpression. (We could just use a string, but this class gives us additional options, such as the alphabetic sorting used here.)

XPathExpression expression = navigator.Compile(@"//Artist"); expression.AddSort(".", XmlSortOrder.Ascending, XmlCaseOrder.None, "",                    XmlDataType.Text);

We get results back in the form of an XPathNodeIterator:

XPathNodeIterator iterator = navigator.Select(expression);

Next we use the XPathNodeIterator.MoveNext method to work our way through the data (in this case, all the <Artist> elements), and we process it at our leisure:

string artist = ""; string lastArtist = ""; int multiple = 1; while (iterator.MoveNext()) {     artist = iterator.Current.Value;     if (artist == lastArtist)     {         multiple++;     }     else     {         if (multiple > 1)         {             Console.WriteLine(" (x{0})", multiple);             multiple = 1;         }         else         {             Console.WriteLine();         }         Console.Write(iterator.Current.Value);     }     lastArtist = artist; }

The result of this code lists all the artists in the database, counting how many entries they have. (The code relies on the fact that the data is sorted.)

Obviously, we can do a whole lot with XPath, and we strongly advise you to read up on its powerful syntax.

The other current possibility in .NET XML processing is XQuery, which is for executing queries against XML documents, but this technology is still in the early stages of development. You’ll find the W3C working draft for XQuery at http://www.w3.org/TR/xquery/.

Addressing a DataSet as XML

.NET was created with XML data in mind, and that thinking is apparent throughout ADO.NET. As you’ve seen, serializing a DataSet object as XML happens automatically (although you can, of course, control this or even do it all yourself if you prefer). Not only that, but you can easily get an XML representation of a DataSet object in code (including schema information and so forth), using an XmlDataDocument class.

The following code, from the XMLDataSetQuerying project available with this chapter’s sample code, shows just how easy this is:

OleDbConnection conn = new OleDbConnection(     @"Provider=Microsoft.Jet.OLEDB.4.0;"     + @"User ID=Admin;Data Source=C:\WSCR\dansrecords.mdb;"); OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT RecordID, Artist, "     + "Title, Genre, Compilation, Num_discs, Single_or_Album, Format, "     + "Signed, Promo, Case_Type, InStorage FROM Records", conn); DataSet ds = new DataSet(); conn.Open(); adapter.Fill(ds, "Records"); conn.Close(); XmlDataDocument doc = new XmlDataDocument(ds); 

After we have our data, we just pass the DataSet object to the XmlDataDocument constructor. Then we can do whatever we like with this XML, treating it much like any other XML document: navigate it, add, remove, or update nodes, transform it using XSLT, or serialize it to XML. Any change applied to the XML document is reflected live in the data set, and vice versa. The example code simply executes the same XPath expression as in the XMLQuerying example, so we won’t repeat it here.

SQLXML

If you are using a SQL Server 2000 data source, you have another option at your disposal. By installing SQLXML, you can extend the XML capabilities of the DBMS in several ways, including adding FOR XML to the SQL SELECT statement syntax. This enables you to run queries and get data back directly as XML.

Let’s look at a quick example of this, from the SQLXMLExample project. We start by initializing a new XmlDocument object for storing our results, and then we set up our connection and FOR XML query:

XmlDocument doc = new XmlDocument(); SqlConnection conn = new SqlConnection(     "Server=ALTAR1;User=sa;DataBase=pubs"); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * FROM Titles FOR XML AUTO";

The AUTO keyword lets SQL Server decide how to format the results. Next we use the SqlCommand.ExecuteXmlReader method to obtain an XmlReader object on our results (not a SqlDataReader object):

conn.Open(); XmlReader reader = cmd.ExecuteXmlReader();

Then we access the results and close the connection. Bear in mind that we can’t just load XML straight into an XmlDocument object using this XmlReader object because the results of this query don’t constitute well-formed XML— there is no single document root element. Instead, we have a collection of results in individual elements. One way of dealing with this, which is used in the example, is to construct our own XML using a StringBuilder, wrapping the results in a <QueryResult> element:

StringBuilder sb = new StringBuilder(); sb.Append("<QueryResult>"); if (reader.Read()) {     while (!reader.EOF)     {         sb.Append(reader.ReadOuterXml());         reader.Skip();     } } conn.Close(); sb.Append("</QueryResult>");

The XML that this generates can be loaded into an XmlDocument object, and in our example we simply write the document to the screen:

doc.LoadXml(sb.ToString()); doc.Save(Console.Out);

As with the other technologies we’ve looked at, a whole lot more is possible by using FOR XML queries and SQLXML as a whole. If this solution fits your needs, it is well worth a look. You can even configure Microsoft Internet Information Services (IIS) to execute commands using URL format, effectively accessing SQL Server over the Web as a Web service, but without using .NET Web services. However, this veers away from the topic of this book somewhat, so we won’t cover it here.

Exposing Other Data as XML

As mentioned throughout this chapter, you need not limit yourself to databases and XML documents when it comes to data access. As we did earlier with a custom provider, you can expose sources such as file system data, and you can make full use of XML querying capabilities as well as ADO.NET. In fact, it can often be worthwhile to skip ADO.NET and take a pure XML route, although this comes with its own challenges.

One approach is to derive a class from XPathNavigator, implementing the various members as appropriate to your data source. An excellent example of this can be found on Aaron Skonnard’s Web site, at http://staff.develop.com/aarons/xmllinks.htm . He provides a library of customized XPathNavigator classes that are suitable for using XPath to query file system, registry, assembly, and other data sources.