Distributing Enterprise Data via XML with SQL Server 2000 and Excel 2002

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Henry Lau
Microsoft Consulting Services

April 2002

Applies to:
    Microsoft Excel 2002
    Microsoft® SQL Server™ 2000
    SQLXML 3.0 for SQL Server 2000

Summary: How to take advantage of features in SQL Server 2000 and Excel 2002 in order to distribute enterprise data to Excel 2002 via XML. (19 printed pages)

Introduction
The Business Challenge of Distributed Corporate Information
The Business Solution
Basic Concepts
Set Up a SQL Server IIS Virtual Directory
Create a SQLXML Template File
Create a SQL Server Test Database
Create a SQL Server Stored Procedure that Generates XML-SS
Load 'mysp_generate_excel' into SQL Server
The Excel 2002 End User Experience
SQL Server Data Transformation Services
Appendix A: Code Listing for 'mysp_generate_excel'
Appendix B: Additional References

Introduction

This white paper describes how to take advantage of features in Microsoft® SQL Server™ 2000 and Excel 2002 in order to distribute enterprise data to Excel 2002 via XML. Sample code is described in detail in order to illustrate how SQLXML may be used to generate XML data that is in a format directly readable by Excel 2002. This format is referred to as the Excel XML Spreadsheet Schema or XML-SS. This XML format is supported by Excel 2002 and the Office Web Spreadsheet Component.

The Business Challenge of Distributed Corporate Information

Companies today are collecting and storing a tremendous amount of information from many sources (customers, partners, vendors, and others). Large companies likely store information in databases of many different varieties across their enterprise environment. This tends to make data harder to access and analyze from a single desktop.

To be competitive in today's fast-paced market, it is critical to leverage as much of this information as possible in a useful manner. This information needs to be consolidated into a concise, comprehensive, and cohesive manner. Consolidated data should be easily accessible on a single desktop by any knowledge worker that can use that information for competitive advantage.

The Business Solution

In short, this document proposes a business solution to the issue of making enterprise data more accessible by leveraging the following Microsoft technologies:

SQL Server 2000 XML capabilities to generate XML from SQL Server data.
Excel 2002 capabilities read XML data.
SQL Server data transformation capabilities, which can take data from many sources (some major examples include IMS, DB2, Oracle, and Sybase) and consolidate them into SQL Server on a regularly scheduled basis.

SQL Server is an enterprise-class database, capable of hosting multi-terabyte databases and handling the toughest transaction processing loads. SQL Server holds many of the top spots in the industry-recognized TPC-C transaction processing database performance benchmark. (Refer to Top Ten TPC-C by Performance Version 5 Results for more information.) Not only does SQL Server provide top-level enterprise database performance, scalability, and reliability, but it does so at a much lower cost in comparison to other leading databases. Refer to the Microsoft.com SQL Server Web site for the latest information on SQL Server.

In addition to its many productivity, collaboration, data analysis, and reporting capabilities, Excel 2002 now includes the ability to read and save data in XML. It does so in a format referred to as XML-SS or Excel XML Spreadsheet Schema. This feature opens the door for Excel 2002 to receive data from many different sources, as long as they provide data as XML.

The goal of this document is to encourage you to take advantage of SQL Server 2000 to make enterprise data more readily assessable across your entire organization by using SQL Server data transformation capabilities to gather critical data from enterprise legacy systems and then use SQL Server XML capabilities to generate XML that can be consumed by Excel 2002. Excel 2002 is a very powerful data analysis tool, with the ability to read XML data. Combining SQL Server 2000 and Excel 2002 makes a great combination for increasing visibility to business critical enterprise data.

Basic Concepts

Here are the basic steps required in order to generate XML with SQL Server 2000 such that it can be read by Excel 2002. More information will be provided about all the key points in later sections.

Configure SQL Server 2000 XML support in Internet Information Server (IIS). This involves setting up an IIS virtual directory to support the calling of SQLXML template queries.
Create a SQLXML template file that will be used to call a SQL Server stored procedure.
Create a SQL Server stored procedure that retrieves data from SQL Server and generates data in XML-SS format by using the SELECT . . . FOR XML EXPLICIT statement.
Use Internet Explorer to access the SQLXML template query via the URL.
Save the returned data as a local Excel 2002 formatted XML file by using Internet Explorer's File...Save As functionality

You now have a local copy of the SQL Server data that can be opened in Excel 2002 for any kind of Excel 2002 work.

SQL Server 2000 ships with a sample database named Northwind. We'll use this database to illustrate an example of using SQLXML templates to retrieve information from SQL Server via HTTP in an XML-SS format.

This document will assume that SQL Server 2000 and IIS are running on the same machine. It is also assumed that you have installed SQLXML 3.0 for SQL Server 2000, which you may download from the Microsoft SQLXML Web site.

Set Up a SQL Server IIS Virtual Directory

Create a local directory in a location of your choice. This local directory will store your SQLXML files. For this example, the pathname of this local directory is "c:\Inetpub\wwwroot\sqlxml_dir1".
On the Microsoft Windows Start menu, point to All Programs, point to Microsoft SQL Server, and then click Configure SQLXML Support IIS.
On the IIS Virtual Directory Management for SQL Server MMC console, next to the name of the IIS server in the left pane of the MMC console, click the "+" sign.
In the left pane, click Default Web Site.
Right-click Default Web Site, select New, and then click Virtual Directory.
In the New Virtual Directory Properties dialog box, enter a name of your choice for the SQLXML virtual directory in the Virtual Directory Name text box. For this example, the virtual directory name is "myvdir1".
Browse to the local directory that contains your XML template file. For this example, use the "c:\Inetpub\wwwroot\sqlxml_dir1" directory.
Click the Security tab and then select the Use Windows Integrated Authentication radio button.
Click the Data Source tab. Clear the Use default database for current login checkbox and then select Northwind in the database dropdown list.
Click the Virtual Names tab. Enter a name of your choice to be the IIS virtual directory where SQLXML template files will be called from. For this example, "templates".
Select "template" in the Type dropdown box.
Click the ". . ." button and browse to the local directory that contains your XML template file. For this example, use the "c:\Inetpub\wwwroot\sqlxml_dir1" directory.
Click Save.
Click OK.

You have now set up an IIS virtual directory that may be used to host SQLXML template files. With the directory names that were specified in this example, XML template files which are placed into the local directory "c:\Inetpub\wwwroot\sqlxml_dir1" will be accessible via the URL http://localhost/myvdir1/templates/filename.xml or http://servername/myvdir1/templates/filename.xml, where filename.xml could be any filename that you choose for a SQLXML template file. In this example, we will be using the filename "my_nw1.xml".

Create a SQLXML Template File

The following is the XML code used to call the SQL Server stored procedure that will generate the XML data stream. This code is referred to as the SQLXML template file. You need to save this code via Notepad using the filename "my_nw1.xml" and place it into your local directory set up for the SQLXML IIS virtual directory as described in the previous section. For this document, we are assuming the local directory is "c:\Inetpub\wwwroot\sqlxml_dir1".

<?xml version="1.0"?>
<Workbook 
xmlns:sql="urn:schemas-microsoft-com:xml-sql"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
      xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<sql:query>
exec testdb1..mysp_generate_excel
</sql:query>
</Workbook>

Most of the XML code above is static code. Everything outside of the <sql:query> and </sql:query> elements is static XML. With respect to the static parts of the code, we know that we need to generate XML-SS so it is okay to just put in the <Workbook> element along with the attributes that we know that we will need into the template file. The XML version number is also included at the top of the file because it is a requirement of XML files to be read by Excel 2002.

The namespace "urn:schemas-microsoft-com:xml-sql" is declared because that is how the <sql:query> element can be recognized as a call to SQLXML in order to execute the statements contained within the <sql:query> element. In this example, the statement is the 'mysp_generate_excel' stored procedure that generates the XML data stream. The namespace "urn:schemas-microsoft-com:office:spreadsheet" is declared because there will be generated elements in the XML data stream that will refer to the namespace.

We enabled the use of the <sql:query> element by including the SQLXML namespace in the <Workbook> element. By "enabling the namespace," we are referring to the code segment:

xmlns:sql="urn:schemas-microsoft-com:xml-sql"

The <Workbook> element needs to also enable the Office spreadsheet XML namespace and associate it with the "ss" prefix because this namespace will be used in the XML that is generated by SQL Server and returned by the stored procedure called 'mysp_generate_excel'.

The code inside <sql:query> and </sql:query> elements populates the data for this example. The <sql:query> element is used so that the SQL stored procedure can be executed. It is the results of 'mysp_generate_excel' that provides the rows and columns of this example's data in XML-SS format.

Note that by enabling the Office spreadsheet XML namespace, we could leverage other elements of the XML-SS format. This could include static XML-SS elements that we include in the SQLXML template file. Or the stored procedure might generate some additional XML-SS elements. XML-SS elements could include typical Excel spreadsheet properties like the Author, Last time spreadsheet was saved, or fonts for spreadsheet data. For more information on XML-SS elements, please refer to the XML-Spreadsheet Reference.

Create a SQL Server Test Database

You may notice that the name of the SQL Server database used to hold the 'mysp_generate_excel' stored procedure is named "testdb1". You may create a SQL Server test database with any name that you would like but for the purposes of the code samples in this document, we'll stick with the name "testdb1". To create the test database, follow these steps:

On the Microsoft Windows Start menu, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
In the SQL Server Enterprise Manager MMC console, in the left pane, click the "+" sign next to Microsoft SQL Servers.
In the left pane, click the "+" sign next to SQL Server Group.
In the left pane, click the "+" sign next to (local) (Windows NT).
Right-click Databases and then select New Database . . . .
In the Name box, type a name of your choice.
Click OK.

Create a SQL Server Stored Procedure that Generates XML-SS

In the subsections that follow, I will explain all of the key aspects of the SQL Server 2000 stored procedure that was used to generate Northwind data in XML-SS format so that the data can be directly read by Excel 2002.

The entire code listing is provided at the bottom of this document in Appendix A for reference. We will focus on key code segments in the following sections.

Description of Northwind Data Being Queried

Here is the SQL query that will retrieve the information that we are looking for. It is a good idea to execute the query in SQL Server Query Analyzer and examine the results before moving on to the latter steps, which will include generation of the same data using SQLXML and in XML-SS format for Excel 2002.

The SQL query below answers what could be a typical business question. The question answered is, "Show the orders that were shipped between the dates of January 7,1998 and January 9,1998 inclusive. For each order, show the customer that placed the order, what product they ordered, the quantity of each product ordered, and the employee that was responsible for that order."

select o.OrderID,
convert(varchar(20),o.ShippedDate,101) as 'Shipment Date',
o.CustomerID,
c.CompanyName as 'Customer Company Name',
      c.ContactName as 'Customer Contact Name',
C.Phone as 'Customer Phone Number',
      p.ProductName as 'Product Ordered',
od.Quantity,
      o.EmployeeID,
e.LastName as 'Employee Last Name'
      from Northwind..Orders o 
      inner join NorthWind..[Order Details] od on o.OrderID =
        od.OrderID
      inner join NorthWind..Customers c on o.CustomerID = c.CustomerID
      inner join NorthWind..Employees e on o.EmployeeID = e.EmployeeID
      inner join NorthWind..Products p on od.ProductID = p.ProductID
      where o.ShippedDate between '1/7/1998' and '1/9/1998'
order by o.ShippedDate

Temporary Table Creation

