accessing-sqlxml-functionality-in-the-net-environment.md

title	Access SQLXML functionality in the .NET environment
description	Learn how to use SQLXML Managed Classes to access the .NET Framework environment.
author	MikeRayMSFT
ms.author	mikeray
ms.date	03/14/2017
ms.service	sql
ms.subservice	xml
ms.topic	reference
helpviewer_keywords	Managed Classes [SQLXML], accessing SQLXML functionality SQLXML Managed Classes, accessing SQLXML functionality DiffGrams [SQLXML], accessing SQLXML functionality .NET Framework [SQLXML], accessing SQLXML functionality
monikerRange	=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Accessing SQLXML Functionality in the .NET Environment

[!INCLUDE SQL Server Azure SQL Database] This example shows:

• How to use [!INCLUDEmsCoName] SQLXML Managed Classes (Microsoft.Data.SqlXml) to access Microsoft [!INCLUDEssNoVersion] in the [!INCLUDEmsCoName] .NET Framework environment.

• How DiffGrams that are generated in the .NET Framework environment can apply data updates to [!INCLUDEssNoVersion] tables.

In this application, an XPath query is executed against an XSD schema. The execution of the XPath query returns an XML document that consists of contact data (FirstName, LastName). The application loads the XML document in the dataset in the .NET Framework environment. The data in the dataset is modified: the contact's first name is changed to "Susan" for the first contact in the dataset. The DiffGram is generated from the dataset, and the update that is specified in the DiffGram (the change in the employee's first name) is then applied to the Person.Contact table.

Note

In the code, you must provide the name of the instance of [!INCLUDEssNoVersion] in the connection string.

using System;  
using System.Data;  
using Microsoft.Data.SqlXml;  
using System.IO;  
class Test  
{  
   static string ConnString = "Provider=SQLOLEDB;Server=SqlServerName;database=AdventureWorks;Integrated Security=SSPI;";  
   public static int testParams()  
   {  
      DataRow row;  
      SqlXmlAdapter ad;  
      //need a memory stream to hold diff gram temporarily  
      MemoryStream ms = new MemoryStream();  
      SqlXmlCommand cmd = new SqlXmlCommand(ConnString);  
      cmd.RootTag = "ROOT";  
      cmd.CommandText = "Con";  
      cmd.CommandType = SqlXmlCommandType.XPath;  
      cmd.SchemaPath = "MySchema.xml";  
      //load data set  
      DataSet ds = new DataSet();  
      ad = new SqlXmlAdapter(cmd);  
      ad.Fill(ds);  
      row = ds.Tables["Con"].Rows[0];  
      row["FName"] = "Susan";  
      ad.Update(ds);  
      return 0;  
   }  
   public static int Main(String[] args)  
   {  
      testParams();  
      return 0;  
   }  
}  

To test the example:

To test this example, you must have the [!INCLUDEmsCoName] .NET Framework installed on your computer.

1. Save this XSD schema (MySchema.xml) in a folder:

   <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
               xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
     <xsd:element name="Con" sql:relation="Person.Contact" >  
      <xsd:complexType>  
        <xsd:sequence>  
           <xsd:element name="FName"    
                        sql:field="FirstName"   
                        type="xsd:string" />   
           <xsd:element name="LName"    
                        sql:field="LastName"    
                        type="xsd:string" />  
        </xsd:sequence>  
        <xsd:attribute name="ContactID" type="xsd:integer" />  
       </xsd:complexType>  
     </xsd:element>  
   </xsd:schema>  
   

2. Save the C# code (DocSample.cs) provided in this example in the same folder in which the schema is stored. (If you store the files in a different folder, you will have to edit the code and specify the appropriate directory path for the mapping schema.)

3. Compile the code. To compile the code at the command prompt, use:

   csc /reference:Microsoft.Data.SqlXML.dll DocSample.cs  
   

   This creates an executable (DocSample.exe).

At the command prompt, execute DocSample.exe.