Use XSLT to customize XML output
One of the biggest problems that companies face in their use of information systems is exchanging data from those systems with other companies. EDI was created years ago to solve the data-exchange problem, but it is costly and cumbersome to implement and to use. To use EDI, you must either buy expensive software or create your own, and you need a network to exchange data over. As the Internet has moved into a standard role, an increasing number of companies have started using Web services to exchange data. A Web service is a software application that provides some function to a remote system, usually across HTTP.
Public Web sites such as Nasdaq and various sports and news sites also share the data-exchange problem. Many users would like to regularly extract data from these sites, but to do so, they must resort to parsing the data from public HTML pages. However, HTML's tagging system provides no good way to separate data from formatting. If the page's structure changes, the user's applications must also change. However, you can use XML with Active Server Pages (ASP), ADO, and SQL Server to solve part of the data-exchange problem. Let's look at a sample system that provides a simple Web service to return data from SQL Server.
Data-Exchange Needs
Let's say that Company X must routinely provide product information to its customers in an electronic format. One of the most time-consuming processes Company X faces is generating a set of data for each of a few customers, with each customer wanting the data in a slightly different format. The customers can all handle electronic data, but each insists that the data be in a different, specific format such as flat files, XML streams, EDI formats, and so on.
Figure 1, page 46, shows an overview diagram of one approach Company X might take to automate the movement of data among the customers and vendors. The top of the diagram shows the requesting server issuing the data request through HTTP. This server or user submits a URL in the following format:
www.xco.com/secure/ReqProc.asp?CustID= ALFKI&PasswordRetrieveData=123BK23YY
The request-processing code takes the CustomerID field CustID from the URL and looks up that customer in the Northwind Customers table. If the customer's password in the Customers table (PasswordRetrieveData) matches the one submitted, the exchange continues.
To process the data, the ASP page ReqProc.asp extracts the data from the Products table in SQL Server and generates an XML stream. The ASP page then processes the XML stream by using the MSXML object and XSL Transformations (XSLT). The ReqProc.asp ASP page uses a particular customer's XSLT stylesheet to process the incoming stream from the database. Then, the parser sends the resulting XML stream as output from ReqProc.asp across HTTP to the requesting server.
The use of XSLT is essential to making this process work. XSLT uses a declarative format to specify the parser's actions against an XML document or stream and the format of the output data. XSLT's flexibility is the key to exchanging data, letting you output different data formats from one incoming XML stream. (You can find more information about XSLT at http://www.w3.org/tr/xslt.)
Setting Up the Architecture
To begin, you need to create an architecture to support the application. You need to decide how to authorize access to the Web service and how to handle the various customer requirements, such as different output formats or security. Both of these items are easy to implement, and you can use this sample application with any version of SQL Server or ADO, even if they can't natively generate XML.
To control access to the service, I added a PasswordRetrieveData column to the Customers table in the Northwind database. This column stores a password for each customer who can access the service. If a customer entry contains no password in this column, that customer can't access the service. Of course, I could have placed the password in another table or used an entirely different approach to security. You can handle it in a way that works for you. For example, you can enter Company X in your Microsoft Active Directory (AD). Then, using the service, you can create functions to authenticate other applications by checking AD for their credentials.
Building the XSLT Structure
In this article's samples, I used XSLT to transform the data coming from SQL Server. If you do a Web search on XSLT, you'll find a huge amount of data but not much information. Almost all the XSLT data that I found was geared toward using XML and XSLT to generate HTML or XML that ends up in a browser. This article's sample application uses these technologies to generate XML in a stream format that an automated reader can process. Most work in the business-to-business (B2B) world involves XML streams that organizations use for exchanging data with other businesses.