Notice the SQL Server temporary table creation statement in the code for 'mysp_generate_excel'. The temporary table is necessary because the rowid column is needed in order to provide a way to identify each row of the temporary table. This is necessary because, later on, multiple SQL queries will need to simultaneously retrieve data from this temporary table as part of the process for generating the XML data stream. The result of these different select queries will need to be combined into a single resultset using the SQL "UNION ALL" operator. To combine the multiple resultsets in a meaningful order, rowid will be used.

The data from the SQL query described in the previous section will be inserted into the SQL Server temporary table named #temp_table1. With each row of the resultset from the SQL query, an integer row identifier will be generated and inserted into the rowid column of the temporary table.

create table #temp_table1(
      rowid int identity,
      OrderID varchar(10),
      [Shipment Date] varchar(100),
      CustomerID varchar(10),
      CompanyName varchar(100),
      ContactName varchar(100),
      Phone varchar(30),
      ProductName varchar(100),
      Quantity varchar(10),
      EmployeeID varchar(10),
      [Employee Last Name] varchar(100)
)

insert into #temp_table1(OrderID ,[Shipment Date] ,CustomerID,
CompanyName, ContactName ,
      Phone ,ProductName ,Quantity ,EmployeeID ,[Employee Last Name]) 
      select  o.OrderID,
            convert(varchar(20),o.ShippedDate,101) as 'Shipment Date',
            o.CustomerID,
            c.CompanyName as 'Customer Company Name',
            c.ContactName as 'Customer Contact Name',
            c.Phone as 'Customer Phone Number',
            p.ProductName as 'Product Ordered',
            od.Quantity,
            o.EmployeeID,
            e.LastName as 'Employee Last Name'
      from Northwind..Orders o 
      inner join NorthWind..[Order Details] od on o.OrderID =
        od.OrderID
      inner join NorthWind..Customers c on o.CustomerID = c.CustomerID
      inner join NorthWind..Employees e on o.EmployeeID = e.EmployeeID
      inner join NorthWind..Products p on od.ProductID = p.ProductID
      where o.ShippedDate between '1/7/1998' and '1/9/1998'
      order by o.ShippedDate

Concept of the SQLXML "Universal Table"

After the stored procedure has finished creating and populating the temporary table, the SQL select statements that will generate the XML data stream will be executed.

Within documentation for SQL Server 2000 XML functionality, the concept of the "universal table" is mentioned. The universal table is the combination of all the resultsets of the all the SELECT . . . FOR XML EXPLICIT statements that are used for a particular XML generation query. In 'mysp_generate_excel', one universal table is created from all of the select statements that have been combined via the "UNION ALL" clause.

SQL Server requires that the universal table be structured such that all rows include an integer column named 'tag' as the first column. The tag column assigns an integer identifier to the XML element being generated. The second column of each select statement that the universal table requires is an integer column labeled as 'parent'. The parent column provides a way for one select statement to indicate the XML parent element for that particular select statement.

You will notice that there are a lot of nulls used in the select statements in 'mysp_generate_excel'. That is because not all the columns of the universal table are relevant to all the select statements. When a select statement does not need the data from various columns for sorting or XML generation, then null may be retrieved instead of actual column data.

Generation of <Worksheet> and <Table> Elements

The first select statement in the universal table creation portion of 'mysp_generate_excel' is where the names of all the XML elements are identified in the column aliases.

select 1 as tag,
null as parent,
'Northwind XML-SS Example 1' as [Worksheet!1!ss:Name],
null as [Table!2],
null as [Row!3!row_id!hide],
null as [Cell!4!cell_id!hide],
null as [Data!5],
null as [Data!5!ss:Type]

The tag column with an integer value of '1' gives us a way to refer to the <Worksheet> element later on in the SQL query. This is important because we want to be able to create other elements as child elements of the <Worksheet>. The column 'null as parent' indicates that this is the outermost XML element being generated by the SQL query. Other XML elements generated later on in this query could be at the same outermost level by indicating, 'null as parent as well'. But in this example, <Worksheet> will be the only element at the outermost level.

