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.
SQL Server 2000, XML and XSL: The Ultimate Combination - Creating our sample database (Page 2 of 5 )
On your SQL Server, open Enterprise Manager and create a new database named "myProducts". Then, using either Enterprise Manager or Query Analyzer, create the tables shown below:
(Note: catId, productId and descId are all auto-incrementing identity fields)
As you've probably guessed, we're using three tables to simulate a very simple product description database (let's assume we sell books). The diagram above shows the hierarchy of our data: categories listing products, listing their descriptions. Before we progress to the next step, we'll need to create some dummy data in our tables. To maximize productivity and minimize the length of this article, I've created a simple T-SQL script, which will populate our tables as needed. You can download it as part of the support material at the end of this article. The script will create 3 categories, 7 products and 7 descriptions.