Basic Reading and Writing of XML

In Chapters 5 and 6 we demonstrated how to fill a DataSet with data either programmatically or from a database. Another method for loading data into a DataSet is reading in XML. As you would expect, you can also write the data in a DataSet as XML data. Further, the DataSet allows you to read and write XML schemas, either together with the XML data or separately.

Reading XML

ADO.NET provides rich and varied support for reading and writing both XML and XML schemas. Let's take a look at the basic use and operation of these methods and the properties that work with them.

As we've done in previous chapters, we'll build a simple form to demonstrate the fundamentals of working with ADO.NET and XML. Later in this chapter, in the Business Case, we illustrate a real-world use of ADO.NET and XML. Follow these steps as we build the form.

1. Create a new Visual Basic Windows Application project.

2. Name the project ADO-XML.

3. Specify a path for for saving the project files.

4. Enlarge the size of Form1.

5. In the Properties window for Form1, set its Name property to frmXML and its Text property to ADO.NET and XML.

6. In the upper-left-hand corner of the form, add a button from the Windows Forms tab of the Toolbox.

7. In the Properties window, set the Name property of the button to btnReadXML and set the Text property to Read XML.

8. From the Windows Forms tab of the Toolbox, add a DataGrid to frmXML and place it on the right side of the form.

9. In the Properties window, set the Name property of the DataGrid to grdData.

10. Enlarge the DataGrid so that it covers about 80 percent of the area of the form.

As usual, we add the following to the top of the file and then add the routine shown in Listing 10.1 to the frmXML class definition.

 Imports System  Imports System.Data Imports System.Data.SqlClient 

Listing 10.1 Reading the contents of an XML file into a DataSet

 Private Sub btnReadXML_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles btnReadXML.Click   Dim dsPubs As New DataSet()   ' Read in XML from file.   dsPubs.ReadXml("..\Pubs.xml")   ' Bind DataSet to Data Grid.   grdData.DataMember = "publishers"   grdData.DataSource = dsPubs End Sub 

This function reads the XML data from the file Pubs.xml into the DataSet. At this point, the DataSet and its data can be accessed in any of the ways that we have discussed in earlier chapters. In addition, this routine then binds the DataSet to a DataGrid. Listing 10.2 shows the contents of the file pubs.xml. Figure 10.1 shows the data displayed in a DataGrid.

Figure 10.1. The contents of the file Pubs.xml displayed in a DataGrid

Listing 10.2 The contents of the file Pubs.xml

 <?xml version="1.0" standalone="yes"?> <NewDataSet>   <publishers>     <pub_id>0736</pub_id>     <pub_name>New Moon Books</pub_name>     <city>Boston</city>     <state>MA</state>     <country>USA</country>   </publishers>   <publishers>     <pub_id>0877</pub_id>     <pub_name>Binnet &amp; Hardley</pub_name>     <city>Washington</city>     <state>DC</state>     <country>USA</country>   </publishers>   <publishers>     <pub_id>1389</pub_id>     <pub_name>Algodata Infosystems</pub_name>     <city>Berkeley</city>     <state>CA</state>     <country>USA</country>   </publishers>   <publishers>     <pub_id>1622</pub_id>     <pub_name>Five Lakes Publishing</pub_name>     <city>Chicago</city>     <state>IL</state>     <country>USA</country>   </publishers>   <publishers>     <pub_id>1756</pub_id>     <pub_name>Ramona Publishers</pub_name>     <city>Dallas</city>     <state>TX</state>     <country>USA</country>   </publishers>   <publishers>     <pub_id>9952</pub_id>     <pub_name>Scootney Books</pub_name>     <city>New York</city>     <state>NY</state>       <country>USA</country>     </publishers>     <publishers>       <pub_id>9999</pub_id>       <pub_name>Lucerne Publishing</pub_name>       <city>Paris</city>       <country>France</country>     </publishers>    </NewDataSet> 

Note

When ReadXML is used to load a DataSet, the RowState property of all of the (new) rows is set to Added. This approach is different from the default behavior when a DataAdapter is used to load a DataSet from a database, where the RowState property of all the rows is set to Unchanged. This approach allows the data to be loaded from an XML source and then inserted into a database table. If you don't want to do that, you can reset the RowState to Unchanged by calling the AcceptChanges method. If you want to change the default behavior when loading a DataSet from a database, setting DataAdapter's AcceptChangesOnFill property to False will cause the newly added rows to have a RowState of Added.

The preceding example demonstrates the simplest form of reading XML data into a DataSet reading it from a file. There are numerous other forms (function overloads) of this method for reading XML, including using a Stream, a TextReader, or an XmlReader. A parallel set of ReadXml methods also accepts a second parameter that specifies the value of XmlReadMode to use. This parameter is used to specify how to interpret the contents of the XML source, and how to handle the data's schema. Table 10.1 shows the XmlReadMode enumeration and describes the possible values.

Table 10.1. The XmlReadMode Enumeration

Enumeration Member Name	Description
ReadSchema	Reads any existing inline schema, loading both the data and the schema into the DataSet. Tables defined in the schema are added to the DataSet, but an exception is thrown if the schema defines a table that is already defined in the DataSet schema.
IgnoreSchema	Ignores any existing inline schema, loading the data into the DataSet by using the DataSet's existing schema definition. Any data that does not match the DataSet's schema is ignored and not loaded. Similarly, if no schema is defined, no data is loaded.
InferSchema	Ignores any existing inline schema and infers the schema from the structure of the data, and then loads the data into the DataSet. Tables and columns inferred from the data are added to the schema repeated in the DataSet. If they conflict with the existing definitions, an exception is thrown.
Fragment	Reads all existing XML fragments and loads the data into the DataSet. Any data that does not match the DataSet's schema is ignored and not loaded.
DiffGram	Reads a DiffGram and loads the data into the DataSet. New rows are merged with existing rows when the unique identifier values match; otherwise new rows are just added to the DataSet. If the schemas don't match, an exception is thrown.
Auto	The default mode. The most appropriate of the following options is performed: (1) if the XML data is a DiffGram, the XmlReadMode is set to DiffGram; (2) if a schema is defined in the DataSet or inline as part of the XML document, the XmlReadMode is set to ReadSchema; and (3) otherwise, the XmlReadMode is set to InferSchema.

A separate (overloaded) method of the DataSet, ReadXmlSchema, is available to read in just the schema information and not the actual data. It can be used simply to read the schema of a DataSet's DataTable(s), as in

 MyDataSet.ReadXmlSchema ("MySchemaFile.xml")  

