Programmer to ProgrammerTM  
Wrox Press Ltd  
   
  Search ASPToday Living Book ASPToday Living Book
Index Full Text
 
ASPToday Home
 
 
Home HOME
Site Map SITE MAP
Index INDEX
Full-text search SEARCH
Forum FORUM
Feedback FEEDBACK
Advertise with us ADVERTISE
Subscribe SUBSCRIBE
Bullet LOG OFF
                         
      The ASPToday Article
May 18, 2000
      Previous article -
May 17, 2000
  Next article -
May 19, 2000
 
   
   
   
XML Documents and Stored Procedures on SQL Server   Dejan Stojanovic  
by Dejan Stojanovic
 
CATEGORIES:  Data Access, DNA 2000, XML/Data Transfer  
ARTICLE TYPE: Overview Reader Comments
   
    ABSTRACT  
 
Article Rating
 
   Useful
  
 125 responses

Dejan Stojanovic shows you how to build reusable XML data islands from existing data on Microsoft SQL Server using stored procedures. By combining two common techniques, he shows you how to better divide the database services tier and the business logic tier, and what gains can be achieved with such an approach. Furthermore, he explains how to exchange XML documents through SQL Server, using extended stored procedures and SQL Mail.

   
                   
    Article Discussion   Rate this article   Related Links   Index Entries  
   
 
    ARTICLE

This article shows how to build XML documents from existing data on the Microsoft SQL Server using stored procedures. By combining two common techniques, I'll show you how to better divide the database services tier and the business logic tier, and what gains can be achieved with such an approach. Furthermore, I'll explain how to use stored procedures in conjunction with the messaging capabilities of the SQL Server to exchange XML data.

There are a number of arguments for the use of the stored procedures. The first is that they are compiled and executed within a relational database that boosts the overall speed of the application. The second is the ability to maintain centrally reusable tasks. This improves the performance, scalability and robustness of a specific application. It is a common scenario that the same database is shared between two or more applications – (e.g. a web based extranet application, and a GUI application used on the intranet), and there is a significant gain in overall modularity of the whole system in using stored procedures.

There are a number of ways to build XML documents from data in existing databases – one approach is outlined on Microsoft's recently released XML preview for SQL Server 7.0. This technology is an IIS ISAPI extension that provides HTTP access to SQL Server and XML data formatting and updating. With this, it is possible to build an XML document from existing data using simple functions that add XML tags to a resulting recordset.

As it was announced, the next major release of SQL, i.e. SQL Server 2000, includes rich support for XML and will include a superset of the features available in the XML preview for SQL Server 7.0. Some of the XML related features to be included in SQL Server 2000 are:

FOR XML clause that returns XML data from the SELECT statement

OPEN XML Transact–SQL keyword that provides a relational view on the XML data, that can be used to query data from XML, join XML data with existing relational tables and update the database

Efficient storage of data as XML, maintaining the relationships and hierarchy of data

XML Dynamic Definition

XML is a strictly structured way for data and documents to be stored so that they can be viewed across all types of web based applications. The number of document definitions is growing daily, and, as the whole technology is still quite young, it is possible if not probable that some of the definitions are going to change in the future.

Thus the argument for the use of stored procedures and binding of XML tags on the database server – for with the power of Transact-SQL or even SQL-92, it is possible to dynamically build XML documents on the database server, from existing data. This makes it much easier to upkeep and maintain any further changes in the definitions of XML, as the whole structure of dynamically generated XML documents is moved to the database tier, and the upkeep of all future changes is easier. That means that for all changes of the XML document definition it is enough to change just a database tier.

As an example of the above, we are going to build a heterogeneous XML document from the existing data in the Northwind database with a stored procedure. To build a heterogenous XML document we are going to use two homogenous XML documents, each of them created using stored procedures.

Building a Homogenous XML Document with a Stored Procedure

The logic of the approach is fairly simple. The first example builds an XML document that is used and formatted on the server side to populate a drop down list. Here is the stored procedure that creates a temporary table that holds the resulting XML document, adds the results of the query to that table together with XML tags, and returns an XML document to the application. Top-level XML tags are not added in the stored procedure, they are added in the application, as this can be convenient where the homogenous document is a part of a heterogeneous document.

CREATE PROCEDURE sp_OrderList AS

-- Create a temporary table (single columned) to hold the XML data
   SELECT "IDs" = CONVERT(ntext,'')  INTO #Work 
-- Determine the pointer of the XML column in the temp table
   DECLARE @@ptrval varbinary(16)
   SELECT @@ptrval = TEXTPTR(IDs) 
   FROM #Work

   BEGIN
   DECLARE @OrderID varchar(10)
   DECLARE LineCursor CURSOR FOR
   SELECT DISTINCT OrderID FROM [Order Details]
   FOR READ ONLY

-- Adds XML top element in the temporary table
   UPDATETEXT #Work.IDs @@ptrval NULL 0 '<OrderList>'

-- Adds the query result together with XML tags to the temporary table
   OPEN LineCursor
   FETCH NEXT FROM LineCursor INTO @OrderID
   WHILE @@fetch_status = 0
   BEGIN
      UPDATETEXT #Work.IDs @@ptrval NULL 0 '<OrderID>'
      UPDATETEXT #Work.IDs @@ptrval NULL 0 @OrderID
      UPDATETEXT #Work.IDs @@ptrval NULL 0 '</OrderID>'
      FETCH NEXT FROM LineCursor INTO @OrderID
   END

   DEALLOCATE LineCursor

-- Closing XML tag
   UPDATETEXT #Work.IDs @@ptrval NULL 0 '</OrderList>'

-- Return resulting table
   SELECT * FROM #Work
   END

A single columned temporary table id is created, and will be used for the storage of the XML documents. Each record of the table will store the whole XML document. As a next step, an opening XML tag is added to the resulting record. A cursor is declared which fetches all the rows of the query. In the cursor loop we add the query's resulting row as well as the XML tags into the temporary table.

On exiting the loop, the closing XML tag is added, and, to end, the resulting XML document is returned with a single SELECT on the temporary table. The resulting query is then returned to the script that uses Microsoft XMLDOM to display the result. The ASP script that invokes the stored procedure, and displays the result is the default.asp given here:

<!--#include file="connect.asp"-->
<%
   response.buffer = true

   ' Create a database connection object
   Set Conn = Server.CreateObject("ADODB.Connection")

   ' Tell it which ODBC data source to use
   Conn.Open strConnect

   ' Call the stored procedure and retrive recordset as variant
   XmlOrders = Conn.Execute ("sp_OrderList")
   XML = "<?xml version='1.0'?>"
   XML = XML + "<?xml-stylesheet type='text/xsl' href='list.xsl'?>"
   XML = XML + XmlOrders("IDs")

   ' Map the style file
   styleFile = Server.MapPath("list.xsl")

   ' Apply style on the XML
   Set source =Server.CreateObject("Microsoft.FreeThreadedXMLDOM")
   source.async = false
   source.loadXML(XML)
   Set style = Server.CreateObject("Microsoft.FreeThreadedXMLDOM")
   style.async = false
   style.load(styleFile)
   result = source.transformNode(style)

   ' Display result
   Response.write result
   Response.flush
   Conn.Close
%>

Note that the connection string strConnect in the code above is defined in the connect.asp include file and thus to make this example work it is necessary to adjust the connection parameters in the include file.

The script executes the stored procedure to retrieve the resulting recordset and then uses the XMLDOM object on the server to display the result, as seen below:

Note that instead of using the XMLDOM object on the server side, which will work only if one thread is accessing the XML data, the FreeThreadedXMLDOM object is used as it allows several threads to access the XML data from a single control. In addition, the async property of the DOM object is set to false as it tells the DOM object not to perform an asynchronous load of the XML document. This is important, since immediately after the document is loaded the application is starting to use its contents. If the content is not loaded at that time, the error may occur in accessing it.

Build a Heterogeneous XML Document with a Stored Procedure

Following the example of choosing the specific Order Number from the drop-down list, this submits that number to the second script, display_po.asp , which builds a heterogeneous XML document from the database, and passes it to the browser:

<!--#include file="connect.asp"-->
<%
   id = request.form("id")
   sql = "sp_order "& id

   response.buffer=true
   ' Create a database connection object
   Set Conn = Server.CreateObject("ADODB.Connection")

   ' Tell it which ODBC data source to use
   Conn.Open strConnect
   ' Call the stored procedure
   Set Xml = Conn.Execute (sql)

   ' Add header 
   Response.write "<?xml version='1.0' encoding='ISO-8859-1' ?>" _
                & "<PurchaseOrder>"

   ' Display the results

   While Not Xml Is Nothing
   Response.write Xml(0)
   Set Xml = Xml.NextRecordset
   Wend


   Response.write "</PurchaseOrder>"
   ' Clean Up
   Conn.Close
%>

The script invokes the sp_order stored procedure that uses two nested stored procedures sp_orderheader and sp_lines to build a heterogeneous XML document from the database. The sp_order stored procedure is:

CREATE PROCEDURE sp_order (@ID as int)  AS
BEGIN
   exec sp_orderheader @ID
   exec sp_lines @ID
END

The whole concept is very similar to the first example. The difference is that the call to a stored procedure is done by passing a parameter to it, and that the stored procedure uses nesting of stored procedures. In this example, the stored procedure called from the script invokes two other stored procedures and passes the parameter to them. The script only passes the result to the browser. The result of the execution of the script in the browser can be seen as:

It is possible to build a heterogeneous document with just a single stored procedure, but for the demonstration of modularity, two stored procedures are used, as you could find some of them reusable for some other XML documents.

Just to point something out. As the database used for the example is the Northwind database and as it includes some Unicode characters, it is necessary to add one more line to the XML object. Any document that contains Unicode characters must have the following top-level XML tag:

<?xml version='1.0'? encoding='ISO-8859-1'>

Attempts to load an XML document that contains Unicode characters to the parser, without specifying the proper code page, results in an 'invalid character found in XML document' error.

Step Into the Messaging

One area of deployment of the XML is B-2-B (business-to-business) applications. In such a case, it is necessary to enable the application to exchange data with other applications. One of the supported services of SQL Server is SQLMail service. It allows SQL Server to send and receive e-mail by establishing client connection with a mail server. It is possible to configure SQLMail to use with Microsoft Exchange Server, Microsoft Windows NT Mail or a POP3 server. Using SQL Server extended procedures, messages can be sent either from a trigger or from a stored procedure.

The following example explains how to build an XML document on the SQL Server and send it through SQLMail. To run this example, it is assumed that SQLMail is properly installed and SQLMail service is started on the target system. The stored procedure that generates XML document is:

CREATE PROCEDURE sp_order_to_mail AS

BEGIN
   DECLARE @OrderID varchar(10)
   DECLARE LineCursor CURSOR FOR
   SELECT  DISTINCT OrderID
   FROM Northwind.dbo.[Order Details]
   FOR READ ONLY
   PRINT "<?xml version='1.0' encoding='ISO-8859-1'>"
   PRINT '<OrderList>'
   OPEN LineCursor
   FETCH NEXT FROM LineCursor INTO @OrderID
   WHILE @@fetch_status = 0

   BEGIN
      PRINT  '<OrderID>'
      PRINT  @OrderID
      PRINT  '</OrderID>'
   FETCH NEXT FROM LineCursor INTO @OrderID
   END

   DEALLOCATE LineCursor
   PRINT  '</OrderList>'
END

The approach is the same as in the previous examples, with the difference being that there is no temporary table to store the data because there is no need to return the recordset to the application – the stored procedure is called from the xp_sendmail extended stored procedure. The application calls this extended store procedure and passes to it the parameters that are needed to correctly send e-mail with it. For more information on the xp_sendmail see the SQL Server documentation.

Note also that it is possible to invoke the extended stored procedure only from the master database, so that it is necessary to change the default database. This is possible by changing the DefaultDatabase property of the Connection object, or by using full database object names as it is done in the example script that performs the task ( send_mail.asp ), without changing the properties of the DefaultDatabase object.

   ...
   'Prepare the stored procedure
   strCommand = "master.dbo.xp_sendmail  @recipients='username@domain',"
   strCommand = strCommand + "@query "'Northwind.dbo.sp_order_to_mail',"
   strCommand = strCommand + "@subject = 'XML message'"

   ' Call the stored procedure
   Conn.Execute (strCommand)
   ...

Incoming e-mail can be processed either within SQL Server, or it can be returned to the application. It is very easy for the example to retrieve incoming XML e-mail messages into a single column table of a varchar or nvarchar type that is very suitable for storage of XML documents. If such a task is performed by SQL Server, there is a powerful set of extended stored procedures ( xp_processmail , xp_readmail , xp_findnextmsg and xp_deletemail ) that are able to handle the e-mail messages from the SQLMail inbox.

For demonstration purposes, we are going to retrieve and pass just the first XML message from the SQL Server mailbox to the client. The script that shows this is read_mail.asp . It uses xp_readmail extended stored procedure to read and display the message body of the first message in the SQL Server inbox to the browser. To make this example work ensure that you have at least one message, preferably a message with an XML document in the message body, in the SQL Server inbox. A real scenario could include further handling of the messages on the side of the SQL Server, or within the application.

<!--#include file="connect.asp"-->

<%
   'this is the extra line of code
   Response.expires = 0
   Response.buffer = true
   ' Create a database connection object
   Set Conn = Server.CreateObject("ADODB.Connection")

   ' Tell it which ODBC data source to use
   Conn.Open strConnect
   ' Call the stored procedure
   Set XmlDoc = Conn.Execute ("master.dbo.xp_readmail")

   ' Move to the first recordset 
   XmlDoc.MoveFirst

   ' Display the message body  
   Response.write XmlDoc("Message")

   Response.flush
   XmlDoc.Close
   Conn.Close
%>

As already mentioned, this example displays only the first message in the SQL Server inbox. It is a function of the receiving application to properly handle all incoming messages or to display administration notices. For demonstration purposes the example is parsed to Internet Explorer, but some other scenarios could include retrieval of the incoming messages by SQL Server itself or by the receiving application, appropriate data-storage, and/or suitable handling of data. It can be very useful to declare a single columned database table of varchar or nvarchar that can be used to store whole XML documents.

Conclusion

With stored procedures on SQL Server, it is possible to build reusable XML data islands, and/or their reusable composites on the side of database services layer. As a step forward, it is possible to exchange XML documents through SQL Server, using extended stored procedures. In next-generation B-2-B applications, such an approach can be of significant importance due to its ability to maintain centrally reusable tasks.

 
 
   
  RATE THIS ARTICLE
  Please rate this article (1-5). Was this article...
 
 
Useful? No Yes, Very
 
Innovative? No Yes, Very
 
Informative? No Yes, Very
 
Brief Reader Comments?
Your Name:
(Optional)
 
  USEFUL LINKS
  Related Tasks:
 
 
   
  Related ASPToday Articles
   
  • Using Stored Procedures With ASP, ADO and SQL Server (June 19, 2000)
  • SQL Server XML Data Access with SQLXML.DLL: the Polymorphic Spreadsheet - Part I (November 25, 1999)
  • The Stored Procedure (June 1, 1999)
  • Creating XML Recordsets (March 18, 1999)
  •  
           
     
     
      Related Sources
     
  • XML preview for SQL Server 7.0: http://msdn.microsoft.com/downloads/samples/Internet/xml/sqlxml/sample.asp
  •  
     
           
      Search the ASPToday Living Book   ASPToday Living Book
     
      Index Full Text Advanced 
     
     
           
      Index Entries in this Article
     
  • async property
  •  
  • building
  •  
  • Connection object
  •  
  • DefaultDatabase property
  •  
  • dynamic document definitions
  •  
  • FreeThreadedXMLDOM object
  •  
  • heterogeneous XML documents
  •  
  • homogenous XML documents
  •  
  • load method
  •  
  • loadXML method
  •  
  • SQL Server
  •  
  • SQLMail
  •  
  • stored procedures
  •  
  • stored procedures, building with
  •  
  • Unicode characters
  •  
  • Unicode characters, problems with
  •  
  • XML
  •  
  • XML documents
  •  
  • XML documents, building
  •  
  • XML documents, problems with
  •  
  • XML documents, SQLMail
  •  
  • XMLDOM object
  •  
     
     
    HOME | SITE MAP | INDEX | SEARCH | REFERENCE | FEEDBACK | ADVERTISE | SUBSCRIBE
    .NET Framework Components Data Access DNA 2000 E-commerce Performance
    Security Admin Site Design Scripting XML/Data Transfer Other Technologies

     
    ASPToday is brought to you by Wrox Press (http://www.wrox.com/). Please see our terms and conditions and privacy policy.
    ASPToday is optimised for Microsoft Internet Explorer 5 browsers.
    Please report any website problems to webmaster@asptoday.com. Copyright © 2001 Wrox Press. All Rights Reserved.