Developer

Create customized XML streams with SQL 2000

You can use SQL 2000 to help create your own customized XML solutions. Here's how.

This article originally appeared as a Web Development Zone e-newsletter.

By Phillip Perkins

You might have an idea of the XML that you'd like to use in your application, yet the ability to grab that information from Microsoft SQL Server 2000 isn't available with a simple query.

Let's say that you have an HTML TABLE that you want to bind to an XML data island, but you've decided that it's complex enough that you want to nest a couple of TABLEs within the parent TABLE. Each TABLE within the parent TABLE is bound to a group of nodes inside a parent node in your XML data structure. A phone directory is a good example of this.

<TABLE dataSrc="#xmlPhoneData">
<TR>
    <TD>
        <SPAN dataFld="fname"></SPAN>&nbsp;
        <SPAN dataFld="lname"></SPAN>
    </TD>
</TR>
<TR>
    <TD>
        <TABLE dataSrc="#xmlPhoneData" dataFld="phone">
            <TR>
                <TD><SPAN dataFld="phone_type"></SPAN>:</TD>
                <TD><SPAN dataFld="phone_no"></SPAN></TD>
            </TR>
        </TABLE>
    </TD>
</TR>
<TR>
    <TD>
        <TABLE dataSrc="#xmlPhoneData" dataFld="other_info">
            <TR>
                <TD><SPAN dataFld="info_type"></SPAN>:</TD>
                <TD><SPAN dataFld="info_data">/SPAN></TD>
            </TR>
        </TABLE>
    </TD>
</TR>
</TABLE>

This TABLE represents a view of information that will display the name of the person in the directory, all their available phone numbers, and other information about the person (i.e., address, etc.).

The XML for this view follows the model:

<root>
    <data>
        <lname/>
        <fname/>
        <phone>
            <phone_type/>
            <phone_no/>
        </phone>
        <other_info>
            <info_type/>
            <info_data/>
        </other_info>
    </data>
</root>

Although the ability to create this XML model isn't impossible, we'll use this simple example for the purpose of this article.

First, we'll offer some background information to make this a little more comprehensible. The data is coming from three different sources: an employee table that contains the first name (fname) and the last name (lname) of the employee; a phone table that contains the phone types (phone_type) and the phone numbers (phone_no) of the employee; and another table, other_info, that contains extra information for the employee (i.e., address, etc.).

If you were to grab the information from these tables into a recordset, the SQL query may look something like this:

SELECT employee.lname, employee.fname, phone.phone_type, phone.phone_no,
other_info.info_type, other_info.info_data FROM employee LEFT JOIN phone ON
 phone.employee_id = employee.employee_id LEFT JOIN other_info ON
 other_info.employee_id = employee.employee_id

When you need XML from SQL 2000, you can usually put a FOR XML AUTO, ELEMENTS statement at the end of the query string, and you'll get the desired XML string. However, JOINs are a little tricky. A single JOIN isn't difficult, but when you introduce more tables, you may end up spending an entire day trying to get the desired XML output.

An alternative is to turn off row counting and stream your data output. You can do this by populating a cursor with your parent data, navigating the records with a fetch, and streaming the XML output by selecting the related records from the other tables with a FOR XML AUTO, ELEMENTS statement.

This functionality is best reserved for a stored procedure since cursors tend to be a bit slow and a stored procedure is precompiled. Here's the stored procedure, Transact SQL, to accomplish this task:

DECLARE @employee_id int
DECLARE @fname varchar(50)
DECLARE @lname varchar(50)
DECLARE phone_cursor CURSOR FOR SELECT employee_id, fname, lname FROM employees
 ORDER BY lname, fname

SET NOCOUNT ON

OPEN phone_cursor

FETCH NEXT FROM phone_cursor INTO @employee_id, @fname, @lname

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT '<data><fname>' + @fname + '</fname><lname>' + @lname +
 '</lname>'

    SELECT phone_type, phone_no FROM phone WHERE employee_id =
 @employee_id FOR XML AUTO, ELEMENTS
    SELECT info_type, info_data FROM other_info WHERE employee_id =
 @employee_id FOR XML AUTO, ELEMENTS

    SELECT '</data>'

    FETCH NEXT FROM phone_cursor INTO @employee_id, @fname, @lname

END

CLOSE phone_cursor
DEALLOCATE phone_cursor

SET NOCOUNT OFF
GO

This bit of SQL creates a cursor for storing the employee data and iterating through. NOCOUNT is set ON to disable the output of row counting after each SELECT statement. As each record in the cursor is navigated, a string is fabricated that contains the first and last name nodes of the XML output. The XML is created for both the phone information and the other information.

Then, this parent node is closed. After successful completion of the cursor navigation, the cursor is closed and deallocated. This produces an XML string that matches our desired XML model.

In order to effectively use this XML output string, the output must be stored in an Active Data Objects (ADO) Stream object. The data can be accessed through the ReadText method on the Stream object:

<XML id="xmlPhoneData" name="xmlPhoneData">
<%
Dim adoConn, adoCmd
Dim adoStream
Set adoConn = Server.CreateObject("ADODB.Connection")
Set adoCmd = Server.CreateObject("ADODB.Command")
adoConn.ConnectionString = "Some Connection String To MS SQL 2K"
adoConn.Open
Set adoCmd.ActiveConnection = adoConn
Set adoStream = Server.CreateObject("ADODB.Stream")
adoCmd.CommandType = 4 'adCmdStoredProc
adoCmd.CommandText = "get_test_phone"
adoStream.Open
adoCmd.Properties("Output Stream") = adoStream
adoCmd.Execute ,,1024 'adExecuteStream

Response.Write adoStream.ReadText(-1)

adoStream.Close
Set adoStream = Nothing
adoConn.Close
Set adoCmd = Nothing
Set adoConn = Nothing
%>
</XML>

This code creates an ADO connection to SQL 2000 and executes a stored procedure. The result is stored in the ADO Stream object (adoStream). That data is written to the Response buffer, the Stream object is closed, and then there's some "house cleaning."

This simple example provides a generic way for you to create your own customized XML solutions. With the ability to bind HTML TABLEs to XML data islands, you can create some intense solutions.

Phillip Perkins is a contractor with Ajilon Consulting. His experience ranges from machine control and client/server to corporate intranet applications.

Editor's Picks

Free Newsletters, In your Inbox