Reading and Writing XML Data

The SQL XML .NET Data Provider

There's an easier way to fetch XML data from SQL Server: using the SQL XML .NET Data Provider. This .NET Data Provider is not included in the .NET Framework, but you should be able to find it on the MSDN or SQL Server Web site by the time this book becomes available. Once you've installed the .NET Data Provider, you can use it in your applications by adding a reference to the Microsoft.Data.SqlXml namespace.

The SQL XML .NET Data Provider is designed to help .NET developers access SQL Server's XML features. This provider is very different from the other providers because SQL Server's XML features aren't traditional data access features. The initial release of the SQL XML .NET Data Provider consists of only three objects from the "traditional" .NET Data Provider (if you can refer to anything as traditional this close to the initial release of a set of technologies): the SqlXmlCommand, the SqlXmlAdapter, and the SqlXmlParameter.

If you want to access the results of a SQL Server XML query, you should consider using the SQL XML .NET Data Provider. Let's see why.

Using a SqlXmlCommand to Load Data into an XmlDocument

Storing the results of a SQL Server XML query into an XmlDocument object is simpler than using a SqlXmlCommand. The SqlXmlCommand object offers a single constructor that requires a connection string to your SQL Server database. The SQL XML .NET Data Provider doesn't talk directly to your database, but it can communicate using OLE DB. You can therefore use the same connection string that you use for an OleDbConnection object.

As with the OleDbCommand object, you use the CommandText property to specify the query you want to execute. You can then use the ExecuteXmlReader object to execute the query and fetch the results in an XmlReader object.

In the previous code snippet, the XML data that the query returned was a fragment rather than a well-formed document because the results did not have a top-level node. The SqlXmlCommand object has a RootTag property that you can use to add a top-level node to the results of a query, which means that the results will constitute a well-formed document. You can thus use the XmlDocument object's Load method rather than programmatically add a top-level node to the XmlDocument and then add the results of the query one node at a time, as shown here:

Visual Basic .NET

'Add the following lines of code at the beginning of the code module. Imports Microsoft.Data.SqlXml Imports System.Xml Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT TOP 2 CustomerID, CompanyName FROM Customers " & _          "FOR XML AUTO, ELEMENTS" Dim cmd As New SqlXmlCommand(strConn) cmd.CommandText = strSQL cmd.RootTag = "ROOT" Dim xmlDoc As New XmlDocument() Dim rdr As XmlReader = cmd.ExecuteXmlReader xmlDoc.Load(rdr) rdr.Close() Dim strPathToXml As String = "C:\MyData.XML" xmlDoc.Save(strPathToXml) ShowXmlInIE(strPathToXml)

Visual C# .NET

//Add the following lines of code at the beginning of the code module. using Microsoft.Data.SqlXml; using System.Xml; string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT TOP 2 CustomerID, CompanyName FROM Customers " +          "FOR XML AUTO, ELEMENTS"; SqlXmlCommand cmd = new SqlXmlCommand(strConn); cmd.CommandText = strSQL; cmd.RootTag = "ROOT"; XmlDocument xmlDoc = new XmlDocument(); XmlReader rdr = cmd.ExecuteXmlReader(); xmlDoc.Load(rdr); rdr.Close(); string strPathToXml = "C:\\MyData.XML"; xmlDoc.Save(strPathToXml); ShowXmlInIE(strPathToXml);

Using a SqlXmlAdapter to Load Data into a DataSet

You could use the same process to load the contents of the XmlReader object into a DataSet, but the SQL XML .NET Data Provider offers a simpler way—using the SqlXmlAdapter. Just as you can easily store the results of a standard SQL query in a DataSet using an OleDbDataAdapter, you can store the results of a FOR XML query in a DataSet using a SqlXmlAdapter.

You use the same code to create your SqlXmlCommand, and then you create a SqlXmlAdapter, specifying your SqlXmlCommand in the constructor. You can then fill your DataSet with the results of your query by calling the SqlXmlAdapter object's Fill method, as shown in the following code snippet:

Visual Basic .NET