The same four sources of data (file, Stream, TextReader, and XmlReader) are also available for the ReadXmlSchema method. The DataSet also has analogous sets of methods for the WriteXml and WriteXmlSchema methods, as described next.

Writing XML

Once we have loaded data and/or schema information into the DataSet, regardless of how or from where it was loaded, it can be written as XML and/or XML schemas. Follow along as we continue with the form frmXML we prepared earlier.

1. Add a button immediately below the btnReadXML button from the Windows Forms tab of the Toolbox.

2. In the Properties window, set the Name property of the button to btnWriteXML and set the Text property to Write XML.

3. Add the code shown in Listing 10.3.

Listing 10.3 Code to save the contents of a DataSet as an XML file

 Private Sub btnWriteXML_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles btnWriteXML.Click   Dim dsSales As New DataSet()   Dim cn As New SqlConnection _     ("data source=localhost;initial cata log=pubs;user id=sa")   Dim daAuthors As New SqlDataAdapter("select * from sales", cn)   Dim daPublishers As New SqlDataAdapter("select * from stores", cn)   ' Load Relational data from database.   daAuthors.Fill(dsSales, "Sales")   daPublishers.Fill(dsSales, "Stores")   'Write XML out to file.   dsSales.WriteXml("..\StoreSales.xml") End Sub 

The btnWriteXML_Click routine initializes two DataAdapters and then uses them to fill the dsPubs DataSet with the data from two tables in the SQL Server sample database "pubs". Listing 10.4 shows the contents of the file StoreSales.xml that this routine creates. Note that the XML document first contains the sales records and then, afterward, the stores' records. This approach makes sense because no relationship has been defined between the two tables. In cases where tables are related, you'll want the records to be nested. We give an example of nesting records later, in Business Case 10.1.

Listing 10.4 The contents of the file StoreSales.xml

 <?xml version="1.0" standalone="yes"?> <NewDataSet>   <Sales>     <stor_id>6380</stor_id>     <ord_num>6871</ord_num>     <ord_date>1994-09-14T00:00:00.0000000+02:00</ord_date>     <qty>5</qty>     <payterms>Net 60</payterms>     <title_id>BU1032</title_id>   </Sales>   <Sales>     <stor_id>6380</stor_id>     <ord_num>722a</ord_num>     <ord_date>1994-09-13T00:00:00.0000000+02:00</ord_date>     <qty>3</qty>     <payterms>Net 60</payterms>     <title_id>PS2091</title_id>   </Sales>   <Sales>     <stor_id>7066</stor_id>     <ord_num>A2976</ord_num>     <ord_date>1993-05-24T00:00:00.0000000+02:00</ord_date>     <qty>50</qty>     <payterms>Net 30</payterms>     <title_id>PC8888</title_id>   </Sales>   <Sales>     <stor_id>7066</stor_id>     <ord_num>QA7442.3</ord_num>     <ord_date>1994-09-13T00:00:00.0000000+02:00</ord_date>     <qty>75</qty>     <payterms>ON invoice</payterms>     <title_id>PS2091</title_id>   </Sales>   <Sales>     <stor_id>7067</stor_id>     <ord_num>D4482</ord_num>     <ord_date>1994-09-14T00:00:00.0000000+02:00</ord_date>     <qty>10</qty>     <payterms>Net 60</payterms>     <title_id>PS2091</title_id>   </Sales>   <Sales>     <stor_id>7067</stor_id>     <ord_num>P2121</ord_num>     <ord_date>1992-06-15T00:00:00.0000000+02:00</ord_date>     <qty>40</qty>     <payterms>Net 30</payterms>     <title_id>TC3218</title_id>   </Sales>   <Sales>     <stor_id>7067</stor_id>     <ord_num>P2121</ord_num>     <ord_date>1992-06-15T00:00:00.0000000+02:00</ord_date>     <qty>20</qty>     <payterms>Net 30</payterms>     <title_id>TC4203</title_id>   </Sales>   <Sales>     <stor_id>7067</stor_id>     <ord_num>P2121</ord_num>     <ord_date>1992-06-15T00:00:00.0000000+02:00</ord_date>     <qty>20</qty>     <payterms>Net 30</payterms>     <title_id>TC7777</title_id>   </Sales>   <Sales>     <stor_id>7131</stor_id>     <ord_num>N914008</ord_num>     <ord_date>1994-09-14T00:00:00.0000000+02:00</ord_date>     <qty>20</qty>     <payterms>Net 30</payterms>     <title_id>PS2091</title_id>   </Sales>   <Sales>     <stor_id>7131</stor_id>     <ord_num>N914014</ord_num>     <ord_date>1994-09-14T00:00:00.0000000+02:00</ord_date>     <qty>25</qty>     <payterms>Net 30</payterms>     <title_id>MC3021</title_id>   </Sales>   <Sales>     <stor_id>7131</stor_id>     <ord_num>P3087a</ord_num>     <ord_date>1993-05-29T00:00:00.0000000+02:00</ord_date>     <qty>20</qty>     <payterms>Net 60</payterms>     <title_id>PS1372</title_id>   </Sales>   <Sales>     <stor_id>7131</stor_id>     <ord_num>P3087a</ord_num>     <ord_date>1993-05-29T00:00:00.0000000+02:00</ord_date>     <qty>25</qty>     <payterms>Net 60</payterms>     <title_id>PS2106</title_id>   </Sales>   <Sales>     <stor_id>7131</stor_id>     <ord_num>P3087a</ord_num>     <ord_date>1993-05-29T00:00:00.0000000+02:00</ord_date>     <qty>15</qty>     <payterms>Net 60</payterms>     <title_id>PS3333</title_id>   </Sales>   <Sales>     <stor_id>7131</stor_id>     <ord_num>P3087a</ord_num>     <ord_date>1993-05-29T00:00:00.0000000+02:00</ord_date>     <qty>25</qty>     <payterms>Net 60</payterms>     <title_id>PS7777</title_id>   </Sales>   <Sales>     <stor_id>7896</stor_id>     <ord_num>QQ2299</ord_num>     <ord_date>1993-10-28T00:00:00.0000000+02:00</ord_date>     <qty>15</qty>     <payterms>Net 60</payterms>     <title_id>BU7832</title_id>   </Sales>   <Sales>     <stor_id>7896</stor_id>     <ord_num>TQ456</ord_num>     <ord_date>1993-12-12T00:00:00.0000000+02:00</ord_date>     <qty>10</qty>     <payterms>Net 60</payterms>     <title_id>MC2222</title_id>   </Sales>   <Sales>     <stor_id>7896</stor_id>     <ord_num>X999</ord_num>     <ord_date>1993-02-21T00:00:00.0000000+02:00</ord_date>     <qty>35</qty>     <payterms>ON invoice</payterms>     <title_id>BU2075</title_id>   </Sales>   <Sales>     <stor_id>8042</stor_id>     <ord_num>423LL922</ord_num>     <ord_date>1994-09-14T00:00: 00.0000000+02:00</ord_date>     <qty>15</qty>     <payterms>ON invoice</payterms>     <title_id>MC3021</title_id>   </Sales>   <Sales>     <stor_id>8042</stor_id>     <ord_num>423LL930</ord_num>     <ord_date>1994-09-14T00:00: 00.0000000+02:00</ord_date>     <qty>10</qty>     <payterms>ON invoice</payterms>     <title_id>BU1032</title_id>   </Sales>   <Sales>     <stor_id>8042</stor_id>     <ord_num>P723</ord_num>     <ord_date>1993-03-11T00:00: 00.0000000+02:00</ord_date>     <qty>25</qty>     <payterms>Net 30</payterms>     <title_id>BU1111</title_id>   </Sales>   <Sales>     <stor_id>8042</stor_id>     <ord_num>QA879.1</ord_num>     <ord_date>1993-05-22T00:00: 00.0000000+02:00</ord_date>     <qty>30</qty>     <payterms>Net 30</payterms>     <title_id>PC1035</title_id>   </Sales>   <Stores>     <stor_id>6380</stor_id>     <stor_name>Eric the Read Books</stor_name>     <stor_address>788 Catamaugus Ave.</stor_address>     <city>Seattle</city>     <state>WA</state>     <zip>98056</zip>   </Stores>   <Stores>     <stor_id>7066</stor_id>     <stor_name>Barnum's</stor_name>     <stor_address>567 Pasadena Ave.</stor_address>     <city>Tustin</city>     <state>CA</state>     <zip>92789</zip>   </Stores>   <Stores>     <stor_id>7067</stor_id>     <stor_name>News &amp; Brews</stor_name>     <stor_address>577 First St.</stor_address>     <city>Los Gatos</city>     <state>CA</state>     <zip>96745</zip>   </Stores>   <Stores>     <stor_id>7131</stor_id>     <stor_name>Doc-U-Mat: Quality Laundry and Books</stor_name>     <stor_address>24-A Avogadro Way</stor_address>     <city>Remulade</city>     <state>WA</state>     <zip>98014</zip>   </Stores>   <Stores>     <stor_id>7896</stor_id>     <stor_name>Fricative Bookshop</stor_name>     <stor_address>89 Madison St.</stor_address>     <city>Fremont</city>     <state>CA</state>     <zip>90019</zip>   </Stores>   <Stores>     <stor_id>8042</stor_id>     <stor_name>Bookbeat</stor_name>     <stor_address>679 Carson St.</stor_address>     <city>Portland</city>     <state>OR</state>     <zip>89076</zip>   </Stores> </NewDataSet> 