The column 'Northwind XML-SS Example 1' as [Worksheet!1!ss:Name] indicates that the text string 'Northwind XML-SS Example 1' is to be assigned to the generated <Worksheet> element as the 'ss:Name' attribute.

The column alias [Table!2] indicates that the select statement with a tag column value of '2' will be the select statement that generates the XML for the <Table> element.

select 2 as tag,
1 as parent,
null,null,null,null,null,null

The select statements that generate the <Workbook> and <Table> elements do not select data from any table. That is because they only need to select one row for use in the universal table.

The remaining columns have null assignments because we don't need the data from these columns in the universal table. These select statements only return one row each so we don't need to worry about providing data for sorting purposes. We'll see in the select statements later on that providing data for sorting is important at times.

Generation of <Row>, <Cell>, and <Data> Elements

The XML-SS schema requires that each Excel cell of data needs to be enclosed in a <Data> element, then a <Cell> element. All the cells for a given row of data in Excel then need to be enclosed in a <Row> element. Here is a very simple example of how the XML-SS will look like for a row that contains only one string of data, that being 'Seven Seas Imports'. Notice the ss:Type attribute for the <Data> element. This is a minimum requirement of the XML-SS schema for all <Data> element elements in any XML document that is to be read by Excel 2002. There are other ss:Type values available besides "String". For more information, refer to the XML-SS reference, as mentioned at the end of this document.

<Row>
      <Cell>
            <Data ss:Type="String">
                  Seven Seas Imports
            </Data>
      </Cell>
</Row>

The select statement with a tag value of '3' is responsible for generating the <Row> element. The parent column is set to the value of '2' to indicate that the <Row> elements are child elements of the <Table> elements. Rowid is fetched from the temporary table into the column [Row!3!row_id!hide] because it will be needed to sort the row elements in the proper order.

select 3 as tag,
      2 as parent,
      null,null,rowid,null,null,null
      from #temp_table1

As you examine the stored procedure code for 'mysp_generate_excel' you might be curious why the select statements with tag numbers 4 and 5 repeat. There are two reasons for this. First, these two select statements generate the <Cell> and <Data> elements. The second is that one pair of these select statements is required for each column of data that will exist in the Excel spreadsheet. Notice that the sixth column position of each of these select statements contains an integer. The pattern that emerges is that this integer represents the Excel spreadsheet column.

So the general rule for putting together XML documents in XML-SS format is that you will need to use one of these pairs of select statements for each column of data in Excel. Once you get used to the pattern, putting together XML-SS for spreadsheets with different column configurations will become just a matter of cutting and pasting the required number of 'select 4 . . . ' and 'select 5 . . . ' statement pairs, fixing up the column number in column six of the 'select 4 . . . ' statement and putting in the data column, data type in columns 7 and 8 of the 'select 5 . . . ' statement.

select 4 as tag,
      3 as parent,
      null,null,rowid,1,null,null
      from #temp_table1
union all
select  5 as tag,
      4 as parent,
      null,null,rowid,1,OrderID,'String'
      from #temp_table1
      union all
select  4 as tag,
      3 as parent,
      null,null,rowid,2,null,null
      from #temp_table1
union all
select  5 as tag,
      4 as parent,
      null,null,rowid,2,[Shipment Date],'String'
      from #temp_table1

Sorting the Generated XML

Once the resultsets of all the select statements that make up the universal table have been put together, it is important to order the universal table such that XML elements will be generated in the proper order to be recognized as XML-SS. To do so, the following ORDER BY clause is used:

order by [Row!3!row_id!hide],[Cell!4!cell_id!hide]