'Add the following lines of code at the beginning of the code module. Imports Microsoft.Data.SqlXml Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT TOP 2 CustomerID, CompanyName FROM Customers " & _          "FOR XML AUTO, ELEMENTS" Dim cmd As New SqlXmlCommand(strConn) cmd.CommandText = strSQL cmd.RootTag = "ROOT" Dim da As New SqlXmlAdapter(cmd) Dim ds As New DataSet() da.Fill(ds) Dim strPathToXml As String = "C:\MyData.XML" ds.WriteXml(strPathToXml) ShowXmlInIE(strPathToXml)

Visual C# .NET

//Add the following lines of code at the beginning of the code module. using Microsoft.Data.SqlXml; string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT TOP 2 CustomerID, CompanyName FROM Customers " +          "FOR XML AUTO, ELEMENTS"; SqlXmlCommand cmd = new SqlXmlCommand(strConn); cmd.CommandText = strSQL; cmd.RootTag = "ROOT"; Dim da As New SqlXmlAdapter(cmd) Dim ds As New DataSet() da.Fill(ds) string strPathToXml = "C:\\MyData.XML"; ds.WriteXml(strPathToXml); ShowXmlInIE(strPathToXml);

Working with Template Queries

To give you greater control over the format of the results of your queries, the SQL XML .NET Data Provider supports XML template queries. Basically, a template query is an XML document that contains queries. When you execute such a query, the SQL XML .NET Data Provider combines the XML in the query along with the results of your query.

Let's look at an example of a template query. The template query that follows contains two SELECT...FOR XML queries that retrieve order and line item information for a particular customer:

<?xml version="1.0" encoding="utf-8" ?>  <ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>   <sql:query>     SELECT OrderID, CustomerID, OrderDate FROM Orders         WHERE CustomerID = 'GROSR'         FOR XML AUTO, ELEMENTS   </sql:query>   <sql:query>     SELECT OrderID, ProductID, Quantity, UnitPrice         FROM [Order Details] WHERE OrderID IN         (SELECT OrderID FROM Orders WHERE CustomerID = 'GROSR')         FOR XML AUTO, ELEMENTS   </sql:query> </ROOT>

The query itself is an XML document. The SQL XML .NET Data Provider examines the elements that reside in the sql namespace and executes the text in the query elements. The rest of the elements are treated simply as XML and appear in the results as such.

You can execute this query using the SQL XML .NET Data Provider, and you'll receive the XML document shown in Figure 12-7. As you can see in the figure, the root element in the template query appears in the results.

Figure 12-7

The results of an XML template query

Executing a Template Query Using a SqlXmlCommand

To tell the SqlXmlCommand object that you're working with a template query using the SqlXmlCommand object, you set the SqlXmlCommand object's CommandType property to the appropriate value in the SqlXmlCommandType enumeration. If you supply the path to the file that contains your query, set the CommandType property to TemplateFile. If, instead, you supply the actual text of the query, you should set the CommandType property to Template.

You can then execute the query to store the results in an XML document or a DataSet, as shown in the previous examples. The following code snippet stores the results in a DataSet:

Visual Basic .NET

'Add the following lines of code at the beginning of the code module. Imports Microsoft.Data.SqlXml Dim strPathToResults As String = "C:\MyResults.XML" Dim strPathToQuery As String = "C:\MyTemplateQuery.XML" Dim strConn As String = "Provider=SQLOLEDB;" & _                         "Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;" & _                         "Trusted_Connection=Yes;" Dim cmd As New SqlXmlCommand(strConn) cmd.CommandText = strPathToQuery cmd.CommandType = SqlXmlCommandType.TemplateFile Dim ds As New DataSet() Dim da As New SqlXmlAdapter(cmd) da.Fill(ds) ds.WriteXml(strPathToResults) ShowXmlInIE(strPathToResults)

Visual C# .NET