The overloaded WriteXml methods include a set that has a second parameter, XmlReadMode. This parameter is used to specify how to write the data and schema contents of the DataSet. Table 10.2 describes the XmlWriteMode enumeration values.

Table 10.2. The XmlWriteMode Enumeration

Enumeration Member Name	Description
DiffGram	Writes the DataSet contents as a DiffGram, with both original and current values for all rows.
WriteSchema	Writes the DataSet contents as XML data, including an inline XML schema. If there is a schema, but no data, the schema is written. If the DataSet does not have a schema defined, nothing is written.
IgnoreSchema	The default mode. Writes the DataSet contents as XML data, without a schema.

Note

The DataSet also has a method GetXml. This method returns a string of XML representing the data in the DataSet. It has the same effect as calling WriteXml with the XmlWriteMode set to IgnoreSchema. Fetching the data as a string may often be more flexible, but doing so requires more effort if all you want to do is to write the data to a file.

To write the DataSet's schema as an independent XSD schema file (instead of inline with the data), use the WriteSchema method:

 dsSales.WriteXmlSchema("..\StoreSales.xsd")  

Listing 10.5 shows the contents of the resulting StoreSales.xsd file.

Listing 10.5 The contents of StoreSales.xsd, which is the schema of the dsSales DataSet

 <?xml version="1.0" standalone="yes"?> <xs:schema  xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns: msdata="urn:schemas-microsoft-com:xml-msdata">   <xs:element name="NewDataSet" msdata:IsDataSet="true">   <xs:complexType>   <xs:choice maxOccurs="unbounded">     <xs:element name="Sales">       <xs:complexType>         <xs:sequence>           <xs:element name="stor_id" type="xs:string" minOccurs="0" />           <xs:element name="ord_num" type="xs:string" minOccurs="0" />           <xs:element name="ord_date" type="xs:dateTime" minOccurs="0" />           <xs:element name="qty" type="xs:short" minOccurs="0" />           <xs:element name="payterms" type="xs:string" minOccurs="0" />           <xs:element name="title_id" type="xs:string" minOccurs="0" />         </xs:sequence>       </xs:complexType>     </xs:element>     <xs:element name="Stores">       <xs:complexType>         <xs:sequence>           <xs:element name="stor_id" type="xs:string" minOccurs="0" />           <xs:element name="stor_name" type="xs:string" minOccurs="0" />           <xs:element name="stor_address" type="xs:string" minOccurs="0" />           <xs:element name="city" type="xs:string" minOccurs="0" />           <xs:element name="state" type="xs:string" minOccurs="0" />           <xs:element name="zip" type="xs:string" minOccurs="0" />         </xs:sequence>       </xs:complexType>     </xs:element>   </xs:choice>   </xs:complexType>   </xs:element> </xs:schema> 

DiffGrams

The enumerations for both XmlReadMode and XmlWriteMode refer to XML formatted as a DiffGram, but we haven't yet discussed this format. A DiffGram is an XML format that not only contains the current values of the data elements, but also contains the original values of rows that have been modified or deleted (since the last call to AcceptChanges). That is, a DiffGram is the serialization format that the DataSet uses to transport its data to another process or computer. Because it is XML, it can also be used to pass data easily to and from other platforms, such as UNIX or Linux.