This ORDER BY clause indicates that all of the rows of the universal table should first be ordered by row_id and within each row_id, rows should be sorted by cell_id. This is so that all <Cell> and <Data> elements and their accompanying data will be contained within the proper <Row> element. Recall that back in the 'select 4 . . . ' and 'select 5 . . . ' statements, the rowid was fetched as column 5 and that the cell_id was assigned as column 6 of the select statements. It is the ORDER BY clause that makes use of this data for sorting purposes. The row_id and cell_id data is not meant for inclusion in the final XML document, which is why the 'hide' directive is used in the column aliases for the <Row> and <Cell> elements. We will talk a little more about the 'hide' directive in the next section because it is handy for SQLXML development.

'hide' Directive As a Handy SQLXML Debug Tool

When you are first creating SQL queries that will be used in SQLXML templates, it is handy to be able to see information such as primary keys associated with data at each XML element level. You may need these keys anyways in order to sort your resulting XML data as we saw with [Row!3!row_id!hide] and [Cell!4!cell_id!hide]. While there might not be an interest in seeing these values generated in the final XML document, they can serve as a valuable aid during the development process in order to be able to visually spot check your work in Internet Explorer or SQL Server Query Analyzer and know that data is being generated in the manner that is expected. As you are getting closer to the final version of generated XML, it may be less necessary to view these generated values. When this is the case, the 'hide' directive is a very convenient way to disable the generation of the values. For example, look at the following statement:

select 1 as tag,
null as parent,
au_id as [Author!1!au_id],
au_lname as [Author!1]
from pubs..authors where au_id = '409-56-7008'
for xml explicit

The above statement generates an XML stream representing the last name of the author in the Authors table of the Pubs database who has an id equal to '409-56-7008'. The generated XML looks like:

<Author au_id="409-56-7008">Bennet</Author>

The inclusion of the au_id in the Author element may be a handy tool during the development and debugging process but we may not wish to see this information in the final version of the XML. If this is the case, it is easy to use the 'hide' directive as the fourth parameter to the column alias in order to suppress the generation of the information in XML. For example, the same statement using the hide directive would look like this:

select 1 as tag,
null as parent,
au_id as [Author!1!au_id!hide],
au_lname as [Author!1]
from pubs..authors where au_id = '409-56-7008'
for xml explicit

As a result, the generated XML would look like this:

<Author>Bennet</Author>

Note that the Pubs database was used in this example instead of the Northwind database. Pubs is another sample database that ships with SQL Server and you may wish to try executing the above statements in SQL Server Query Analyzer to view the results.

Load 'mysp_generate_excel' into SQL Server

At this point, you should load the stored procedure 'mysp_generate_excel' into the 'testdb1' database using SQL Server Query Analyzer.

On the Microsoft Windows Start menu, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer to open the SQL Server Query Analyzer tool.
In the Connect to SQL Server dialog box, make sure that the SQL Server box contains a ".", the Windows Authentication" button is selected, and then click OK to connect to SQL Server.
On the Query menu, click Change Database . . . . In the Name column. double-click testdb1.
Cut and paste the code in Appendix A into the query window. Make sure you copy everything between "create proc" and "go" inclusive.
On the Query menu, click Execute to load the stored procedure into SQL Server.
Close Query Analyzer.

The Excel 2002 End User Experience

At this point, you are ready to access the SQLXML generated Excel 2002 spreadsheet data. There are several ways to go about doing this.

You can retrieve the XML-SS generated by 'mysp_generate_excel' just using Excel 2002.

Open Excel 2002.
On the File menu, click Open.
In the File name: box, type in http://localhost/myvdir1/templates/my_nw1.xml.
Click Open.
Excel 2002 may inform you that this you do not have permissions to make changes to the generated document. Click Yes to open a read-only copy.
With the data now in Excel 2002 you have the choice saving the data as a new, editable spreadsheet to perform analysis and reporting with.

Method Two: Directly via Excel 2002 using Web Query.

You can also use Excel Web Query to retrieve the XML-SS generated by 'mysp_generate_excel'.

Open Excel 2002 with a new workbook active. This is true by default.
On the Data menu, point to Import External Data and then click New Web Query . . . .
In the New Web Query dialog box, type http://localhost/myvdir1/templates/my_nw1.xml in the Address box.
Click Go.
Click Import.
Click OK to accept the default location for importing the data into Excel 2002.

