Using the SQLXML and SQL XML .NET Data Providers

Chapter 6 discussed the major XML .NET-related classes found in the System.Xml namespace to read and write XML documents. We also discussed XML integration with ADO.NET. The .NET Framework Library also provides an XML .NET data provider to work with SQL Server 2000 and XML documents.

The .NET support was introduced in the SQLXML 2.0 library, which includes two data providers that can be used to read XML data and write XML from and into SQL Server 2000 databases. These two data providers are the following:

• The SQLXMLOLEDB provider, which is an OLE DB data provider that exposes SQLXML 2.0 functionality through ADO

• The SQLXML managed classes (the SQL XML .NET data provider) expose SQLXML 2.0 functionality through ADO.NET

Note

Currently, SQLXML 3.0 is available for download with Service Pack 1. This chapter uses SQLXML 3.0, which by default copies to the C:\\Program Files\SQLXML 3.0\Bin folder.

In this chapter, we concentrate only on the SQL XML .NET data provider.

Note

To get the latest updates and downloads for SQLXML, visit http://msdn.microsoft.com/sqlxml/.

The SXDP is an add-on; you can download and install it from Microsoft's site (http://msdn.microsoft.com/sqlxml/). Installing SXDP copies the Microsoft.Data.SqlXml.dll library, which you should add to an application before you start using it in an application. In VS .NET, you can add a reference to SXDP by using the Add Reference menu option and selecting the Browse button. The default path of SXDP is the C:\\Program Files\SQLXML 3.0\Bin folder (see Figure 12-2).

Figure 12-2: Adding a reference to Microsoft.Data.SqlXml.dll

Adding a reference to this library adds the Microsoft.Data.SqlXml namespace to the project. Obviously you include this namespace in your application to access the classes provided by the SXDP:

 imports Microsoft.Data.SqlXml 

Unlike other data providers, the SQLXMLDP model consists of only three classes: SqlXmlCommand, SqlXmlParameter, and SqlXmlAdapter.

Using the FOR XML Keyword

SQL Server 2000 provides a FOR XML keyword that you can use with the SELECT statement to read data from a SQL Server to XML format. Using this statement, you can generate XML in four formats: RAW, AUTO, NESTED, and EXPLICIT.

The syntax of FOR XML looks like the following:

 FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64] 

where mode specifies the XML mode. It can be RAW, AUTO, or EXPLICIT. XMLDATA specifies that an XML-Data schema should be returned. If ELEMENTS is specified, it produces columns as subelements; otherwise columns are mapped as XML attributes. This option is available for AUTO mode only. The choice of which to use is yours, as both options produce valid XML; however, using subelements is more like XML and may be especially useful if you need to share data across systems that may not have XML parsers that understand attributes. The BINARY BASE64 option specifies that any binary data retuned by the query will be in 64-bit encoded format.

The following examples use this syntax.

Using the SqlXmlAdapter Object

The SqlXmlAdapter object represents a partial DataAdapter. We said partial because unlike other data providers' DataAdapter object, it only provides two methods: Fill and Update.

You can create a SqlXmlAdapter from an XML document with arguments of a connection string and command type. These are two overloaded forms of the SqlXmlAdapter constructor:

 Dim adapter As New SqlXmlAdapter(commandText, _  SqlXmlCommandType, connectionString) Dim adapter As New SqlXmlAdapter(commandStream, _  SqlXmlCommandType, connectionString) 

The connection string for a SqlXmlAdapter is a standard OleDb connection string; however, the provider must be SQLOLEDB, the SQL Server provider. You can also create a SqlXmlAdapter from a SqlXmlCommand:

 Dim adapter As New SqlXmlAdapter(SqlXmlCommand) 

You can also specify the command string and command type later using the CommandText and CommandType properties. You can use the SchemaPath property to read an XML document.

 cmd.CommandText = "EmployeeElement" cmd.CommandType = SqlXmlCommandType.XPath cmd.SchemaPath = "xmlDoc.xml" 

The Fill method takes a DataSet as an argument and fills it with the data specified in the SELECT command, and the Update method sends a DataSet to save the changes to a data source.

Listing 12-8 uses a SqlXmlAdapter to fill a DataSet and view data in a DataGrid control. The code also uses the Update method to add a new row to the database.