//Add the following lines of code at the beginning of the code module. using Microsoft.Data.SqlXml; string strPathToResults = "C:\\MyResults.XML"; string strPathToQuery = "C:\\MyTemplateQuery.XML"; string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; SqlXmlCommand cmd = new SqlXmlCommand(strConn); cmd.CommandText = strPathToQuery; cmd.CommandType = SqlXmlCommandType.TemplateFile; DataSet ds = new DataSet(); SqlXmlAdapter da = new SqlXmlAdapter(cmd); da.Fill(ds); ds.WriteXml(strPathToResults); ShowXmlInIE(strPathToResults);

Parameterized Template Queries

You can also add parameters to template queries. The following template query retrieves the same information—orders and line item information for a customer—except the query contains a parameter marker for the value of the CustomerID column rather than specifies that value explicitly:

<?xml version="1.0" encoding="utf-8" ?>  <ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>   <sql:header>     <sql:param name="CustomerID"/>   </sql:header>   <sql:query>     SELECT OrderID, CustomerID, OrderDate FROM Orders         WHERE CustomerID = @CustomerID         FOR XML AUTO, ELEMENTS   </sql:query>   <sql:query>     SELECT OrderID, ProductID, Quantity, UnitPrice         FROM [Order Details] WHERE OrderID IN         (SELECT OrderID FROM Orders WHERE CustomerID = @CustomerID)         FOR XML AUTO, ELEMENTS   </sql:query> </ROOT>

To set the value of this parameter programmatically, you use the SqlXmlParameter object. You can't create a SqlXmlParameter using the New keyword. The only way to create one is to use the CreateParameter method of the SqlCommand object. Once you have your SqlXmlParameter object, you set its Name and Value properties accordingly before executing your query, as shown in the following code snippet:

Visual Basic .NET

Dim cmd As SqlXmlCommand  Dim param As SqlXmlParameter = cmd.CreateParameter() param.Name = "@CustomerID" param.Value = "GROSR"

Visual C# .NET

SqlXmlCommand cmd;  SqlXmlParameter param = cmd.CreateParameter(); param.Name = "@CustomerID"; param.Value = "GROSR";

Working with XPath Queries

If we had an XML document that contained all the orders in the Northwind database, we could use the following XPath query to examine just the orders for the customer whose CustomerID is GROSR:

Orders[CustomerID='GROSR']

If you look at the SqlXmlCommandType enumeration, you'll find an XPath entry. You could set the CommandType property of a SqlXmlCommand to XPath, supply the XPath query in the CommandText, and then execute the query, but doing so would throw an exception stating that the query is invalid.

The SQL Server database engine doesn't really know what to do with an XPath query. The SQL XML .NET Data Provider supports XPath queries, but what it actually does is translate XPath queries into SELECT...FOR XML queries. Although you can interpret the query and perform this translation, the SQL XML .NET Data Provider needs a little help.

Adding Schema Information

You can help the SQL XML .NET Data Provider translate this XPath query by supplying an XML schema that defines the tables and columns in your database to include in the query as well as the structure for the results, as shown here:

<?xml version="1.0" ?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"             xmlns:sql="urn:schemas-microsoft-com:mapping-schema">   <xsd:annotation>     <xsd:appinfo>       <sql:relationship name="relOrdersDetails"                          parent="Orders"                         parent-key="OrderID"                         child="[Order Details]"                         child-key="OrderID" />     </xsd:appinfo>   </xsd:annotation>   <xsd:element name="Orders">     <xsd:complexType>       <xsd:sequence>         <xsd:element name="OrderID" type="xsd:int" />         <xsd:element name="CustomerID" type="xsd:string" />         <xsd:element name="OrderDate" type="xsd:dateTime"                      sql:datatype="datetime" />         <xsd:element name="Order_x0020_Details"                      sql:relation="[Order Details]"                      sql:relationship="relOrdersDetails">           <xsd:complexType>             <xsd:sequence>               <xsd:element name="ProductID" type="xsd:int" />               <xsd:element name="Quantity" type="xsd:int" />               <xsd:element name="UnitPrice" type="xsd:decimal" />             </xsd:sequence>           </xsd:complexType>         </xsd:element>       </xsd:sequence>     </xsd:complexType>   </xsd:element> </xsd:schema>

