XML and Data

I l @ ve RuBoard

XML has become a popular choice for the representation of data in applications. Before XML, almost all "serious" data in applications was held in a database of some form, usually a relational database. Developers became accustomed to dealing with data from relational databases in terms of the structure and relationships between tables (embodied in the schema of the database) and the SQL statements used to retrieve data from those tables.

If you consider that XML data also has a schema and a query mechanism (XPath), you can see that the two models have many similarities. In some cases, it is easier to manipulate and transport application data in terms of database artifacts, such as set of database records as embodied in an ADO.NET DataSet . In other cases, it is preferable to manipulate and transport data in the form of an XML document. This convergence of XML and traditional data sources can be seen in the advent of XML generation and consumption facilities provided by database products, such as the SQLXML capabilities added to SQL Server.

Given the need for flexibility in data representation, and the similarities between the two models, the .NET Framework allows you to easily work with data that can be accessed using XML or traditional database techniques.

Links Between XML and ADO.NET Data

An ADO.NET DataSet is a powerful representation of underlying application data. ADO.NET is discussed in detail in Chapter 7, but for our purposes here, you need to know the following:

• An ADO.NET DataSet contains representations of one or more tables, views on those tables, and the definitions of relationships between them.

• A DataSet can contain data from one or more data sources.

• The tables in a DataSet appear as an indexed property of type DataTable .

• Each table has a Rows property, which represents the rows of data it contains. The type that represents a row is the DataRow class.

• You can obtain metadata from the tables, such as the number of columns , the names of the columns, and their data types.

• You can perform SQL queries based on the contents of the DataSet and retrieve an array of DataRows .

All of the classes you will see relating to the use of ADO.NET DataSet objects are defined in the System.Data namespace.

You can use the mechanisms you've seen so far, such as DOM, to read in the data from an XML document and use this data to programmatically populate an ADO.NET DataSet . You can also read the contents of an ADO.NET DataSet and generate an XML document from them using an XmlWriter . However, life is generally too short to indulge in this form of marshaling. As you'll see, you can create an ongoing relationship between an XML document, in the shape of an XmlDataDocument , and a DataSet such that you can use either view of the data when it suits you. The XmlDataDocument is a subclass of XmlDocument , and it provides DOM-style access to the data it contains. The really cool thing about the link between an XmlDataDocument and a DataSet is that any changes made while you work in one format are automatically reflected in the other format.

Viewing XML as Relational Data

If you're starting with XML data, you can load your data in one of two ways. One way is to create and populate an XmlDataDocument . You can use the Load and LoadXml methods you saw previously to load in the XML document. If you have an XML schema for your document, you can also load this into the DataSet . This helps the XmlDataDocument map your XML data into a table-based format. However, if you do not have a schema for your XML, don't panic ”the XmlDataDocument can usually infer the required schema information from any well-structured XML.

The following code fragment, taken from the sample file XmlAsDataSet.jsl, shows how you can initialize an XmlDataDocument :

 XmlDataDocumentdoc=newXmlDataDocument(); doc.get_DataSet().ReadXmlSchema("SchemaCakeCatalog.xsd"); doc.Load("SchemaCakeCatalog.xml"); DataTablecakeTypes=doc.get_DataSet().get_Tables().get_Item("CakeType"); 

The XmlDataDocument has a DataSet property through which you can obtain the related DataSet . In this instance, the DataSet is retrieved and its ReadXmlSchema method is used to read in the schema. The XML document is then loaded into the XmlDataDocument using the Load method. Once the data is in place, you can access it through the DataSet class, just as if it had been retrieved through a SQL query to a relational database. This process means that you can get hold of the Tables property of the associated DataSet that holds the collection of tables. You can then access the Item property to retrieve a table by name . The names of the tables will relate to the names of the elements in the XML document. In this case, the document consists of a set of CakeType elements, so this is reflected in the DataSet as a table called CakeType . You can use this name to retrieve the associated DataTable .

An alternative to starting with the XmlDataDocument is to commence with the DataSet . You can load XML directly into a DataSet , again optionally providing an XML schema before you do:

 DataSetds=newDataSet(); ds.ReadXml("SchemaCakeCatalog.xml"); DataTablecakeTypes=ds.get_Tables().get_Item("CakeType"); 

The ReadXml method is an overloaded method that takes a variety of sources of XML documents. The method also allows you to specify where to get a schema from if one has not been provided. If you do not provide an XML schema to the DataSet before loading the XML file, the DataSet will infer the schema from the XML document itself. You can see an example of loading an XML document into a DataSet in this way in the sample file InferDataSetSchemaFromXml.jsl.

Manipulating XML as Relational Data

Once you've obtained a DataTable to represent part of your XML document, you can access the content as if it were relational data. For example, you can read individual column values in particular rows, as shown here:

 Console.WriteLine("Firstcakemessageis " + cakeTypes.get_Rows().get_Item(0).get_Item("Message")); 

The DataTable has an indexed Rows property that allows you to retrieve a DataRow by offset. The code shown gets the first DataRow in the DataTable and then uses the Item property of the DataRow to access the contents of a particular column by name.

Even though it is useful to have access to all of the data in the table, you'll often want a subset of the data. When you connect to a relational database, you'll do so by issuing SQL queries that return rows that meet specified criteria. In the case of the CakeCatalog , this might be all of the cake types that have a fruit filling. The SQL statement for this would look something like this:

 SELECT*FROMCakeTypeWHEREfilling='fruit' 

The code that you've seen already retrieved the equivalent of the CakeType table, so you can then query within those results using the Select method on the DataTable :

 DataRow[]rows=cakeTypes.Select("filling='fruit'"); 

You can then loop through this array and print out the contents of each row. The DataRow has an Item collection property that you can use to access the individual column values. To use this property, you must obtain the number of columns from the DataTable as follows :

 DataColumnCollectioncolumns=cakeTypes.get_Columns(); intnumColumns=columns.get_Count(); for(inti=0;i<rows.length;i++) { for(intj=0;j<numColumns;j++) { Console.Write(rows[i].get_Item(j)+ ", "); } Console.WriteLine(); } 

The final task for now is to obtain the column names so that you know which data items relate to which parts of the XML document. You can do this by looping through the DataTable object's collection of columns and retrieving the name of each one in turn :

 for(inti=0;i<numColumns;i++) { Console.Write(columns.get_Item(i).get_ColumnName()+ ", "); } Console.WriteLine(); 

The code in the XmlAsDataSet.jsl and InferDataSetSchemaFromXml.jsl sample files show the whole sequence of events, starting from an XMLDataDocument and from a DataSet .

Viewing Relational Data as XML

Now that you know how an XML document is mapped into a DataSet , it should be fairly straightforward to see how data from a DataSet can be represented as an XML document:

• The contents of the DataSet class can be obtained in XML format using the GetXml method, which returns the XML as a String , or the WriteXml method, which sends the XML document to a file, a Stream , a TextWriter , or an XmlWriter .

• You can retrieve an XML schema that describes the data in the DataSet using the GetXmlSchema method, which returns the XML schema as a String , or the WriteXmlSchema method, which sends the XML schema to a file, a Stream , a TextWriter , or an XmlWriter .

• Given a DataSet , you can instantiate an XmlDataDocument by passing the DataSet into the constructor. This delivers an XML document representation that is synchronized with the contents of the DataSet .

I l @ ve RuBoard