A DiffGram is divided into three sections. The first section contains the current values, regardless of whether they have been modified, of all of the rows in the DataSet. Any element (row) that has been modified is indicated by the diffgr:hasChanges="modified"annotation and any added element (row) is indicated by the diffgr:hasChanges="inserted" annotation. The second section contains the original values of modified and deleted rows. These elements are linked to the corresponding elements in the first section by the diffgr: annotation, where "xxx" is the specific row identifier. The third section contains error information for specific rows.Here, too, the error elements are linked to the elements in the first section via the diffgr: annotation.

You can generate a DiffGram XML file by adding code to the end of the btnWriteXML_Click subroutine of Listing 10.1 to make some changes to the data in the DataSet and then writing the data as a DiffGram, as follows:

   Private Sub btnWriteXML_Click(ByVal sender As System.Object,_        ByVal e As System.EventArgs) Handles btnWriteXML.Click     Dim dsSales As New DataSet()     Dim cn As New SqlConnection _     ("data source=localhost;initial cata log=pubs;user id=sa")     Dim daAuthors As New SqlDataAdapter("select * from sales", cn)     Dim daPublishers As New SqlDataAdapter("select * from stores", cn)     ' Load Relational data from database.     daAuthors.Fill (dsSales, "Sales")     daPublishers.Fill (dsSales, "Stores")     ' Write XML out to file.     dsSales.WriteXml("..\StoreSales.xml")     ' Write out schema as XSD file.     dsSales.WriteXmlSchema("..\StoreSales.xsd")     'Make same changes modify, delete, and insert a new row     dsSales.Tables("Stores").Rows(0)("stor_id") = 999 ' Modify     dsSales.Tables("Stores").Rows(1).Delete() ' Delete     Dim rr As DataRow = dsSales.Tables("Stores").NewRow()     rr("stor_name") = "New Store"     dsSales.Tables("Stores").Rows.Add(rr) ' Insert     ' Write out data as DiffGram.     dsSales.WriteXml("..\DiffGram.xml", XmlWriteMode.DiffGram) End Sub 

Listing 10.6 shows the contents of the file DiffGram.xml, which is produced by running the ADO-XML project and clicking on the Write XML button. Because the changes were made to the Stores table, they appear at the end of the file in boldface. The row deleted no longer appears in the section of current data, but appears in the "before" section along with the original values of the modified row. The current data section also contains the new row, marked as "inserted".

Listing 10.6 A DiffGram XML file with one inserted row, one deleted row, and one modified row

 <?xml version="1.0" standalone="yes"?> <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn: schemas-microsoft-com:xml-diffgram-v1">   <NewDataSet>     <Sales diffgr: msdata:rowOrder="0">       <stor_id>6380</stor_id>       <ord_num>6871</ord_num>       <ord_date>1994-09-14T00:00:00.0000000+02:00</ord_date>       <qty>5</qty>       <payterms>Net 60</payterms>       <title_id>BU1032</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="1">       <stor_id>6380</stor_id>       <ord_num>722a</ord_num>       <ord_date>1994-09-13T00:00: 00.0000000+02:00</ord_date>       <qty>3</qty>       <payterms>Net 60</payterms>       <title_id>PS2091</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="2">       <stor_id>7066</stor_id>       <ord_num>A2976</ord_num>       <ord_date>1993-05-24T00:00:00.0000000+02:00</ord_date>       <qty>50</qty>       <payterms>Net 30</payterms>       <title_id>PC8888</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="3">       <stor_id>7066</stor_id>       <ord_num>QA7442.3</ord_num>       <ord_date>1994-09-13T00:00: 00.0000000+02:00</ord_date>       <qty>75</qty>       <payterms>ON invoice</payterms>       <title_id>PS2091</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="4">       <stor_id>7067</stor_id>       <ord_num>D4482</ord_num>       <ord_date>1994-09-14T00:00: 00.0000000+02:00</ord_date>       <qty>10</qty>       <payterms>Net 60</payterms>       <title_id>PS2091</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="5">       <stor_id>7067</stor_id>       <ord_num>P2121</ord_num>       <ord_date>1992-06-15T00:00: 00.0000000+02:00</ord_date>       <qty>40</qty>       <payterms>Net 30</payterms>       <title_id>TC3218</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="6">       <stor_id>7067</stor_id>       <ord_num>P2121</ord_num>       <ord_date>1992-06-15T00:00: 00.0000000+02:00</ord_date>       <qty>20</qty>       <payterms>Net 30</payterms>       <title_id>TC4203</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="7">       <stor_id>7067</stor_id>       <ord_num>P2121</ord_num>       <ord_date>1992-06-15T00:00: 00.0000000+02:00</ord_date>       <qty>20</qty>       <payterms>Net 30</payterms>       <title_id>TC7777</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="8">       <stor_id>7131</stor_id>       <ord_num>N914008</ord_num>       <ord_date>1994-09-14T00:00: 00.0000000+02:00</ord_date>       <qty>20</qty>       <payterms>Net 30</payterms>       <title_id>PS2091</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="9">       <stor_id>7131</stor_id>       <ord_num>N914014</ord_num>       <ord_date>1994-09-14T00:00: 00.0000000+02:00</ord_date>       <qty>25</qty>       <payterms>Net 30</payterms>       <title_id>MC3021</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="10">       <stor_id>7131</stor_id>       <ord_num>P3087a</ord_num>       <ord_date>1993-05-29T00:00: 00.0000000+02:00</ord_date>       <qty>20</qty>       <payterms>Net 60</payterms>       <title_id>PS1372</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="11">       <stor_id>7131</stor_id>       <ord_num>P3087a</ord_num>       <ord_date>1993-05-29T00:00: 00.0000000+02:00</ord_date>       <qty>25</qty>       <payterms>Net 60</payterms>       <title_id>PS2106</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="12">       <stor_id>7131</stor_id>       <ord_num>P3087a</ord_num>       <ord_date>1993-05-29T00:00: 00.0000000+02:00</ord_date>       <qty>15</qty>       <payterms>Net 60</payterms>       <title_id>PS3333</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="13">       <stor_id>7131</stor_id>       <ord_num>P3087a</ord_num>       <ord_date>1993-05-29T00:00: 00.0000000+02:00</ord_date>       <qty>25</qty>       <payterms>Net 60</payterms>       <title_id>PS7777</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="14">       <stor_id>7896</stor_id>       <ord_num>QQ2299</ord_num>       <ord_date>1993-10-28T00:00: 00.0000000+02:00</ord_date>       <qty>15</qty>       <payterms>Net 60</payterms>       <title_id>BU7832</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="15">       <stor_id>7896</stor_id>       <ord_num>TQ456</ord_num>       <ord_date>1993-12-12T00:00: 00.0000000+02:00</ord_date>       <qty>10</qty>       <payterms>Net 60</payterms>       <title_id>MC2222</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="16">       <stor_id>7896</stor_id>       <ord_num>X999</ord_num>       <ord_date>1993-02-21T00:00: 00.0000000+02:00</ord_date>       <qty>35</qty>       <payterms>ON invoice</payterms>       <title_id>BU2075</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="17">       <stor_id>8042</stor_id>       <ord_num>423LL922</ord_num>       <ord_date>1994-09-14T00:00: 00.0000000+02:00</ord_date>       <qty>15</qty>       <payterms>ON invoice</payterms>       <title_id>MC3021</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="18">       <stor_id>8042</stor_id>       <ord_num>423LL930</ord_num>       <ord_date>1994-09-14T00:00: 00.0000000+02:00</ord_date>       <qty>10</qty>       <payterms>ON invoice</payterms>       <title_id>BU1032</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="19">       <stor_id>8042</stor_id>       <ord_num>P723</ord_num>       <ord_date>1993-03-11T00:00: 00.0000000+02:00</ord_date>       <qty>25</qty>       <payterms>Net 30</payterms>       <title_id>BU1111</title_id>     </Sales>     <Sales diffgr: msdata:rowOrder="20">       <stor_id>8042</stor_id>       <ord_num>QA879.1</ord_num>       <ord_date>1993-05-22T00:00: 00.0000000+02:00</ord_date>       <qty>30</qty>       <payterms>Net 30</payterms>       <title_id>PC1035</title_id>     </Sales>     <Stores diffgr: msdata:rowOrder="0" diffgr:hasChanges= "modified">       <stor_id>999</stor_id>       <stor_name>Eric the Read Books</stor_name>       <stor_address>788 Catamaugus Ave.</stor_address>       <city>Seattle</city>       <state>WA</state>       <zip>98056</zip>     </Stores>     <Stores diffgr: msdata:rowOrder="2">       <stor_id>7067</stor_id>       <stor_name>News &amp; Brews</stor_name>       <stor_address>577 First St.</stor_address>       <city>Los Gatos</city>       <state>CA</state>       <zip>96745</zip>     </Stores>     <Stores diffgr: msdata:rowOrder="3">       <stor_id>7131</stor_id>       <stor_name>Doc-U-Mat: Quality Laundry and Books</stor_name>       <stor_address>24-A Avogadro Way</stor_address>       <city>Remulade</city>       <state>WA</state>       <zip>98014</zip>     </Stores>     <Stores diffgr: msdata:rowOrder="4">       <stor_id>7896</stor_id>       <stor_name>Fricative Bookshop</stor_name>       <stor_address>89 Madison St.</stor_address>       <city>Fremont</city>       <state>CA</state>       <zip>90019</zip>     </Stores>     <Stores diffgr: msdata:rowOrder="5">       <stor_id>8042</stor_id>       <stor_name>Bookbeat</stor_name>       <stor_address>679 Carson St.</stor_address>       <city>Portland</city>       <state>OR</state>       <zip>89076</zip>     </Stores>     <Stores diffgr: msdata:rowOrder="6" diffgr:hasChanges="inserted">       <stor_name>New Store</stor_name>     </Stores>       </NewDataSet>       <diffgr:before>     <Stores diffgr: msdata:rowOrder="0">       <stor_id>6380</stor_id>       <stor_name>Eric the Read Books</stor_name>       <stor_address>788 Catamaugus Ave.</stor_address>       <city>Seattle</city>       <state>WA</state>       <zip>98056</zip>     </Stores>     <Stores diffgr: msdata:rowOrder="1">       <stor_id>7066</stor_id>       <stor_name>Barnum's</stor_name>       <stor_address>567 Pasadena Ave.</stor_address>       <city>Tustin</city>       <state>CA</state>       <zip>92789</zip>     </Stores>   </diffgr:before> </diffgr:diffgram> 

Note

If you want a DiffGram that contains only the changed rows in the DataSet that have been modified, you can first call the GetChanges method:

 Dim ChangedDataSet = dsSales.GetChanges()  ChangedDataSet.WriteXml("..\Changes.xml", XmlWriteMode.DiffGram) 

The resulting DiffGram file is shown in Listing 10.7.

Listing 10.7 A DiffGram XML file, showing only changed rows

 <?xml version="1.0" standalone="yes"?> <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"  xmlns:diffgr="urn: schemas-microsoft-com:xml-diffgram-v1">   <NewDataSet>     <Stores diffgr: msdata:rowOrder="0" diffgr:hasChanges="modified">       <stor_id>999</stor_id>       <stor_name>Eric the Read Books</stor_name>       <stor_address>788 Catamaugus Ave.</stor_address>       <city>Seattle</city>       <state>WA</state>       <zip>98056</zip; mt     </Stores>     <Stores diffgr: msdata:rowOrder="2" diffgr:hasChanges="inserted">       <stor_name>New Store</stor_name>     </Stores>   </NewDataSet>   <diffgr:before>     <Stores diffgr: msdata:rowOrder="0">       <stor_id>6380</stor_id>       <stor_name>Eric the Read Books</stor_name>       <stor_address>788 Catamaugus Ave.</stor_address>       <city>Seattle</city>       <state>WA</state>       <zip>98056</zip; mt     </Stores>     <Stores diffgr: msdata:rowOrder="1">       <stor_id>7066</stor_id>       <stor_name>Barnum's</stor_name>       <stor_address>567 Pasadena Ave.</stor_address>       <city>Tustin</city>       <state>CA</state>       <zip>92789</zip; mt     </Stores>   </diffgr:before> </diffgr:diffgram> 

Business Case 10.1: Preparing XML Files for Business Partners

The Jones Novelty company is increasingly working electronically with many of its business suppliers and customers. This trend will require a more extensive solution several years down the road, in which case something like Microsoft BizTalk Server would probably be appropriate. In the meantime, Brad Jones still needs to meet his current demands for using XML to support electronic transactions and to "get his feet wet" in this area. Jones is going to use some of the XML capabilities that we've just discussed, along with one or two additional features, to meet these requirements. He can get pretty far, even without the "heavy-duty" platforms and tools or the more advanced XML technologies such as XSLT.