Method Three: Using Internet Explorer

You may also like to access the XML-SS data generated by SQL Server using Internet Explorer and performing the steps below. This method leverages Internet Explorer's ability to present XML in an easily readable fashion. It makes it easy for you to review the XML-SS being generated. It is also possible for Internet Explorer to save the XML-SS into a file on your local disk to open in Excel 2002.

Open Internet Explorer.
Go to the URL to access the stored procedure (http://localhost/myvdir1/templates/my_nw1.xml).
Save the returned XML by selecting File, Save As, and saving the XML file to a filename of your choosing.
Open the XML file in Excel 2002. On the File menu, click Open and select your XML file. Click Open.

Extensible Stylesheet Language Transformations (XSLT)

We did not use XSLT in our example, but it is worth mentioning. XSLT provides a programmatic method to transform XML documents into a different and desired format. We didn't need to use XSL files to perform transformations on the XML generated by the 'mysp_generate_excel' example because the XML was already in the desired XML-SS format. No XML data was pulled from other sources other than our stored procedures, so all XML created by our XML files are in the right format.

It is worth noting that if we wanted to, we could have the pulled in XML data from other sources and combined them with the data generated in our stored procedure. If this were the case and the XML coming from those other sources was not in XML-SS format, use of XSLT could transform incoming XML into XML-SS format and merge it appropriately with the XML-SS output of 'mysp_generate_excel' so that Excel 2002 could read the entire XML document.

It is possible to programmatically manipulate XML with XSLT into almost any desired form. A detailed discussion of XSL is outside the scope of this document, but you are encouraged to look at the Microsoft XSLT Developer's Guide as a starting point for learning about XSLT.

SQL Server Data Transformation Services

While the data in the example described in this document was stored in SQL Server, it would be easy to perform the same XML-SS generation work with data from many other sources by leveraging another feature of SQL Server.

SQL Server Data Transformation Services, commonly referred to as DTS, is an enterprise data transformation engine. It is part of the set of tools included with SQL Server 2000. By data transformation engine, we mean that DTS is capable of reading data from many different relational and non-relational sources that may be present in a company. This is done leveraging OLE DB.

There is a lot of great information contained in SQL Server Books Online and at Data Transformation Services DTS Basics so I will not go into further details on DTS here, but I encourage you to review these other sources of information and think about the new possibilities for distributing enterprise information via SQLXML , DTS, and Excel 2002.

Appendix A: Code Listing for 'mysp_generate_excel'

For your reference, here is the entire SQL Server 2000 stored procedure code listing for 'mysp_generate_excel', which retrieves Northwind data in XML-SS format.

create proc mysp_generate_excel as 

create table #temp_table1(
      rowid int identity,
      OrderID varchar(10),
      [Shipment Date] varchar(100),
      CustomerID varchar(10),
      CompanyName varchar(100),
      ContactName varchar(100),
      Phone varchar(30),
      ProductName varchar(100),
      Quantity varchar(10),
      EmployeeID varchar(10),
      [Employee Last Name] varchar(100)
)

insert into #temp_table1(OrderID ,[Shipment Date] ,CustomerID, 
  CompanyName, ContactName ,
      Phone ,ProductName ,Quantity ,EmployeeID ,[Employee Last Name])
      select  o.OrderID,
            convert(varchar(20),o.ShippedDate,101) as 'Shipment Date',
            o.CustomerID,
            c.CompanyName as 'Customer Company Name',
            c.ContactName as 'Customer Contact Name',
            c.Phone as 'Customer Phone Number',
            p.ProductName as 'Product Ordered',
            od.Quantity,
            o.EmployeeID,
            e.LastName as 'Employee Last Name'
      from Northwind..Orders o 
      inner join NorthWind..[Order Details] od on o.OrderID = 
        od.OrderID
      inner join NorthWind..Customers c on o.CustomerID = c.CustomerID
      inner join NorthWind..Employees e on o.EmployeeID = e.EmployeeID
      inner join NorthWind..Products p on od.ProductID = p.ProductID
      where o.ShippedDate between '1/7/1998' and '1/9/1998'
      order by o.ShippedDate

