Home arrow ASP arrow SQL Server 2000, XML and XSL: The Ultimate Combination
ASP

SQL Server 2000, XML and XSL: The Ultimate Combination


All the hype that once surrounded XML is finally starting to die down, and developers are really beginning to harness the power and flexibility of the language. In this article, Mitchell will show us how SQL Server 2000 incorporates XML support using the "FOR XML EXPLICIT" method to retrieve data from a table as a customised XML document.

Author Info:
By: Mitchell Harper
Rating: 5 stars5 stars5 stars5 stars5 stars / 21
January 07, 2002
TABLE OF CONTENTS:
  1. · SQL Server 2000, XML and XSL: The Ultimate Combination
  2. · Creating our sample database
  3. · Creating the stored procedure
  4. · Displaying the XML with ASP and XSL
  5. · Conclusion

print this article
SEARCH DEVARTICLES

SQL Server 2000, XML and XSL: The Ultimate Combination
(Page 1 of 5 )

All the hype that once surrounded XML is finally starting to die down, and developers are really beginning to harness the power and flexibility of the language. XML is a data descriptive language that uses a set of user-defined tags to describe data in a hierarchically structured format.

The release of Microsoft SQL Server 2000 saw Microsoft jump on the XML band-wagon too: they've included a number of different ways to manipulate data as well-formed XML. Firstly, there's the SQL XML support. Microsoft's implementation of SQL XML provides a simple configuration tool that allows developers to gain remote access to databases using URL based queries over HTTP. For example, we can setup an SQL XML virtual directory on our Web server named "myVirtual". Then, assuming we have the appropriate security permissions, we can use any browser to query our database using a simple URL based query (such as: http://www.myserver.com/myVirtual?SQL=select+*+from+products+for+xml+auto). This then returns our results as a valid XML document.

Notice the "for xml auto" part of our query above? This determines the way in which SQL Server 2000 "shapes" our data. There are three shaping methods:
  • "for xml auto": Returns XML elements that are nested, based on which tables are listed in the "from" part of the query and which fields are listed in the "select" part.
  • "for xml raw": Returns XML elements with the "row" prefix (ex: "<row tProduct ...>"). Each column in a table is represented as an attribute and null column values aren't included.
  • "for xml explicit": Explicit mode is the most complex shaping method used in SQL Server 2000. It allows users to query a data source in such a way that the names and values of the returned XML are specified before the query batch is executed.
It's the third method, "for xml explicit", that I will discuss today. The explicit method, in my opinion, is the most powerful feature of SQL Server 2000. Not only can we specify how XML data is returned to us, but we can also use record filters and sorting patterns as well, because, as we all know, sorting an XML document any other way is almost impossible.

Now, let's get into it. This article is aimed at the intermediate to advanced developer looking to use XML in the BLL (business logic layer) of an n-Tier based application where speed is a critical issue. To benefit from this article, you'll need to equip yourself with the following:
  • A Win2k box running IIS and SQL Server 2000 with the MSXML 3 (or above) library installed
  • Intermediate ASP, SQL, XML and XSL knowledge

blog comments powered by Disqus
ASP ARTICLES

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials