Programming SQLXML from .NET and COM - SQL Server 2008

SQLXML 4.0 provides an object model that allows you to program its capabilities from both managed and unmanaged code. For unmanaged code such as ADO, you use the SQLXMLOLEDB driver. This provider uses the new SQL Server Native Client. For managed code, you can add a reference to Microsoft.Data.SqlXml in Visual Studio, as shown in Figure.

Adding a reference to SQLXML

Adding a reference to SQLXML

The SQLXML-managed assembly has four classes: SqlXmlCommand, SqlXmlParameter, SqlXmlAdapter,and SqlXmlException. Using these classes, you can send commands to SQL Server and process the results on the client side, such as rendering FOR XML statements or executing XML templates. First, we’ll look at each of the classes, and then we’ll show some examples.

SQLXML Classes

SqlXmlCommand is one of the primary classes you’ll interact with when using SQLXML functionality.
Tables list all the methods and properties for SqlXmlCommand.

SqlXmlCommand Methods

SqlXmlCommand Methods
SqlXmlCommand Properties

SqlXmlCommand Properties
SqlXmlParameter provides the ability to pass parameters to your code. This class is very straightforwardsince it has only two properties: Name and Value. You specify the name of the parameter such as customerid and the value to be the value for the parameter. You can create a SqlXmlParameter object by calling the CreateParameter method on the SqlXmlCommand object.

The SqlXmlAdapter object allows interoperability between .NET datasets and SQLXML functionality. The constructor for this object has three forms:

  • The first form can take a SqlXmlCommand that is populated with the necessary information to connect to your datasource.
  • The second form is the command text as a string, the command type as a SqlXmlCommand object, and finally the connection string as a string.
  • The third form is the same as the second one, except you pass in a Stream object rather than a string for the command text.

Once you’ve created your adapter, there are only two methods on the object. The first is the Fill method, to which you pass an ADO.NET dataset. SQLXML will fill the ADO.NET dataset with whatever data your commands should return. Then you can modify your dataset using standard dataset functionality and call the second method, which is Update, with your dataset as a parameter to the method. SQLXML uses an optimistic locking scheme when updating your data in your backend table.

The SqlXmlException object inherits from the standard SystemException object and allows you to pass back SQLXML exceptions as part of your code. There is an ErrorStream property that you use to return the error. The following code uses this property to print out any errors caught in an exception:

Catch ex As Microsoft.Data.SqlXml.SqlXmlException
ex.ErrorStream.Position = 0
Dim oSR As New System.IO.StreamReader(ex.ErrorStream)
Dim strResult As String = oSR.ReadToEnd()
End Try

SQLXML Coding Examples

To show you how to use this functionality, let’s take a look at a sample application. The sample application allows you to bulk-load XML data into SQL Server and then try out the different functionalitydiscussed in this chapter such as FOR XML, dataset integration, running templates, using updategrams, and also using client-side processing and XMLTextReaders. The user interface for the sample is shown in Figure.

Sample application user interface

Sample application user interface

The sample already has a reference to SQLXML, so we don’t need to perform that step again. To start working with our data, we need to load our XML data into our database and shred it into relational columns. The code could have used an OPENXML statement, but instead it uses XML BulkLoad. To start using BulkLoad, we first need to add a reference to the BulkLoad COM object in Visual Studio.

The component name is Microsoft SQLXML BulkLoad 4.0 Type Library. Next, we need to create a BulkLoad object in our application. The code that follows performs this task:

Dim oXMLBulkLoad As New SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class()

Next, we need to set some properties for our BulkLoad. Since we cannot assume that the table that we are going to bulk-load into already exists, the sample sets the SchemaGen property to true. Also, if the tables do exist, we want to drop them, so the sample sets the SGDropTables to true as well. The sample sets other properties such as where to put the error file, whether our XML is a fragment, and whether to keep identities. The most important property is the ConnectionString property, since it tells BulkLoad how to connect to the server. Once we have set all of our properties, the sample calls the Execute method of BulkLoad and passes in the schema mapping file and the XML to bulk-load. You’ll find all of the schema mapping files and the sample XML files included with the sample application. All this code is shown here:

