11.5. XML and ADO.NET

11.5. XML and ADO.NET

Just as relational data has a schema that defines its tables, columns , and relationships, XML uses a Schema Definition language (XSD) to define the layout of an XML document. Its main use is to validate the content of XML data. See Section 10.1, "Working with XML," for a discussion of XML Schema.

The XML classes reside in the System.Xml namespace hierarchy and are not part of ADO.NET. However, the ADO.NET DataSet class provides a bridge between the two with a set of methods that interacts with XML data and schemas:

• ReadXML . Loads XML data into a DatSet .

• WriteXml and GetXml. Writes the DataSet 's contents to an XML formatted stream.

• WriteXmlSchema and GetXmlSchema . Generates an XML Schema from the DataSet schema.

• ReadXmlSchema . Reads an XML Schema file and creates a database schema.

• InferXmlSchema . Creates a DataSet schema from XML data.

• GetXml and GetXmlSchema , Returns a string containing the XML representation of the data or the XSD schema for XML representation.

We'll first look at examples that show how to write XML from a DataSet . This XML output is then used as input in subsequent examples that create a DataSet from XML.

Using a DataSet to Create XML Data and Schema Files

When working with XML, the DataSet is used as an intermediary to convert between XML and relational data. For this to work, the XML data should be structured so that it can be represented by the relationships and row-column layout that characterizes relational data.

The following code segment illustrates how easy it is to create an XML data file and schema from a DataSet 's contents. A DataAdapter is used to populate a DataSet with a subset of the movies table. The WriteXml and WriteXmlSchema methods are then used to translate this to XML output.

DataSet ds = new DataSet("films");

DataTable dt = ds.Tables.Add("movies");

string sql = "SELECT * FROM movies WHERE bestPicture='Y'";

SqlDataAdapter da = new SqlDataAdapter(sql, conn);

da.Fill(dt);

// Write Schema representing DataTable to a file

ds.

WriteXmlSchema

("c:\oscars.xsd");  // create schema

// Write Table data to an XML file

ds.

WriteXml

("c:\oscarwinners.xml");  // data in xml format

/* To place schema inline with XML data in same file:

   ds.WriteXml(("c:\oscarwinners.xml",

              XmlWriteMode.WriteSchema);

*/

The schema output shown in Listing 11-7 defines the permissible content of an XML document (file). If you compare this with Figure 11-3 on page 502, you can get a general feel for how it works. For example, each field in the movies table is represented by an element containing the permissible field name and type.

Listing 11-7. XML Schema from Movies Table— oscars.xsd

<?xml version="1.0" encoding="utf-16"?>

<xs:schema  xmlns=""

xmlns:xs="http://www.w3.org/2001/XMLSchema"

xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

  <xs:element name="films" msdata:IsDataSet="true">

    <xs:complexType>

      <xs:choice minOccurs="0" maxOccurs="unbounded">

        <xs:element name="movies">

          <xs:complexType>

            <xs:sequence>

              <xs:element name="movie_ID" type="xs:int"

                        minOccurs="0" />

              <xs:element name="movie_Title" type="xs:string"

                        minOccurs="0" />

              <xs:element name="movie_Year" type="xs:int"

                        minOccurs="0" />

              <xs:element name="movie_DirectorID" type="xs:int"

                        minOccurs="0" />

              <xs:element name="AFIRank" type="xs:int"

                        minOccurs="0" />

              <xs:element name="bestPicture" type="xs:string"

                        minOccurs="0" />

            </xs:sequence>

          </xs:complexType>

        </xs:element>

      </xs:choice>

    </xs:complexType>

  </xs:element>

</xs:schema>

Listing 11-8 displays an abridged listing of the XML version of the relational data. The name of the DataSet is the root element. Each row in the table is represented by a child element ( movies) containing elements that correspond to the columns in the data table.

Listing 11-8. Movies Data as an XML Document— oscarwinners.xml

<?xml version="1.0" encoding="utf-16"?>

<?xml version="1.0" standalone="yes"?>