Listing 12-8: Using SqlXmlAdapter

 Private Sub FillDataBtn_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles FillDataBtn.Click     Dim connectionString As String = _     "provider=sqloledb;server=mcb;database=Northwind;uid=sa;password=;"     Dim row As DataRow     Dim adapter As SqlXmlAdapter     Dim cmd As SqlXmlCommand = New SqlXmlCommand(connectionString)     cmd.RootTag = "ROOT"     cmd.CommandText = "EmployeeElement"     cmd.CommandType = SqlXmlCommandType.XPath     cmd.SchemaPath = "xmlDoc.xml"     Dim ds As DataSet = New DataSet()     adapter = New SqlXmlAdapter(cmd)     adapter.Fill(ds)     row = ds.Tables(0).Rows(0)     row("FName") = "New FName"     row("LName") = "New LName"     adapter.Update(ds)     DataGrid1.DataSource = ds.Tables(0)   End Sub 

Using the SqlXmlParameter Object

The SqlXmlParameter represents a parameter, which you can use in a SqlXmlCommand. The SqlXmlParameter object provides only two properties: Name and Value. The Name property represents the name of the parameter. The Value property represents the value of the parameter.

The CreateParameter method of the SqlXmlCommand object creates the parameter object. The following code creates a parameter:

 Dim param As SqlXmlParameter param = cmd.CreateParameter() param.Name = "LastName" param.Value = "Fuller" 

Using the SqlXmlCommand Object

The SqlXmlCommand object represents the Command object of SXDP. It is usually used to execute SQL statements. The SqlXmlCommand constructor takes a connection string as its argument:

 public SqlXmlCommand(string connectionString) 

where connectionString is the ADO or OLEDB connection string identifying the server, database, and the login information. A typical connection string looks like following:

 Dim connectionString As String = "Provider=SQLOLEDB; Server=(local); " & _ "database=Northwind; user id=UserLogin; password=UserPassword" 

Using the SqlXmlCommand Methods

The ExecuteNonQuery method executes SQL statements that don't return any values such as INSERT, UPDATE, and DELETE statements. You can also use this method to execute an updategram or a DiffGram that updates records but returns nothing. A DiffGram is an XML format that identifies current and original versions of data elements. See Chapter 22 for more details.

Note

What are updategrams? Updategrams are a feature of SQL Server 2000 that allow SQL Server database updates to be defined in XML format. The updategrams format contains the XML nodes for each column of a database table.

You can use the ExecuteStream and ExecuteToStream methods to write to streams. The ExecuteStream method returns a new Stream object, and the ExecuteToStream method writes the query results to an existing stream.

The ExecuteXmlReader method executes a query and returns results in an XmlReader object.

Note

To use System.Xml in your application, you need to add a reference to the System.Xml namespace.

The CreateParameter method creates a parameter for SXDP, which can be used by SqlXmlCommand. You can set values for the Name and Value parameters of this object. The ClearParameter method removes all parameters associated with a Command object.

Using the SqlXmlCommand Properties

The SqlXmlCommand object supports many properties, which are defined in Table 12-2.

Table 12-2: The SqlXmlCommand Properties

ClientSideXml	When set to True, conversion of the rowset to XML occurs on the client instead of on the server. This is useful when you want to move the performance load to the middle tier. This property also allows you to wrap the existing stored procedures with FOR XML to get XML output.
SchemaPath	This is the name of the mapping schema along with the directory path (for example, C:\x\y\MySchema.xml). This property specifies a mapping schema for XPath queries. The path specified can be absolute or relative. If the path specified is relative, the base path specified in BasePath resolves the relative path. If the base path isn't specified, the relative path is relative to the current directory.
XslPath	This is the name of the XSL file along with the directory path. The path specified can be absolute or relative. If the path specified is relative, the base path specified in BasePath resolves the relative path. If the base path isn't specified, the relative path is relative to the current directory.
BasePath	This is the base path (a directory path). The value of this property resolves relative paths specified for the XSL file (using the XslPath property), the mapping schema file (using the SchemaPath property), or an external schema reference in an XML template (specified using mapping-schema attribute).
OutputEncoding	This specifies the requested encoding for the stream returned by the command execution. Some commonly used encodings are UTF-8, ANSI, and Unicode. UTF-8 is the default encoding.
Namespaces	XPath queries can include namespaces. If the schema elements are namespace-qualified (use a target namespace), the XPath queries against the schema must specify the namespace. This member enables the execution of XPath queries that use namespaces.
RootTag	This provides the single root element for the XML generated by the command execution. A valid XML document requires a single root-level tag. If the command executed generates an XML fragment (without a single top-level element), you can optionally specify the root element for the returning XML.
CommandText	This is the text of the command to execute.
CommandStream	This is the command stream to execute. This property is useful if you want to execute a command from a file (for example, an XML template). When using CommandStream, only Template, UpdateGram, and DiffGram CommandType values are supported
CommandType	This identifies the type of command being executed. This property is of type SqlXmlCommandType.