oXMLBulkLoad.ErrorLogFile = "c:myerrors.log"
oXMLBulkLoad.SchemaGen = True
oXMLBulkLoad.KeepIdentity = False
oXMLBulkLoad.BulkLoad = True
oXMLBulkLoad.SGDropTables = True
oXMLBulkLoad.XMLFragment = True
oXMLBulkLoad.ConnectionString = strConnectionString
oXMLBulkLoad.Execute(txtXMLSchema.Text, txtXMLFile.Text)

FOR XML: Server-Side and Client-Side Processing

Once we’ve successfully bulk-loaded our data, we can start working with it. One thing we can do is get our data back out as XML, now that it is shredded into the database. We can use the FOR XML construct to do this. Remember that SQLXML allows you to render your XML on the server or on the client. The example allows us to select either one. The code uses a common method for executing all queries in the example. This method takes a number of different parameters such as whether the query coming in is a SQL, template, or diffgram query. (A diffgram is an XML format that is used to identify current and original versions of data elements.) The first thing the common query method does is create a SqlXmlCommand object as shown here. Note the connection string is a standard connection string, such as "Provider=sqloledb;server=localhost;database=pubs;integrated security=SSPI".

Dim oSQLXMLCommand As New _

Next, it sets the command type to be the appropriate type based on the query coming in. For standard SQL queries, the command type is set to SQL, as shown here:

oSQLXMLCommand.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.Sql

To send our FOR XML query to the server, we need to set the command text for our SqlXmlCommandobject. Since we pass the query to the method, we use the strQuery variable for this purpose.

'Set our Query
oSQLXMLCommand.CommandText = strQuery

Since we can render our FOR XML on the server or client, we need to set the ClientSideXml property of our command object to true or false, with true being to render the XML on the client side. Once we’ve set this property, we can execute our query and retrieve the results. The following code uses a StreamReader to get the results and put them in our results text box. We can also use an XMLTextReader, which you’ll see used later in this section.

'See if we need to render client-side
If bUseClientSide = True Then
oSQLXMLCommand.ClientSideXml = True
End If
Dim oStream As System.IO.Stream
oStream = oSQLXMLCommand.ExecuteStream()
oStream.Position = 0
Dim oStreamReader As New System.IO.StreamReader(oStream)
txtResults.Text = oStreamReader.ReadToEnd()

As you can see from the code, using FOR XML in SQLXML is very straightforward. The hard part is making sure that you get your FOR XML query correct and returning the correct results.

Using an XMLTextReader

There may be times when you don’t want to use a StreamReader to get your results back from your SQLXML queries, but instead want to use an XMLTextReader. The XMLTextReader gives you fast access to XML data and more flexibility in navigating your XML than a StreamReader does. The XMLTextReader parses your XML and allows you to query that XML using XPath. To use an XMLTextReader, you just need to change your ExecuteStream method call to an Execute XML Reader method call on your SqlXmlCommand object. Once you get back the reader, you can use the methods and properties of the XML reader to navigate your XML. The following code executes the XML reader and displays the results to the user in the sample:

'Use XMLTextReader
Dim oXMLTextReader As System.Xml.XmlTextReader
oXMLTextReader = oSQLXMLCommand.ExecuteXmlReader()
Dim strXML As String = ""
While oXMLTextReader.Read()
'We're on an element
If oXMLTextReader.NodeType = XmlNodeType.Element Then
strXML += "<" & oXMLTextReader.Name & ""
ElseIf oXMLTextReader.NodeType = XmlNodeType.EndElement Then
strXML += "</" & oXMLTextReader.Name & ">"
End If
'Look for attributes
If oXMLTextReader.HasAttributes() Then
Dim i As Integer = 0
Do While (oXMLTextReader.MoveToNextAttribute())
i += 1
strXML += " " & oXMLTextReader.Name & "=" & oXMLTextReader.Value
If oXMLTextReader.AttributeCount = i Then
'Last attribute, end the tag
strXML += " />"
End If
End If
End While
txtResults.Text = strXML

As you can see, the XML reader, for simple operations like just displaying the XML, is overkill since you must parse the XML to display it. But, if you wanted to figure out information about the XML, such as the number of attributes or elements, or if you wanted to navigate in a richer way, the XML reader is up for the task.

Using Parameters with SQLXML