select 1 as tag,
      null as parent,
      'Northwind XML-SS Example 1' as [Worksheet!1!ss:Name],
      null as [Table!2],
      null as [Row!3!row_id!hide],
      null as [Cell!4!cell_id!hide],
      null as [Data!5],
      null as [Data!5!ss:Type]
union all
select 2 as tag,
      1 as parent,
      null,null,null,null,null,null
union all
select 3 as tag,
      2 as parent,
      null,null,rowid,null,null,null
      from #temp_table1
union all
select  4 as tag,
      3 as parent,
      null,null,rowid,1,null,null
      from #temp_table1
union all
select  5 as tag,
      4 as parent,
      null,null,rowid,1,OrderID,'String'
      from #temp_table1
union all
select  4 as tag,
      3 as parent,
      null,null,rowid,2,null,null
      from #temp_table1
union all
select  5 as tag,
      4 as parent,
      null,null,rowid,2,[Shipment Date],'String'
      from #temp_table1
union all
select  4 as tag,
      3 as parent,
      null,null,rowid,3,null,null
      from #temp_table1
union all
select  5 as tag,
      4 as parent,
      null,null,rowid,3,CustomerID,'String'
      from #temp_table1
union all
select  4 as tag,
      3 as parent,
      null,null,rowid,4,null,null
      from #temp_table1
union all
select  5 as tag,
      4 as parent,
      null,null,rowid,4,CompanyName,'String'
      from #temp_table1
union all
select  4 as tag,
      3 as parent,
      null,null,rowid,5,null,null
      from #temp_table1
union all
select  5 as tag,
      4 as parent,
      null,null,rowid,5,ContactName,'String'
      from #temp_table1
union all
select  4 as tag,
      3 as parent,
      null,null,rowid,6,null,null
      from #temp_table1
union all
select  5 as tag,
      4 as parent,
      null,null,rowid,6,Phone,'String'
      from #temp_table1
union all
select  4 as tag,
      3 as parent,
      null,null,rowid,7,null,null
      from #temp_table1
union all
select  5 as tag,
      4 as parent,
      null,null,rowid,7,ProductName,'String'
      from #temp_table1
union all
select  4 as tag,
      3 as parent,
      null,null,rowid,8,null,null
      from #temp_table1
union all
select  5 as tag,
      4 as parent,
      null,null,rowid,8,Quantity,'String'
      from #temp_table1
union all
select  4 as tag,
      3 as parent,
      null,null,rowid,9,null,null
      from #temp_table1
union all
select  5 as tag,
      4 as parent,
      null,null,rowid,9,EmployeeID,'String'
      from #temp_table1
union all
select  4 as tag,
      3 as parent,
      null,null,rowid,10,null,null
      from #temp_table1
union all
select  5 as tag,
      4 as parent,
      null,null,rowid,10,[Employee Last Name],'String'
      from #temp_table1
order by [Row!3!row_id!hide],[Cell!4!cell_id!hide]
      for xml explicit

drop table #temp_table1
go

Appendix B: Additional References

Using Microsoft Excel 2002 and Microsoft BizTalk Server To Build B2B Solutions by Joe Fütty, Uma Subramanian, and Acey Bunch
XML Web Service-Enabled Office Documents by Chris Lovett and Siew-Moi Khor
XML Support in Excel 2002
DTS Basics
XML Spreadsheet Reference
XML in Excel and the Spreadsheet Component by Michael Stowe, providing some further background on XML-SS
Programming Microsoft SQL Server 2000 with XML by Graeme Malcolm, published by Microsoft Press, ISBN 0-7356-1369-9