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