<films>

  <movies>

    <movie_ID>5</movie_ID>

    <movie_Title>Citizen Kane </movie_Title>

    <movie_Year>1941</movie_Year>

    <movie_DirectorID>1</movie_Director>

    <AFIRank>1</AFIRank>

    <bestPicture>Y</bestPicture>

  </movies>

  <movies>

    <movie_ID>6</movie_ID>

    <movie_Title>Casablanca </movie_Title>

    <movie_Year>1942</movie_Year>

    <movie_DirectorID>2</movie_Director>

    <AFIRank>2</AFIRank>

    <bestPicture>Y</bestPicture>

  </movies>

  ...

</films>

Creating a DataSet Schema from XML

Each ADO.NET DataSet has a schema that defines the tables, table columns, and table relationships that comprise the DataSet . As we saw in the preceding example, this schema can be translated into an XML schema using the WriteXmlSchema method. ReadXmlSchema mirrors the process—adding tables and relationships to a DataSet . In this example, the XML schema for the movies table (refer to Listing 11-7) is used to create a DataSet schema:

DataSet ds = new DataSet();

ds.

ReadXmlSchema

("c:\oscars.xsd");

DataTable tb = ds.Tables[0];

// List Columns for table

string colList = tb.TableName +": ";

for (int i = 0; i < tb.Columns.Count; i++)

   { colList += tb.Columns[i].Caption + "  "; }

Console.WriteLine(colList);

/* output is:

movies: movie_ID  movie_Title  movie_Year movie_DirectorID

        bestpicture AFIRank

*/

It is also possible to create a schema by inferring its structure from the XML data or using a DataAdapter to configure the schema:

// (1) Create schema by inferring it from XML data

ds.Tables.Clear();   // Remove tables from DataSet

ds.

InferXmlSchema

("c:\oscarwinners.xml",null);



// (2) Create schema using Data Adapter

ds.Tables.Clear();

string sql = "SELECT * FROM movies";

SqlDataAdapter da = new SqlDataAdapter(sql, connStr);

// Creates DataTable named "movies"

da.

FillSchema

(ds, SchemaType.Source, "movies");

Core Note

By creating the DataSet schema(s) in a separate step from reading in XML data, you can control the data that is read from the source XML file. Only data for the columns defined by the schema are read in. Conversely, if the schema defines more columns than are in the XML file, these columns are empty in the DataSet .

XmlReadMode ReadXml(string

XMLfilename

);

XmlReadMode ReadXml(string

XMLfilename

, XmlReadMode

mode

);

//  Load XML data into dataset and create schema if one does

//  not exist

DataSet ds = new DataSet();

ds.

ReadXml

("c:\oscarwinners.xml");

// Save source of data in dataset

ds.ExtendedProperties.Add("source", "c:\oscarwinners.xml");

ShowTable(ds.Tables[0]);



// Display each row in table

private void ShowTable(DataTable t)

{

   foreach(DataRow dr in t.Rows)

   {

      StringBuilder sb = new StringBuilder("Table: ");

      sb.Append(t.TableName).Append("\n");

      foreach(DataColumn c in t.Columns)

      {

          sb.Append(c.Caption).Append(": ");

          sb.Append(dr[c.ColumnName].ToString()).Append("\n");

      }

      Console.WriteLine(sb.ToString());



}

string src = (string)ds.ExtendedProperties["source"];

ds.ExtendedProperties.Clear(); // clear hash table

<films>

  <movies>

    <movie_ID>5</movie_ID>

    <movie_Title>Citizen Kane </movie_Title>

    <movie_Year>1941</movie_Year>

<director>

<first_name>Orson</first_name>

      <last_name>Welles</last_name>

</director>

<bestPicture>Y</bestPicture>

    <AFIRank>1</AFIRank>

  </movies>

  ... more movies here

</films>

DataSet ds = new DataSet();

ds.

ReadXml

("c:\oscarwinnersv2.xml");

foreach (DataTable dt in ds.Tables)

   ShowTable(dt);