The schema has entries that reference tables and columns, that relate data from two tables (sql:relationship), and that help the SQL XML .NET Data Provider understand the SQL data type for a column (sql:datatype). However, this schema demonstrates just a fraction of the features that you can use in an XML schema file with the SQL XML .NET Data Provider. For more information on all of the features, see the "Using Annotations in XSD Schemas" topic in the documentation for SQL XML 3.

Once you've created your schema file, you set the SchemaPath property of your SqlXmlCommand object to the file that contains this schema information. The following code snippet executes the XPath query described earlier to retrieve the orders and line items for a particular customer using this schema. The code then stores the results of the query in an XmlDocument object.

Visual Basic .NET

'Add the following lines of code at the beginning of the code module. Imports Microsoft.Data.SqlXml Imports System.Xml Dim strPathToResults As String = "C:\MyResults.XML" Dim strPathToSchema As String = "C:\MySchema.XSD" Dim strConn As String = "Provider=SQLOLEDB;" & _                         "Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;" & _                         "Trusted_Connection=Yes;" Dim cmd As New SqlXmlCommand(strConn) cmd.SchemaPath = strPathToSchema cmd.CommandText = "Orders[CustomerID='GROSR']" cmd.CommandType = SqlXmlCommandType.XPath Dim rdr As XmlReader = cmd.ExecuteXmlReader() Dim xmlDoc As New XmlDocument() xmlDoc.Load(rdr) rdr.Close() xmlDoc.Save(strPathToResults) ShowXmlInIE(strPathToResults)

Visual C# .NET

//Add the following lines of code at the beginning of the code module. using Microsoft.Data.SqlXml; using System.Xml; string strPathToResults = "C:\\MyResults.XML"; string strPathToSchema = "C:\\MySchema.XSD"; string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; SqlXmlCommand cmd = new SqlXmlCommand(strConn); cmd.SchemaPath = strPathToSchema; cmd.CommandText = "Orders[CustomerID='GROSR']"; cmd.CommandType = SqlXmlCommandType.XPath; XmlReader rdr = cmd.ExecuteXmlReader(); XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load(rdr); rdr.Close(); xmlDoc.Save(strPathToResults); ShowXmlInIE(strPathToResults);

Applying an XSLT Transform

Earlier in the chapter, I said that you could format an XML document in more than one way and that it's possible for two XML documents to contain the same data but differ only in their schema. You can use a companion technology called XSLT to transform the structure of your XML documents.

XSLT stands for Extensible Stylesheet Language Transformations. You can think of an XSLT transform as an XML document that contains a set of instructions describing how to transform the contents of another XML document. XLST transforms are handy if you want to change the structure of your document. You can also use an XSLT transform to translate XML into HTML.

If you have an XSLT transform and you want to apply it to the results of your SQL XML query, you can set the XslPath property of the SqlXmlCommand object to a string that contains the path to your XSLT transform.

We'll touch on this feature again shortly.

Submitting Updates

The SQL XML .NET Data Provider lets you submit updates to your database. The SqlXmlAdapter object has an Update method that you can use to submit the changes stored in your DataSet to your database. If you've read Chapter 10, you're probably not surprised to see an Update method on a DataAdapter.

However, the SqlXmlAdapter doesn't handle updating in the same way that other DataAdapter objects do. Most DataAdapter objects (such as the OleDbDataAdapter, the SqlDataAdapter, and the OdbcDataAdapter) expose properties that contain Command objects that contain the logic necessary to submit changes to your database. These Command objects generally contain a number of parameters that are bound to columns in the DataTable. When you call the Update method on most DataAdapter objects, the DataAdapter looks at the rows in a particular DataTable. Each time the DataAdapter discovers a modified row, it uses the appropriate Command object to submit the pending change before calling the DataRow object's AcceptChanges method.

The SqlXmlAdapter takes a different approach. Earlier in the chapter, you learned a little about diffgrams. Figure 12-5 shows the contents of a diffgram. Rather than locating pending changes in a DataSet by looping through DataRow objects one row at a time, the SqlXmlAdapter processes the pending changes in a DataSet by generating a diffgram for the DataSet. The SQL XML .NET Data Provider then processes the entire diffgram, creating a complex batch query to submit all the changes to your database at once.