First, Jones wants to send an XML file of the items he has in inventory. All the table columns except the WholesalePrice column are to be sent; he currently isn't interested in sharing or exposing that information. Although he could obviously get what he wants by creating a query hat includes all but that one column, he chooses a technique involving the use of XML properties. The other requirements for this XML include an inline XSD schema that describes the data and exposure of all the columns as elements, except for the ID column, which is exposed as an attribute.

Building this application is very straightforward. Jones's database developer does the following:

1. Creates a new Visual Basic Windows Application project

2. Names the project BusinessCase10

3. Specifies a path for saving the project files

4. Enlarges the size of Form1

5. In the Properties window for Form1, sets its Name property to frmPrepareXML and its Text property to Prepare XML

6. In the upper-left corner of the form, adds a button from the Windows Forms tab of the Toolbox

In the Properties window, she sets the Name property of the button to btnInventory and the Text property to Create Inventory XML. As usual, she adds

 Imports System  Imports System.Data Imports System.Data.SqlClient 

to the top of the file. Then she adds the following routine within the frmPrepareXML class definition:

   Dim cn As New SqlConnection _      ("data source=localhost;initial cata log=Novelty;user id=sa")   Private Sub btnInventory_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles btnInventory.Click     Dim dsInventory As New DataSet()     Dim daInventory As New SqlDataAdapter _         ("select * from tblInventory ", cn)     daInventory.Fill(dsInventory, "tblInventory")     'Write ID column as XML attribute, rather then element     dsInventory.Tables("tblInventory").Columns("ID").ColumnMapping = _         MappingType.Attribute     ' Hide the WholesalePrice column from the saved XML     dsInventory.Tables("tblInventory").Columns ("WholesalePrice").ColumnMapping =  MappingType.Hidden     ' Write data as XML file, including inline schema.     dsInventory.WriteXml("..\Inventory.xml", XmlWriteMode.WriteSchema) End Sub 

After the DataSet has been filled with the data from the database, two code statements specify how to form the XML. The first,

 dsInventory.Tables("tblInventory").Columns("ID").ColumnMapping = MappingType.Attribute  

specifies that the ID column should be saved as an XML attribute. The second,

 dsInventory.Tables("tblInventory").Columns("WholesalePrice"). _      ColumnMapping = MappingType.Hidden 

specifies that the WholeSale price column should be hidden and not written as part of the XML.

Finally, when the data has been written, the second parameter to the WriteXml method specifies that the schema should be included along with the actual data. The resulting file is shown in Listing 10.8.

Listing 10.8 The tblInventory table saved as an XML file

 <?xml version="1.0" standalone="yes"?> <NewDataSet>   <xs:schema  xmlns="'' xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns: msdata="urn:schemas-microsoft-com:xml-msdata">     <xs:element name="NewDataSet" msdata:IsDataSet="true">       <xs:complexType>         <xs:choice maxOccurs="unbounded">           <xs:element name="tblInventory">             <xs:complexType>               <xs:sequence>                 <xs:element name="ProductName" type="xs:string" minOccurs="0" msdata: Ordinal="1" />                 <xs:element name="RetailPrice" type="xs:decimal" minOccurs="0" msdata: Ordinal="3" />                 <xs:element name="Description" type="xs:string" minOccurs="0" msdata: Ordinal="4" />               </xs:sequence>               <xs:attribute name="ID" type="xs:int" />               <xs:attribute name="WholesalePrice" type="xs:decimal" use="prohibited" />             </xs:complexType>           </xs:element>         </xs:choice>       </xs:complexType>     </xs:element>   </xs:schema>   <tblInventory >     <ProductName>Rubber Chicken</ProductName>     <RetailPrice>2.99</RetailPrice>     <Description>The quintessential rubber chicken.</Description>   </tblInventory>   <tblInventory >     <ProductName>Joy Buzzer</ProductName>     <RetailPrice>9.99</RetailPrice>     <Description>They will get a real shock out of this.</Description>   </tblInventory>   <tblInventory >     <ProductName>Seltzer Bottle</ProductName>     <RetailPrice>15.24</RetailPrice>     <Description>Seltzer sold separately.</Description>   </tblInventory>   <tblInventory >     <ProductName>Ant Farm</ProductName>     <RetailPrice>14.99</RetailPrice>     <Description>Watch ants where they live and breed.</Description>   </tblInventory>   <tblInventory >     <ProductName>Wind-Up Robot</ProductName>     <RetailPrice>29.99</RetailPrice>     <Description>Giant robot:attack toybox!</Description>   </tblInventory>   <tblInventory >     <ProductName>Rubber Eyeballs</ProductName>     <RetailPrice>0.99</RetailPrice>     <Description>Peek-a-boo!</Description>   </tblInventory>   <tblInventory >     <ProductName>Doggy Mess</ProductName>     <RetailPrice>1.99</RetailPrice>     <Description>Yechhh!</Description>   </tblInventory>   <tblInventory >     <ProductName>Mini-Camera</ProductName>     <RetailPrice>9.99</RetailPrice>     <Description>For future spies!</Description>   </tblInventory>   <tblInventory >     <ProductName>Glow Worms</ProductName>     <RetailPrice>1.99</RetailPrice>     <Description>Makes them easy to find</Description>   </tblInventory>   <tblInventory >     <ProductName>Insect Pops</ProductName>     <RetailPrice>0.99</RetailPrice>     <Description>Special treats</Description>   </tblInventory>   <tblInventory >     <ProductName>Alien Alarm Clock</ProductName>     <RetailPrice>45.99</RetailPrice>     <Description>Do you know what time it is out there?</Description>   </tblInventory>   <tblInventory >     <ProductName>Cinnamon Toothpicks</ProductName>     <RetailPrice>1.99</RetailPrice>     <Description>Really wakes up your mouth</Description>   </tblInventory>    </NewDataSet> 

The second issue that Jones has to deal with is the fact that the company handling the payroll for Jones Novelty wants the basic employee information in XML format, organized by department. The database developer adds a second button, btnEmployees, to frmPrepareXML, and adds the code shown in Listing 10.9 to the frmPrepareXML class.