To use parameters with SQLXML, we need to create a SqlXmlParameter object. Our query must specify that we are going to pass a parameter, and the SqlXmlParameter object must have its properties set correctly. The following code snippets show you how to use a parameter with your SQLXML queries:

strQuery = "SELECT * FROM " & strTable & " WHERE city = ? FOR XML AUTO, ELEMENTS"
. . .
Dim oSQLXMLParameter As Microsoft.Data.SqlXml.SqlXmlParameter
oSQLXMLParameter = oSQLXMLCommand.CreateParameter()
oSQLXMLParameter.Name = "city"
oSQLXMLParameter.Value = "Oakland"

Executing XPath or SQL Queries with Templates

With SQLXML, you can execute XPath or SQL queries. The sample application uses a template to execute a SQL query and a straight XPath statement for the XPath query. The sample could have used a template for the XPath query, but the sample demonstrates how to use the XPath command type. The following code sets up the SQL template query:

'Load up our query
strQuery = "<Root><sql:query xmlns:sql=""urn:schemas-microsoft-com:xml-sql""> _
SELECT * FROM AuthorsXMLNew FOR XML AUTO</sql:query></Root>"

Next, the sample sets the command type to be a template in order to run the SQL template query. The sample also specifies the root node and the path to the annotated XSD schema file.

oSQLXMLCommand.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.Template
oSQLXMLCommand.SchemaPath = txtXMLSchema.Text
oSQLXMLCommand.RootTag = "ROOT"

The code uses a StreamReader to render the results. That code won’t be shown here, since you’ve seen it already.

To perform the XPath query, again, we set up the query as shown here:

'Load up our query
strQuery = "/AuthorsXMLNew[city='Oakland']"

Since we are using an XPath query directly, we need to set the command type to be XPath for our SqlXmlCommand object. Just as we did for the SQL template query, we want to set our root node and also the path to our annotated XSD schema. After that, we’ll again use the StreamReader to render our results.

oSQLXMLCommand.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.XPath
oSQLXMLCommand.SchemaPath = txtXMLSchema.Text
oSQLXMLCommand.RootTag = "ROOT"

Interoperating with the ADO.NET Dataset

SQLXML interoperates with the ADO.NET dataset through the SqlXmlAdapter object. You can use the SqlXmlAdapter to fill your dataset. Then you can use the DataSet object as you normally would in ADO.NET. The following code, taken from the sample application, creates a query, executes that query using the SqlXmlAdapter object, and then fills a dataset with the information. To write out the value returned back, the code uses some stream objects.

strQuery = "SELECT * FROM " & strTable & " WHERE city =
Dim oSQLXMLDataAdapter As New _
Dim oDS As New System.Data.DataSet()
'Display the underlying XML
Dim oMemStream As New System.IO.MemoryStream()
Dim oStreamWriter As New System.IO.StreamWriter(oMemStream)
oDS.WriteXml(oMemStream, System.Data.XmlWriteMode.IgnoreSchema)
oMemStream.Position = 0
Dim oStreamReader As New System.IO.StreamReader(oMemStream)
txtResults.Text = oStreamReader.ReadToEnd()

Programming Updategrams

The final piece of the sample application we’ll look at uses updategrams. Updategrams allow you to update your SQL Server using your existing XML documents. The code creates the updategram using a StringBuilder object. Then the code sets the command type to be UpdateGram. Finally, the rest of the code is the same as the original code to execute the command and get the results, so that section is not repeated here.

Dim strUpdateGram As New System.Text.StringBuilder()
strUpdateGram.Append("<?xml version='1.0'?><AuthorsXMLNewupdate ")
strUpdateGram.Append("xmlns:updg='urn:schemas- microsoft-com:xml-updategram'>")
strUpdateGram.Append("<updg:sync updg:nullvalue='nothing'>" &
strUpdateGram.Append("<updg:after><AuthorsXMLNew au_id='123-22-1232'")
strUpdateGram.Append(" au_fname='Tom' state='WA' phone='425-882-8080'/>")
strUpdateGram.Append(" au_id='267-41-2394'/></updg:before>")
strUpdateGram.Append(" au_id='238-95-7766'/></updg:before>")
strUpdateGram.Append(" city='Oakland' phone='212-555-1212'/>")
strQuery = strUpdateGram.ToString()
oSQLXMLCommand.CommandType =

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics