Lesson 6: Using XML in ADO.NET

Lesson 6: Using XML in ADO.NET

XML is the behind-the-scenes foundation of ADO.NET. Data is described in the XML format, and XML representations of DataSet objects, DataTable objects, and schemas can be written from in-memory representations of data and persisted as XML text files or streams. The XmlDataDocument class allows you to work directly with in-memory representations of XML and synchronize them with a DataSet. In this lesson, you will learn techniques for using the XML-based methods provided by ADO.NET.

After this lesson, you will be able to

• Explain how to retrieve an XmlReader from a SqlCommand object

• Describe how to fill a DataSet from an XML text file

• Describe how to write the contents of a DataSet to an XML stream

• Explain how to write the schema of a DataSet to an XML stream

• Describe how to read an XML schema onto a DataSet

• Explain how to create an XmlDataDocument and synchronize it with a DataSet

• Describe how to execute an Extensible Stylesheet Lanaguage Transformation (XSLT) transform on an XmlDataDocument

Estimated lesson time: 45 minutes

Retrieving XML from a SQL Server 2000 Database

Microsoft SQL Server 2000 contains built-in support for XML-based data access. Data can be retrieved from the database and read into memory in the XML format. The ADO.NET SQL Data Provider provides built-in support for retrieving XML from the database.

Retrieving an XmlReader with a SqlCommand

The SqlCommand class provides a method for retrieving data as XML. The ExecuteXmlReader returns an XmlReader object that exposes the data returned by the SqlCommand as a set of XML rows. The SQL SELECT query executed by the ExecuteXmlReader method must contain a FOR XML clause. This method is only available with the SqlCommand class and can be used only when connecting to SQL Server 2000 or later.

The XmlReader class is analogous to the DataReader class. It provides read-only, forward-only access to the XML returned by the query. Like the DataReader, the XmlReader requires the exclusive use of a connection.

The XmlReader exposes a Read method, similar to the Read method of a Data Reader, which allows you to iterate through the nodes that are returned. Like a DataReader, the Read method advances the XmlReader to the next node of the XML stream and returns false when the last node is reached. Also like the Data Reader, you must call the Read method before the first node is accessible. The following example demonstrates how to retrieve an XmlReader and read the XML stream it returns:

Visual Basic .NET

' This example assumes the existence of a valid SqlConnection named ' SqlConnection1. Dim myReader As Xml.XmlReader Dim mySQLCommand As New SqlClient.SqlCommand( _ "SELECT * FROM Customers FOR XML AUTO, XMLDATA", SqlConnection1) SqlConnection1.Open myReader = mySQLCommand.ExecuteXmlReader() While myReader.Read() ' Writes the content, including markup, of this node and any child ' nodes to the Console Console.WriteLine(myReader.ReadOuterXml()) End While myReader.Close SqlConnection1.Close

Visual C#

// This example assumes the existence of a valid SqlConnection named // SqlConnection1. System.Xml.XmlReader myReader; SqlCommand mySQLCommand = new SqlCommand( "SELECT * FROM Customers FOR XML AUTO, XMLDATA", SqlConnection1); SqlConnection1.Open(); myReader = mySQLCommand.ExecuteXmlReader(); while (myReader.Read()) { // Writes the content, including markup, of this node and any // child nodes to the Console Console.WriteLine(myReader.ReadOuterXml()); } myReader.Close(); SqlConnection1.Close();

Using XML with DataSets

DataSets provide methods for interacting with data stored as XML. You can load data stored as an XML file or stream into a DataSet, or you can write the data represented in a DataSet to an XML file or stream. You can create typed DataSet objects of a known structure by reading an XML schema into the DataSet, and you can create a template for other typed DataSets by writing the structure of the DataSet to an XML schema.

Reading XML into a DataSet

You can access XML data stores by using the DataSet.ReadXml method. This method allows you to specify an existing XML file or stream, or an existing XmlReader or TextReader object, and read the schema and data represented therein into a DataSet. The following code example demonstrates how to read XML from a file called myData.xml into a new DataSet:

Visual Basic .NET

Dim myDataSet As New DataSet() myDataSet.ReadXml("C:\myData.XML")

Visual C#

DataSet myDataSet = new DataSet(); myDataSet.ReadXml("C:\\myData.XML");