Listing 10.9 Code to save data from both tblEmployee and tblDepartment as an XML file

 Private Sub btnEmployees_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles btnEmployees.Click   Dim dsEmployees As New DataSet()   Dim daEmployees As New SqlDataAdapter _       ("select * from tblEmployee", cn)   Dim daDepartments As New SqlDataAdapter _       ("select * from tblDepartment", cn)   daDepartments.Fill(dsEmployees, "tblDepartment")   daEmployees.Fill(dsEmployees, "tblEmployee")   ' Define Relation between tables.   dsEmployees.Relations.Add("DepartmentEmployees", _   dsEmployees.Tables("tblDepartment").Columns("ID"), _   dsEmployees.Tables("tblEmployee").Columns ("DepartmentID"))   ' Write data as XML file.   dsEmployees.WriteXml("..\Employees.xml") End Sub 

This code uses the default settings of the DataSet and saves the data from both tblEmployee and tblDepartment. The XML produced is shown in Listing 10.10.

Listing 10.10 XML produced to save the data from tblDepartment and tblEmployee

 <?xml version="1.0" standalone="yes"?> <NewDataSet>   <tblDepartment>     <ID>1</ID>     <DepartmentName>Administration</DepartmentName>   </tblDepartment>   <tblDepartment>     <ID>2</ID>     <DepartmentName>Engineering</DepartmentName>   </tblDepartment>   <tblDepartment>     <ID>3</ID>     <DepartmentName>Sales</DepartmentName>   </tblDepartment>   <tblDepartment>     <ID>4</ID>     <DepartmentName>Marketing</DepartmentName>   </tblDepartment>   <tblEmployee>     <ID>2032</ID>     <FirstName>Carole</FirstName>     <LastName>Vermeren</LastName>     <DepartmentID>2</DepartmentID>     <Salary>222</Salary>   </tblEmployee>   <tblEmployee>     <ID>2033</ID>     <FirstName>Cathy</FirstName>     <LastName>Johnson</LastName>     <DepartmentID>2</DepartmentID>     <Salary>13000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2034</ID>     <FirstName>Eric</FirstName>     <LastName>Haglund</LastName>     <DepartmentID>4</DepartmentID>     <Salary>12000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2035</ID>     <FirstName>Julie</FirstName>     <LastName>Ryan</LastName>     <DepartmentID>1</DepartmentID>     <Salary>4000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2036</ID>     <FirstName>Richard</FirstName>     <LastName>Halpin</LastName>     <DepartmentID>2</DepartmentID>     <Salary>10000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2037</ID>     <FirstName>Kathleen</FirstName>     <LastName>Johnson</LastName>     <DepartmentID>3</DepartmentID>     <Salary>18000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2038</ID>     <FirstName>Sorel</FirstName>     <LastName>Polito</LastName>     <DepartmentID>4</DepartmentID>     <Salary>28000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2039</ID>     <FirstName>Sorel</FirstName>     <LastName>Terman</LastName>     <DepartmentID>1</DepartmentID>     <Salary>8000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2040</ID>     <FirstName>Randy</FirstName>     <LastName>Hobaica</LastName>     <DepartmentID>2</DepartmentID>     <Salary>18000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2041</ID>     <FirstName>Matthew</FirstName>     <LastName>Haglund</LastName>     <DepartmentID>3</DepartmentID>     <Salary>30000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2042</ID>     <FirstName>Cathy</FirstName>     <LastName>Vermeren</LastName>     <DepartmentID>4</DepartmentID>     <Salary>0</Salary>   </tblEmployee>   <tblEmployee>     <ID>2043</ID>     <FirstName>Brad</FirstName>     <LastName>Townsend</LastName>     <DepartmentID>2</DepartmentID>     <Salary>12000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2044</ID>     <FirstName>Jennifer</FirstName>     <LastName>Eves</LastName>     <DepartmentID>2</DepartmentID>     <Salary>26000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2045</ID>     <FirstName>Steve</FirstName>     <LastName>Marshall</LastName>     <DepartmentID>3</DepartmentID>     <Salary>42000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2046</ID>     <FirstName>Laura</FirstName>     <LastName>Davidson</LastName>     <DepartmentID>4</DepartmentID>     <Salary>60000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2047</ID>     <FirstName>Angela</FirstName>     <LastName>Stefanac</LastName>     <DepartmentID>2</DepartmentID>     <Salary>16000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2048</ID>     <FirstName>Marjorie</FirstName>     <LastName>Bassett</LastName>     <DepartmentID>2</DepartmentID>     <Salary>34000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2049</ID>     <FirstName>Joe</FirstName>     <LastName>Chideya</LastName>     <DepartmentID>3</DepartmentID>     <Salary>54000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2050</ID>     <FirstName>Katie</FirstName>     <LastName>Chideya</LastName>     <DepartmentID>4</DepartmentID>     <Salary>76000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2051</ID>     <FirstName>Terri</FirstName>     <LastName>Allen</LastName>     <DepartmentID>1</DepartmentID>     <Salary>20000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2052</ID>     <FirstName>Mike</FirstName>     <LastName>Doberstein</LastName>     <DepartmentID>2</DepartmentID>     <Salary>42000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2053</ID>     <FirstName>Terri</FirstName>     <LastName>Woodruff</LastName>     <DepartmentID>3</DepartmentID>     <Salary>66000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2054</ID>     <FirstName>Cathy</FirstName>     <LastName>Rosenthal</LastName>     <DepartmentID>4</DepartmentID>     <Salary>5555</Salary>   </tblEmployee>   <tblEmployee>     <ID>2055</ID>     <FirstName>Margaret</FirstName>     <LastName>Eves</LastName>     <DepartmentID>1</DepartmentID>     <Salary>24000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2056</ID>     <FirstName>Mikki</FirstName>     <LastName>Lemay</LastName>     <DepartmentID>2</DepartmentID>     <Salary>50000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2057</ID>     <FirstName>Randy</FirstName>     <LastName>Nelson</LastName>     <DepartmentID>3</DepartmentID>     <Salary>78000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2058</ID>     <FirstName>Kathleen</FirstName>     <LastName>Husbands</LastName>     <DepartmentID>4</DepartmentID>     <Salary>108000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2059</ID>     <FirstName>Kathleen</FirstName>     <LastName>Eberman</LastName>     <DepartmentID>1</DepartmentID>     <Salary>28000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2060</ID>     <FirstName>Richard</FirstName>     <LastName>Rosenthal</LastName>     <DepartmentID>2</DepartmentID>     <Salary>58000</Salary>   </tblEmployee>   <tblEmployee>     <ID>2061</ID>     <FirstName>Mike</FirstName>     <LastName>Woodruff</LastName>     <DepartmentID>3</DepartmentID>     <Salary>90000</Salary>   </tblEmployee> </NewDataSet> 

