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