If you want to create a DataSet with a specified structure but not load any data, you can read an XML schema with the DataSet.ReadXmlSchema method. Like the ReadXml method, the ReadXmlSchema method allows you to specify an existing XML file or stream, or an existing XmlReader or TextReader object, but ReadXmlSchema only reads the structure of the data into the DataSet, not the data itself. The following code example demonstrates how to read an XML schema from a file called mySchema.xml:

Visual Basic .NET

Dim myDataSet As New DataSet() myDataSet.ReadXmlSchema("C:\mySchema.XML")

Visual C#

DataSet myDataSet = new DataSet(); myDataSet.ReadXmlSchema("C:\\mySchema.XML");

To read XML into a DataSet

Call the DataSet.ReadXml method.

To read an XML schema into a DataSet

Call the DataSet.ReadXmlSchema method.

Writing XML from a DataSet

DataSet objects can write the data they contain and the schema that describes the data as XML files. The DataSet object provides the WriteXml method to facilitate exporting data to an XML format. The WriteXml method allows you to specify a file, a stream, or an XmlWriter or TextWriter object to receive the XML output from the DataSet. The following code example demonstrates how to write the contents of a DataSet to an XML file called myXml.xml. If the specified file is not present, it is created automatically.

Visual Basic .NET

myDataSet.WriteXml("C:\myData.XML")

Visual C#

myDataSet.WriteXml("C:\\myData.XML");

Similarly, you can use the WriteXmlSchema method to write the structure of the DataSet without writing any of the data. The following code example writes a DataSet schema to a file called mySchema.xml:

Visual Basic .NET

myDataSet.WriteXmlSchema("C:\mySchema.XML")

Visual C#

myDataSet.WriteXmlSchema("C:\\mySchema.XML");

To write the contents of a DataSet to XML

Call the DataSet.WriteXml method.

To write the schema of a DataSet to XML

Call the DataSet.WriteXmlSchema method.

Using the XmlDataDocument Class

The XmlDataDocument class is designed to work closely with a DataSet. An XmlDataDocument is an in-memory representation of XML data, just as a DataSet is an in-memory representation of relational data. Data loaded into an XmlDataDocument can be manipulated using the W3C Document Object Model (DOM), and an XmlDataDocument can serve as a source for XSLT.

NOTE
A comprehensive discussion of XML document manipulation is beyond the scope of this text and would fill several chapters. This section touches upon the basics of working with the XmlDataDocument class.

Every XmlDataDocument has an associated DataSet. You can specify a preexisting DataSet when the XmlDataDocument is created by supplying a reference to the DataSet as a parameter in the constructor. An example follows:

Visual Basic .NET

Dim myDocument As New Xml.XmlDataDocument(myDataSet)

Visual C#

XmlDataDocument myDocument = new XmlDataDocument(myDataSet);

This synchronizes the XmlDataDocument with the DataSet. The data and schema contained in the DataSet are read into the XmlDataDocument automatically. When changes are made to one, the other is updated.

To create an XmlDataDocument from a preexisting DataSet

Supply a reference to the existing DataSet to the XmlDataDocument constructor. The data and schema of the DataSet will be loaded into the XmlDataDocument.

You can also create an XmlDataDocument without specifying an existing DataSet, as follows:

Visual Basic .NET

Dim myDocument As New Xml.XmlDataDocument()

Visual C#

XmlDataDocument myDocument = new XmlDataDocument();

In this case, a new, empty DataSet is created and associated with the XmlDataDocument. In either case, the DataSet associated with a particular XmlDataDocument can be retrieved through the XmlDataDocument.DataSet property.

You can load XML data into an XmlDataDocument from an XML file, an XML stream, an XmlReader object, or a TextReader that is reading an XML document. To load XML data into the XmlDataDocument and synchronize it with its DataSet, you must first call the ReadXmlSchema method of the DataSet, supplying the appropriate XML source for the schema. To be loaded into the XmlDataDocument, this schema must match the schema for the XML source, and usually the same XML source is used. Next the Load method of the XmlDataDocument is used to load the XML data into memory. The following code example demonstrates how to load XML data contained in a file named myXml.xml into an XmlDataDocument:

Visual Basic .NET

Dim myDocument As New Xml.XmlDataDocument() myDocument.DataSet.ReadXmlSchema("C:\myXml.xml") myDocument.Load("C:\myXml.xml")

Visual C#

XmlDataDocument myDocument = new XmlDataDocument(); myDocument.DataSet.ReadXmlSchema("C:\\myXml.xml"); myDocument.Load("C:\\myXml.xml");

To fill an XmlDataDocument from an XML data source

1. Create an instance of an XmlDataDocument using the parameterless constructor, as follows:

   Visual Basic .NET

   Dim myDocument As New Xml.XmlDataDocument()

   Visual C#

   XmlDataDocument myDocument = new XmlDataDocument();

2. Call the ReadXmlSchema method of the associated DataSet to load the XML schema into the DataSet, as follows:

   Visual Basic .NET

   myDocument.DataSet.ReadXmlSchema("C:\myXml.xml")

   Visual C#

   myDocument.DataSet.ReadXmlSchema("C:\\myXml.xml");

3. Call the XmlDataDocument.Load method to load the XML data. For example:

   Visual Basic .NET

   myDocument.Load("C:\myXml.xml")

   Visual C#

   myDocument.Load("C:\\myXml.xml");

Executing XSLT Transformations

XSLT is designed to facilitate transforming XML data into different formats. For example, an XML document might be converted to HTML for display on a Web page or it might be converted to a different XML format for a specialized application.

The .NET Framework provides the XslTransform class to execute XSLT transformations. To execute a transformation, the XslTransform class first needs to load a style sheet. This is a file that contains the formatting instructions for the XML data. You use the XslTransform.Load method to load the style sheet. The style sheet can be specified as either a URL that points to an XSL file that contains the style definitions or as any of a number of classes that contain an in-memory representation of the style sheet. The following code example declares a new XslTransform object and loads a style sheet file called myStyle.xsl:

Visual Basic .NET

Dim myTransform as New System.Xml.Xsl.XslTransform() myTransform.Load("C:\myStyle.xsl")

Visual C#

Xml.Xsl.XslTransform myTransform = new System.Xml.Xsl.XslTransform(); myTransform.Load("C:\\myStyle.xsl");

After the style sheet is loaded, you can execute the transformation on an XmlDataDocument. The XslTransform.Transform method requires three parameters; the first parameter is the object to transform. This can be an XmlDataDocument or any object that implements the IXPathNavigable interface. The second parameter is an instance of the System.Xml.Xsl.XsltArgumentList and is used to contain any parameters required by the style sheet. If the style sheet requires no parameters, you can pass Nothing (null) to this parameter. The third parameter is the object to which the output must be written. This can be an instance of a Stream, a TextWriter, or an XmlWriter. The following code example demonstrates how to apply a transform to an XmlDataDocument named myDocument and write the resulting transform to a text file. In this example, no parameters are used, so the second parameter will be Nothing (null).

Visual Basic .NET

' The StreamWriter will receive the output from the transform and ' write it to a text file Dim myWriter As New System.IO.StreamWriter("myTextFile.txt") myTransform.Transform(myDocument, Nothing, myWriter)

Visual C#

// The StreamWriter will receive the output from the transform and // write it to a text file System.IO.StreamWriter myWriter = new System.IO.StreamWriter("myTextFile.txt"); myTransform.Transform(myDocument, null, myWriter);

To execute an XSLT transformation

1. Declare an instance of System.Xml.Xsl.XslTransform.

2. Load the XSL style sheet with the XslTransform.Load method.

3. Call the XslTransform.Transform method to execute the transformation on an XmlDataDocument. You must supply any parameters required by the style sheet in the form of an XsltArgumentList object.

Lesson Summary

• XML is the behind-the-scenes foundation of ADO.NET. Data can be represented in memory or in a file as XML.

• An instance of an XmlReader can be obtained by executing a SqlCommand that contains a SELECT command that includes a valid FOR XML clause. The XmlReader provides connected, read-only, forward-only access to a database in XML format.

• The DataSet object can read and write data formatted as XML. The ReadXml and WriteXml methods are used to load XML into a DataSet and write data in XML format, respectively. DataSets can also read and write XML schemas with the ReadSchema and WriteSchema methods.

• The XmlDataDocument is an in-memory representation of an XML document that is synchronized with a DataSet. Any changes made to the DataSet are directly transmitted to the XmlDataDocument and vice versa.

• XSLT transforms are used to transform XML from one format to another. Transforms can be executed on an XmlDataDocument and require an XSLT style sheet that contains the definition for the transformation.