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
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.
SqlXmlCommand is one of the primary classes you’ll interact with when using SQLXML functionality.
Tables list all the methods and properties for SqlXmlCommand.
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:
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:...
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
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"
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
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
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
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"
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
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
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
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
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 =
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()
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.