If you look at the contents of the diffgram in Figure 12-5, you can probably figure out how to generate a series of INSERT, UPDATE, and DELETE queries to submit the pending changes to your database. The SQL XML .NET Data Provider cannot generate those queries without a little help.

Remember the annotated XML schema file that we used to help the SQL XML .NET Data Provider translate an XPath query into a SQL query? Different challenge, same solution. When we were working with the XPath query, we set the SchemaPath property of the SqlXmlCommand to the path to our schema file. You can use the SqlXmlAdapter to submit changes to your database by making sure that the SqlXmlAdapter object's SqlXmlCommand object has a schema file that contains all the necessary table and column information for the data in the diffgram.

In fact, you can submit the update by using a SqlXmlCommand whose CommandText property is set to DiffGram. You simply use the DataSet object's WriteXml method to create your diffgram. Then you set up a SqlXmlCommand to use that diffgram and a schema file and...voil ! The following code snippet demonstrates this functionality:

Visual Basic .NET

'Add the following lines of code at the beginning of the code module. Imports Microsoft.Data.SqlXml Imports System.Xml Imports System.IO Dim strConn As String = "Provider=SQLOLEDB;" & _                         "Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;" & _                         "Trusted_Connection=Yes;" Dim cmd As New SqlXmlCommand(strConn) Dim strPathToSchema As String = "C:\MySchema.XSD" cmd.SchemaPath = strPathToSchema cmd.CommandText = "Orders[CustomerID='GROSR']" cmd.CommandType = SqlXmlCommandType.XPath cmd.RootTag = "ROOT" Dim da As New SqlXmlAdapter(cmd) Dim ds As New DataSet() da.Fill(ds) ds.Tables("Orders").Rows(0)("CustomerID") = "ALFKI" ds.Tables("Orders").Rows(1)("CustomerID") = "ALFKI" Dim strPathToDiffGram As String = "C:\MyDiffGram.XML" ds.WriteXml(strPathToDiffGram, XmlWriteMode.DiffGram) cmd = New SqlXmlCommand(strConn) cmd.SchemaPath = strPathToSchema cmd.CommandType = SqlXmlCommandType.DiffGram cmd.CommandStream = New FileStream(strPathToDiffGram, _                                    FileMode.Open, FileAccess.Read) cmd.ExecuteNonQuery() 'Undo the changes. Dim strSQL As String = "UPDATE Orders SET CustomerID = 'GROSR' " & _                        "WHERE OrderID = 10268 OR OrderID = 10785" cmd = New SqlXmlCommand(strConn) cmd.CommandText = strSQL cmd.CommandType = SqlXmlCommandType.Sql cmd.ExecuteNonQuery()

Visual C# .NET

//Add the following lines of code at the beginning of the code module. using Microsoft.Data.SqlXml; using System.Xml; using System.IO; string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; SqlXmlCommand cmd = new SqlXmlCommand(strConn); string strPathToSchema = "C:\\MySchema.XSD"; cmd.SchemaPath = strPathToSchema; cmd.CommandText = "Orders[CustomerID='GROSR']"; cmd.CommandType = SqlXmlCommandType.XPath; cmd.RootTag = "ROOT"; SqlXmlAdapter da = new SqlXmlAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); ds.Tables["Orders"].Rows[0]["CustomerID"] = "ALFKI"; ds.Tables["Orders"].Rows[1]["CustomerID"] = "ALFKI"; string strPathToDiffGram = "C:\\MyDiffGram.XML"; ds.WriteXml(strPathToDiffGram, XmlWriteMode.DiffGram); cmd = new SqlXmlCommand(strConn); cmd.SchemaPath = strPathToSchema; cmd.CommandType = SqlXmlCommandType.DiffGram; cmd.CommandStream = new FileStream(strPathToDiffGram, FileMode.Open,                                     FileAccess.Read); cmd.ExecuteNonQuery(); //Undo the changes. string strSQL  = "UPDATE Orders SET CustomerID = 'GROSR' " +                  "WHERE OrderID = 10268 OR OrderID = 10785"; cmd = new SqlXmlCommand(strConn); cmd.CommandText = strSQL; cmd.CommandType = SqlXmlCommandType.Sql; cmd.ExecuteNonQuery();

	The code also executes an action query to undo the changes to the database. This ensures that you can run the code snippet multiple times. To verify that the SqlXmlCommand submitted the changes stored in the diffgram, you can set a breakpoint so that you can pause the execution of the code before the execution of this last query.