Table 12-3 describes the SqlXmlCommandType property.

Table 12-3: The SqlXmlCommandType Members

COMMANDTYPE	DESCRIPTION
Sql	Executes an SQL command (for example, SELECT * FROM Employees FOR XML AUTO).
XPath	Executes an XPath command (for example, Employees[@EmployeeID=1]).
Template	Executes an XML template.
UpdateGram	Executes an updategram.
DiffGram	Executes a DiffGram.
TemplateFile	Executes a template file. This allows you to execute updategrams and DiffGrams.

Seeing SqlXmlCommand in Action

Listing 12-9 uses different methods and properties of SqlXmlCommand to execute SQL queries.

Listing 12-9: Using SqlXmlCommand to Execute Commands

 Imports System Imports System.IO Imports System.Xml Imports Microsoft.Data.SqlXml Module Module1   Sub Main()     ExecuteStreamMethod()     ExecuteToStreamMethod()     SqlParamExecuteMethod()     ExecuteNonQueryMethod()   End Sub   Public Sub ExecuteStreamMethod()     Dim connectionString As String = _       "provider=sqloledb;server=mcb;database=Northwind;uid=sa;password=;"     Dim sql As String = _     "SELECT FirstName, Title, Address FROM Employees WHERE " & _     "LastName = 'Fuller' FOR XML AUTO"     Dim cmd As SqlXmlCommand = New SqlXmlCommand(connectionString)     cmd.CommandText = sql     Try       Dim st As Stream = cmd.ExecuteStream()       Dim stReader As StreamReader = New StreamReader(st)       Console.WriteLine(stReader.ReadToEnd())     Catch exp As Exception       Console.WriteLine(exp.Message)     End Try   End Sub   Public Sub ExecuteToStreamMethod()     Dim connectionString As String = _      "provider=sqloledb;server=mcb;database=Northwind;uid=sa;password=;"     Dim sql As String = _     "SELECT FirstName, Title, Address FROM Employees WHERE " & _     "LastName = 'Fuller' FOR XML AUTO"     Dim cmd As SqlXmlCommand = New SqlXmlCommand(connectionString)     cmd.CommandText = sql     Try       Dim memStream As MemoryStream = New MemoryStream()       Dim stReader As StreamReader = New StreamReader(memStream)       cmd.ExecuteToStream(memStream)       memStream.Position = 0       Console.WriteLine(stReader.ReadToEnd())     Catch exp As Exception       Console.WriteLine(exp.Message)     End Try   End Sub   Public Sub ExecuteNonQueryMethod()     Dim connectionString As String = _      "provider=sqloledb;server=mcb;database=Northwind;uid=sa;password=;"     Dim sql As String = _     "DELETE Employees WHERE LastName = 'tel'"     Dim cmd As SqlXmlCommand = New SqlXmlCommand(connectionString)     cmd.CommandText = sql     Try       cmd.ExecuteNonQuery()     Catch exp As Exception       Console.WriteLine(exp.Message)     End Try   End Sub   Public Sub SqlParamExecuteMethod()     Dim connectionString As String = _      "provider=sqloledb;server=mcb;database=Northwind;uid=sa;password=;"     Dim sql As String = _     "SELECT FirstName, Title, Address FROM Employees " & _     "FOR XML AUTO"     Dim cmd As SqlXmlCommand = New SqlXmlCommand(connectionString)     cmd.CommandText = sql     Try       Dim param As SqlXmlParameter       param = cmd.CreateParameter()       param.Name = "LastName"       param.Value = "Fuller"       Dim st As Stream = cmd.ExecuteStream()       Dim stReader As StreamReader = New StreamReader(st)       Console.WriteLine(stReader.ReadToEnd())     Catch exp As Exception       Console.WriteLine(exp.Message)     End Try   End Sub End Module 

Caution

You get an exception if you don't use the FOR XML directive in a SQL query and then try to use general SQL queries.