Retrieving Data as XML from SQL Server Article

Step 2: Creating the stored procedure

To retrieve our data in an XML format, 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 analyser on our database server (Start -> Programs -> Microsoft SQL Server -> Query Analyser).

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 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 that resides above that line right now. 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 can 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 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 in the next select statement (where we deal with the categories, products and descriptions tables). If this all sounds a little confusing, take a look at the diagram below:

515diagagain

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" 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).

Go to page: 1 | 2 | 3

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

No Reader comments

Comments on this post are closed.