Home arrow ASP arrow Page 3 - 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 - Creating the stored procedure
(Page 3 of 5 )

To retrieve our data as XML, we will compress our query batch into a single stored procedure. This encourages code reuse strategies and is easily modifiable in the future. Lets start by loading up query analyzer on our database server (Start -> Programs -> Microsoft SQL Server -> Query Analyzer).

When prompted, enter your database login credentials. You should be connected to the server on which you created the "myProducts" database. Next, enter the following T-SQL commands, which I will explain shortly:

use myProducts

go

CREATE PROCEDURE sp_GetExplicitXML

AS

SELECT 1 AS Tag,

NULL AS Parent,

c.catName as [Category!1!CatName],

NULL as [Product!2!ProdName],

NULL as [Product!2!Description]

FROM categories c

UNION ALL

SELECT 2 AS Tag,

1 AS Parent,

c.catName,

p.productName,

d.descText

FROM categories c, products p, descriptions d

WHERE c.catId = p.productCat AND p.productId = d.descProdId

ORDER BY [Category!1!CatName], [Product!2!ProdName]

FOR XML EXPLICIT


Don't get scared away by the code for the stored procedure above, I promise, it's easy! Allow me to explain the code step by step.

use myProducts

go

CREATE PROCEDURE sp_GetExplicitXML

AS


If you've ever worked with T-SQL then you should be familiar with these commands. The "use myProducts" command tells SQL Server to "run any queries that we execute against the myProducts database". The "go" command tells SQL server to execute all the code up until that line. Next, we tell SQL server that we will create a new stored procedure named "sp_GetExplicitXML". The stored procedure will accept no input parameters and will not return any output values.

SELECT 1 AS Tag,

NULL AS Parent,

c.catName as [Category!1!CatName],

NULL as [Product!2!ProdName],

NULL as [Product!2!Description]

FROM categories c

UNION ALL

SELECT 2 AS Tag,

1 AS Parent,

c.catName,

p.productName,

d.descText

FROM categories c, products p, descriptions d


This is the main chunk of code for our stored procedure. Remember how I said earlier, that when using the "FOR XML EXPLICIT" mode, you could control the shape, column names and content of the returned XML? Well, this is the code that does that for us. The code above acts as a template into which a universal table will be created. A universal table is similar to a normally mapped SQL table with just a couple of differences:
  • A universal table mimics a hierarchical structure by using "tag" and "parent" fields to measure the depth of the data hierarchy. You'll notice in our code above, that the first select command has a tag of 1 with no parent. In the next select statement, there is a tag of 2, with a parent of 1, which means that all the results returned from the second select statement will be children of the first select statement.
  • As you've probably noticed, the column names for a universal table differ from normal column names. Lets break down the name of one of our universal table column names from the code above:
  • [Category!1!CatName] As you can see, the column name has three values, each separated by an exclamation mark. The first value is the name of the XML element that the universal table will produce. The second is the tag index, which lets SQL's internal XML pointer know which level of the hierarchy into which this data will be placed. The last value, "CatName" is the name of the attribute into which the value of the select statement will be inserted. A sample category element from our database would look like this:
<Category CatName="ASP">

Notice how there are two NULL values in the first select statement? That's because in the first statement, we're only concerned with the categories for our books, which come from the categories table ("FROM categories c"). These values are left NULL because they'll be filled in as part of the next select statement (where we deal with the categories, products and descriptions tables).

Now, to the last part of the code:

WHERE c.catId = p.productCat AND p.productId = d.descProdId

ORDER BY [Category!1!CatName], [Product!2!ProdName]

FOR XML EXPLICIT


In this final chunk of our code, we make sure that each category displays only the products whose "productCat" field is equal to their "catId" field. Also, we match each product with its description using the "p.productId = d.descProdId" equality test. Lastly, we sort the XML output by ascending category name, and ascending product name (NB: Remember to run the code by pressing Alt+X).
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