Working with XML and ADO.NET

I l @ ve RuBoard

Chapter 5 introduced working with XML and ADO.NET. To refresh your memory, you learned that ADO.NET allows you to populate a DataSet from an XML document. You can read XML data into a DataSet , use an XML schema document to define the structure of the DataSet , or a combination of the two. Also in Chapter 5, you learned how to read an XML schema and XML data from files. To do this, you can invoke the ReadXml and ReadXmlSchema methods of a DataSet or use an XMLDataDocument object and invoke its Load and LoadXML methods. You also learned how an XMLDataDocument and DataSet synchronize, and you were briefly introduced to writing DataSet contents as XML and writing and retrieving XML Schema information. Here, we'll look at the following topics:

• Writing a DataSet as XML

• Loading DataSet Schema information from XML

• Writing DataSet Schema information as XML Schema

Writing a DataSet as XML

ADO.NET allows you to write a DataSet object's data as XML or write its structure as an XML schema. You can write either item to a file, a stream, or an XMLWriter , or write it as a string. Obviously, this functionality is useful for persisting DataSet information locally, but it is also useful for interacting with XML Web services (as you'll see in Chapter 17 ) and other means of sharing data within a distributed environment. If you simply want to get an XML representation of the DataSet that can be manipulated in memory, you can call the DataSet object's GetXML method, and to get the XML Schema, you call GetXMLSchema :

 //CreateandfillaDataSet DataSetds=newDataSet(); sqlDataAdapter.Fill(ds); //GettheXMLrepresentations StringdsXML=ds.GetXML(); StringdsSchema=ds.GetXMLSchema(); 

In contrast, if you want to write a representation of the DataSet as XML to a file, a stream, or an XMLWriter , you invoke the WriteXML method. This method can accept a single parameter, which is the output destination. Alternatively, you can pass two parameters, where the first is the output destination and the second is a member of the System.Data.XmlWriteMode enumeration. This enumeration has three members, which define the format of the XML to be written. Table 7-11 describes these members .

Table 7-13. System.Data.XmlWriteMode Enumeration Members

Member	Description
IgnoreSchema	Writes the contents of the DataSet as XML but does not write a schema. This is the default value.
WriteSchema	Writes the contents of the DataSet as XML and writes the DataSet object's structure as an inline XSD Schema.
DiffGram	Writes the contents of the DataSet as a DiffGram , which is an XML representation that includes both the current and original versions of the DataSet object's data.

For example, the DataSetAsXML.jsl sample file writes a DataSet object's content and structure to a text file. The structure of the document is included within the XML document containing the data rather than as a separate document. The sample file DataSet.xml shows the type of output generated by the WriteXML method.

DataSet.xml

 <?xmlversion="1.0" standalone="yes"?> <EmployeesDS> <!--InlineSchemastarts--> <xs:schemaid="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"  xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:elementname="NewDataSet" msdata:IsDataSet="true"  msdata:Locale="en-GB"> <xs:complexType> <xs:choicemaxOccurs="unbounded"> <xs:elementname="Employees"> <xs:complexType> <xs:sequence> <xs:elementname="EmployeeID" type="xs:int"  minOccurs="0" /> <xs:elementname="LastName" type="xs:string"  minOccurs="0" /> <xs:elementname="FirstName" type="xs:string"  minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema> <!--InlineSchemaends&datastarts--> <Employees> <EmployeeID>1</EmployeeID> <LastName>Davolio</LastName> <FirstName>Nancy</FirstName> </Employees> <!--Nodesetscutforbrevity--> <Employees> <EmployeeID>16</EmployeeID> <LastName>Peter</LastName> <FirstName>Roxburgh</FirstName> </Employees> </EmployeesDS> 

This chapter cannot provide a definitive guide to XML Schema, but a couple of items in the DataSet.xml sample file are worthy of our attention. The first is how the table and columns of the DataSet map to the XML elements. As you can see, the table is represented by an element, which has a child element for each of the columns the original DataSet contained. The second point to note is that XML Schema declares the data types of the elements that represent the original columns as two strings and an int . Of course, these are not the original data types, which were System.String and System.Int32 . For a complete reference for how XML Schema types map to .NET types, see the product documentation.

