Reading XML Data Direct from SQL Server

Our earlier discussion of the format of the XML returned from a DataSet mentioned an alternative format of using the attributes of a row element to hold the individual column or field data items. This was, in fact, the default format introduced with ADO 2.0/2.1 when XML support was added to ADO. It's also the default format for the XML created by the new features of Microsoft SQL Server 2000 (and the SQLXML Technology Preview that is still available for SQL Server 7.0).

The Reading XML Direct From SQL Server With An XmlReader ( xmldatareader-sql.aspx ) example page demonstrates this feature. To use it you must have SQL Server 2000 installed “ the SQLXML Technology Preview does not work with .NET.

When you run the page, as shown in Figure 11-15, you see the connection string and the SQL SELECT statement used. Notice that it includes the FOR XML AUTO instruction. Below this is a series of XML elements that are created automatically by SQL Server in response to the instruction in the SQL statement:

Figure 11-15:

It's hard to see what the structure of the XML is from the screenshot, but if you break out one of the elements, you will see the structure quite clearly:

  <BookList ISBN="1861003110" Title="Professional XML"   PublicationDate="2000-01-01T00:00:00"/>  

There are a couple of things to note. This is not an XML document, as there is no document type declaration or root element. All you get back is a series of elements that contain the row values as attributes. This is the default format for the AUTO part of the instruction used in the SQL statement. You would probably use the elements to build up your own specific XML documents, depending on the task your application has to achieve.

Note

The XML technology built into SQL Server 2000 is very powerful, allowing updates to be made to the source data as well as extracting it. The Help topic "Retrieving and Writing XML Data" within the "XML and Internet Support" section of SQL Server Books Online contains complete details of the various formats and options that are available when using this technology.

The Code for the SQLXML Example Page

The ability to extract data as XML from SQL Server using the SQLXML feature has proved very useful to developers already, and it's extremely fast and efficient. To support it in .NET simply entailed including an option to return an object that could hold XML document fragments .

The answer is a special version of the execute methods available in the Command object used for relational data access, but which returns an XmlReader object instead of a DataReader object. This method is called, not surprisingly, ExecuteXmlReader .

The code used in the example page demonstrates this. First we collect the connection string from the web.config file, and then create the SQL statement that will extract the XML. Then we create a StringBuilder object to hold the large strings that we expect to get back from the database. We also create the customary Connection and Command objects.

Notice that the objects from the System.Data.SqlClient namespace are being used here (prefixed Sql ). This page is only going to work with SQL Server 2000 anyway, and so we might as well take advantage of the performance boost that comes with the SQL TDS provider:

  Dim strConnect As String   strConnect = ConfigurationSettings.AppSettings("DsnWroxBooksSql")   Dim strSelect As String   strSelect = "SELECT * FROM BookList WHERE " _   & "ISBN LIKE '1861003%' FOR XML AUTO"     'create a new StringBuilder to hold the results   Dim objStrBuilder As New StringBuilder()     'create a new Connection object using the connection string   Dim objConnect As New SqlConnection(strConnect)     'create new Command using the connection object and select statement   Dim objCommand As New SqlCommand(strSelect, objConnect)  

Executing the Command

We need an object to receive the results of executing the SQL statement. The following code shows how to declare a variable to hold an XmlTextReader object (a public class based on XmlReader ) for this. You can then open the connection and call the ExecuteXmlReader method. It returns the XmlTextReader all ready to go:

  'declare a variable to hold an XmlTextReader object   Dim objXTReader As XmlTextReader     'open the connection to the database   objConnect.Open()     'execute the SQL statement against the command to create the XmlReader   objXTReader = objCommand.ExecuteXmlReader()  

Retrieving the XML Result

To retrieve the data once the SQL statement is executed, we call the ReadString method of the XmlTextReader to initialize it. Then we call the GetRemainder method to read to the end of the results, and append it all to the StringBuilder created earlier. To finish off, the reader and the connection are closed, and the results are displayed:

  'read the first result to initialize the reader   objXTReader.ReadString()     'and then read remainder into the StringBuilder as well   objStrBuilder.Append(objXTReader.GetRemainder().ReadToEnd())     'close the XmlReader and Connection   objXTReader.Close()   objConnect.Close()     'display the results as Text to show XML elements   outError.InnerText = objStrBuilder.ToString()