Creating a user-XML stream with SQL 2000

xml| Create

You might want to use XML in your program, but you can't get information from Microsoft SQL Server 2000 with just one simple query.

Let's say you want to connect an HTML table to an XML data island, but you're pretty sure it's complicated to have several tables nested in the master table. Each table in the master table must be a set of nodes under the parent node in the XML data structure. The directory of phone numbers is a good example.

<table datasrc= "#xmlPhoneData" >
<TR>
<TD>
<span datafld= "FName" ></SPAN>
<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>

Table represents a range of information, including the name of a person in the directory, all phone numbers, and other information about that person (for example, address, etc.).

The model for this XML is as follows:

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

Although it is not impossible to create this XML model, we still use this simple example in this article.

First, we will provide some background information to make it more comprehensive. The data comes from three aspects: a staff table containing the employee's name (fname) and surname (LName), a phone table containing the employee phone type (phone_type) and telephone number (PHONE_NO), and other information tables (Other_info) that contain additional information about the employee, such as address.

If you want to crawl information from these tables into a recordset, SQL queries can be as follows:

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 in SQL 2000, you can usually enter a FOR XML AUTO, elements statement at the end of the query string, and you can get the XML string you want. However, multiple nodes can have a small problem. A single node is easy, but when you refer to multiple tables, you may not spend all day trying to get the XML output you want.

Another approach is to turn off row calculations and data output. You can do this by moving a pointer into the master table data, using an instruction to manipulate the record, and then selecting the relevant record from the other table with the FOR XML AUTO and elements statement to stream the XML output.

This feature is best saved to a stored program because the pointer is slower and a stored program is edited in advance. The following is the stored program that completes this task (Transact SQL).

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 SQL creates a pointer to store employee data and repeat execution. The NOCOUNT is set to ON when the output function is computed for the row after each SELECT statement. When each record in the pointer is manipulated, a name and surname node string containing the XML output is formed. The phone and other information also creates XML.

The parent node is then closed. After the pointer operation has been successfully completed, close the pointer and reassign it. This obtains an XML string that conforms to the target XML model.

In order to use this XML output string effectively, the output must be stored in an ADO Stream object. Data can be accessed using the ReadText method in 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>

The above code creates an ADO to SQL 2000 connection and executes a stored program. The result is stored in the ADO Stream object (Adostream). Its data is written to the response buffer, the stream object is closed, and then some "clearance" operations are done.

This example provides a general way to create a user-XML schema. By connecting HTML tables to an XML data island, you can create a number of targeted solutions.