Unfortunately, this XML isn't really what the payroll vendor wants. Even though the database developer has created a Relation to link the parent table (tblDepartment) to the child table (tblEmployee), the XML produced still lists the data from the two tables separately. To nest the child elements within the parent elements, she needs to set the Relation's Nested property to True:

 dsEmployees.Relations("DepartmentEmployees").Nested = True  

If she adds the preceding line before writing the XML, she gets the results shown in Listing 10.11, which is what the payroll vendor really wants.

Listing 10.11 XML file with the tblEmployee data nested within the tblDepartment data>

 <?xml version="1.0" standalone="yes"?> <NewDataSet>   <tblDepartment>     <ID>1</ID>     <DepartmentName>Administration</DepartmentName>     <tblEmployee>       <ID>2035</ID>       <FirstName>Julie</FirstName>       <LastName>Ryan</LastName>       <DepartmentID>1</DepartmentID>       <Salary>4000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2039</ID>       <FirstName>Sorel</FirstName>       <LastName>Terman</LastName>       <DepartmentID>1</DepartmentID>       <Salary>8000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2051</ID>       <FirstName>Terri</FirstName>       <LastName>Allen</LastName>       <DepartmentID>1</DepartmentID>       <Salary>20000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2055</ID>       <FirstName>Margaret</FirstName>       <LastName>Eves</LastName>       <DepartmentID>1</DepartmentID>       <Salary>24000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2059</ID>       <FirstName>Kathleen</FirstName>       <LastName>Eberman</LastName>       <DepartmentID>1</DepartmentID>       <Salary>28000</Salary>     </tblEmployee>   </tblDepartment>   <tblDepartment>     <ID>2</ID>     <DepartmentName>Engineering</DepartmentName>     <tblEmployee>       <ID>2032</ID>       <FirstName>Carole</FirstName>       <LastName>Vermeren</LastName>       <DepartmentID>2</DepartmentID>       <Salary>222</Salary>     </tblEmployee>     <tblEmployee>       <ID>2033</ID>       <FirstName>Cathy</FirstName>       <LastName>Johnson</LastName>       <DepartmentID>2</DepartmentID>       <Salary>13000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2036</ID>       <FirstName>Richard</FirstName>       <LastName>Halpin</LastName>       <DepartmentID>2</DepartmentID>       <Salary>10000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2040</ID>       <FirstName>Randy</FirstName>       <LastName>Hobaica</LastName>       <DepartmentID>2</DepartmentID>       <Salary>18000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2043</ID>       <FirstName>Brad</FirstName>       <LastName>Townsend</LastName>       <DepartmentID>2</DepartmentID>       <Salary>12000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2044</ID>       <FirstName>Jennifer</FirstName>       <LastName>Eves</LastName>       <DepartmentID>2</DepartmentID>       <Salary>26000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2047</ID>       <FirstName>Angela</FirstName>       <LastName>Stefanac</LastName>       <DepartmentID>2</DepartmentID>       <Salary>16000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2048</ID>       <FirstName>Marjorie</FirstName>       <LastName>Bassett</LastName>       <DepartmentID>2</DepartmentID>       <Salary>34000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2052</ID>       <FirstName>Mike</FirstName>       <LastName>Doberstein</LastName>       <DepartmentID>2</DepartmentID>       <Salary>42000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2056</ID>       <FirstName>Mikki</FirstName>       <LastName>Lemay</LastName>       <DepartmentID>2</DepartmentID>       <Salary>50000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2060</ID>       <FirstName>Richard</FirstName>       <LastName>Rosenthal</LastName>       <DepartmentID>2</DepartmentID>       <Salary>58000</Salary>     </tblEmployee>     </tblDepartment>   <tblDepartment>     <ID>3</ID>     <DepartmentName>Sales</DepartmentName>     <tblEmployee>       <ID>2037</ID>       <FirstName>Kathleen</FirstName>       <LastName>Johnson</LastName>       <DepartmentID>3</DepartmentID>       <Salary>18000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2041</ID>       <FirstName>Matthew</FirstName>       <LastName>Haglund</LastName>       <DepartmentID>3</DepartmentID>       <Salary>30000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2045</ID>       <FirstName>Steve</FirstName>       <LastName>Marshall</LastName>       <DepartmentID>3</DepartmentID>       <Salary>42000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2049</ID>       <FirstName>Joe</FirstName>       <LastName>Chideya</LastName>       <DepartmentID>3</DepartmentID>       <Salary>54000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2053</ID>       <FirstName>Terri</FirstName>       <LastName>Woodruff</LastName>       <DepartmentID>3</DepartmentID>       <Salary>66000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2057</ID>       <FirstName>Randy</FirstName>       <LastName>Nelson</LastName>       <DepartmentID>3</DepartmentID>       <Salary>78000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2061</ID>       <FirstName>Mike</FirstName>       <LastName>Woodruff</LastName>       <DepartmentID>3</DepartmentID>       <Salary>90000</Salary>     </tblEmployee>   </tblDepartment>   <tblDepartment>     <ID>4</ID>     <DepartmentName>Marketing</DepartmentName>     <tblEmployee>       <ID>2034</ID>       <FirstName>Eric</FirstName>       <LastName>Haglund</LastName>       <DepartmentID>4</DepartmentID>       <Salary>12000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2038</ID>       <FirstName>Sorel</FirstName>       <LastName>Polito</LastName>       <DepartmentID>4</DepartmentID>       <Salary>28000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2042</ID>       <FirstName>Cathy</FirstName>       <LastName>Vermeren</LastName>       <DepartmentID>4</DepartmentID>       <Salary>0</Salary>     </tblEmployee>     <tblEmployee>       <ID>2046</ID>       <FirstName>Laura</FirstName>       <LastName>Davidson</LastName>       <DepartmentID>4</DepartmentID>       <Salary>60000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2050</ID>       <FirstName>Katie</FirstName>       <LastName>Chideya</LastName>       <DepartmentID>4</DepartmentID>       <Salary>76000</Salary>     </tblEmployee>     <tblEmployee>       <ID>2054</ID>       <FirstName>Cathy</FirstName>       <LastName>Rosenthal</LastName>       <DepartmentID>4</DepartmentID>       <Salary>5555</Salary>     </tblEmployee>     <tblEmployee>       <ID>2058</ID>       <FirstName>Kathleen</FirstName>       <LastName>Husbands</LastName>       <DepartmentID>4</DepartmentID>       <Salary>108000</Salary>     </tblEmployee>   </tblDepartment> </NewDataSet>