In the previous example, we wrote an XML schema together with the data, but sometimes you'll want to write only an XML schema. To do this, you can invoke the WriteXmlSchema method of the DataSet . The method accepts a single parameter, which is the destination to write to. For example, the following code fragment writes an XML schema that represents the structure of a DataSet to a StreamWriter :

 //CreateanewStreamWriterinstance StreamWritersw=newStreamWriter("mySchema.xsd"); //WritetheDataSet'sschematotheStreamWriter myDataSet.WriteXmlSchema(sw); 

Writing DataRelation Objects as XML

When you write XML to a destination or when you synchronize a DataSet with an XmlDataDocument , you can represent the relations between tables in the XML. When we wrote out the contents of a DataSet as XML earlier, in the DataRelationships.jsl file, you saw that the contents of each table appear in sequence within the XML. Therefore, only the data from the two tables is represented, and not the relationship between the two tables. Given the hierarchical nature of an XML document, you might want to use the relationship you've defined to have all of the elements representing child rows contained within their respective parent. To do this, you set the Nested property of the relationship to true .

 //SettheNestedproperty relationship.set_Nested(true); 

The NestedDataRelationships.jsl sample file shows how to set the Nested property to true and then write an XML representation of the DataSet to a local file. The contents of the XML file are shown in the NestedDataRelationships.xml sample file.

Inferring the DataSet Structure from XML

You're likely to encounter a situation in which you have an XML data source but no XML schema. Luckily, ADO.NET provides a limited means of inferring the XML schema of an XML document. There are two main approaches to generating an XML schema from a DataSet . The first is to call the InferXmlSchema method of the DataSet (one that contains data derived from an XML source). The second approach is to pass the InferSchema member of the System.Data.XmlReadMode enumeration as a second parameter to the ReadXml method of the DataSet . The InferDataSetSchemaFromXml.jsl sample file demonstrates this technique, and it prints the derived schema to the console.

When the code executes, it populates the DataSet by reading the XML source in the sample file Sample.xml. At the same time, it infers the XML schema of the underlying data. Finally, the code prints the XML Schema by calling the DataSet object's GetXmlSchema method, which returns a string. The XML Schema appears as follows :

 <?xmlversion="1.0" encoding="utf-16"?> <xs:schemaid="authors" xmlns=""  xmlns:xs="http://www.w3.org/2001/XMLSchema"  xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:elementname="authors" msdata:IsDataSet="true" msdata:Locale="en-GB"> <xs:complexType> <xs:choicemaxOccurs="unbounded"> <xs:elementname="author"> <xs:complexType> <xs:sequence> <xs:elementname="firstName" type="xs:string" minOccurs="0" /> <xs:elementname="lastName" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema> 

That's pretty impressive, but how does ADO.NET generate this XML schema from the XML data? The DataSet will examine the incoming XML and decide which XML elements should become tables and which should become columns in tables. This inference process is governed by a number of rules, as described in detail in the .NET Framework documentation. Essentially, the DataSet looks for elements that have attributes and infers these as tables. In addition, any elements that have child elements are inferred as tables. Other elements, and attributes, are inferred as columns in one of the inferred tables. In the Sample.xml sample file, which follows, elements that become tables are marked as bold:

Sample.xml

 <authors>  <author>  <firstName> John </firstName> <lastName> Sharp </lastName>  </author>   <author>  <firstName> Andy </firstName> <lastName> Longshaw </lastName>  </author>   <author>  <firstName> Peter </firstName> <lastName> Roxburgh </lastName>  </author>  </authors> 

The DataSet can also infer relationships through elements that are inferred as tables, which themselves are nested within elements that are inferred as tables. In this type of scenario, the DataSet creates a DataRelation between the two tables, and it also generates a new column in each table to help set up the relationship. An extra table is created in which this relationship is defined (thus linking the two tables based on the values in the new columns that have been added). The process then generates a ForeignKeyConstraint between the two tables. For more details about inferring relationships, and the limitations of the process, see the product documentation.

I l @ ve RuBoard