The SqlXmlCommand Object's Updating Logic

Before we move on, I'd like to show you some of the logic that the SqlXmlCommand object generates to submit changes to your database. This information might help you better understand the benefits and drawbacks of using the SQL XML .NET Data Provider to submit changes.

When the previous code snippet called the SqlXmlCommand object's ExecuteNonQuery method to submit the changes stored in the diffgram, the SQL XML .NET Data Provider generated and submitted the following batch query to SQL Server:

SET XACT_ABORT ON BEGIN TRAN DECLARE @eip INT, @r__ int, @e__ int SET @eip = 0 UPDATE Orders SET CustomerID=N'ALFKI' WHERE  ( OrderID=10268 )  AND   ( CustomerID=N'GROSR' )  AND  ( OrderDate=N'1996-07-30 00:00:00' ) ; SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT  IF (@e__ != 0 OR @r__ != 1) SET @eip = 1  IF (@r__ > 1) RAISERROR ( N'SQLOLEDB Error Description: Ambiguous update,                   unique identifier required  Transaction aborted ', 16, 1)  ELSE IF (@r__ < 1) RAISERROR ( N'SQLOLEDB Error Description: Empty update,                   no updatable rows found  Transaction aborted ', 16, 1) UPDATE Orders SET CustomerID=N'ALFKI' WHERE  ( OrderID=10785 )  AND ( CustomerID=N'GROSR' )  AND  ( OrderDate=N'1997-12-18 00:00:00' ) ; SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT  IF (@e__ != 0 OR @r__ != 1) SET @eip = 1  IF (@r__ > 1) RAISERROR ( N'SQLOLEDB Error Description: Ambiguous update,                   unique identifier required  Transaction aborted ', 16, 1)  ELSE IF (@r__ < 1) RAISERROR ( N'SQLOLEDB Error Description: Empty update,                   no updatable rows found  Transaction aborted ', 16, 1) IF (@eip != 0) ROLLBACK ELSE COMMIT SET XACT_ABORT OFF

The batch query starts off by telling SQL Server to abort the current transaction if it raises an error, starts a transaction, and defines some variables to store data. Once this preparation work is done, the code executes the first UPDATE query and then pulls data into variables to determine whether an error occurred and how many rows the query affected. If the query affected one row and did not generate an error, the code continues, issuing action queries and determining whether the updates succeeded. After issuing all the action queries, the code commits the work in the transaction if all the updates succeeded and turns off the setting that tells SQL Server to abort transactions in case of an error.

This is impressive and well-designed code. It's a large and complex batch of queries, but this query minimizes the number of round-trips required to submit changes and determine the success or failure of those changes. If you're looking to provide similar functionality in your application by generating your own queries, this is a great example to reference.

Although it's not important that you understand the individual queries in this batch, you must understand the following points if you're going to rely on this provider to submit changes:

• The SQL XML .NET Data Provider wraps this batch of updates in a transaction and will roll back the transaction if it detects that an error occurred or if any individual query reports that it did not update only one row. This means that you'll submit either all of the updates or none of them.

• The SQL XML .NET Data Provider does not retrieve data from your database while submitting changes. You will not see any new autoincrement or timestamp values after you submit your updates.

• If you submit changes to your database using the SqlXmlAdapter object's Update method, the SqlXmlAdapter will call your DataSet object's AcceptChanges method after successfully submitting your changes. The SqlXmlAdapter does not call the AcceptChanges method on only the DataTable objects that appear in the schema file.