Flexible Data Exchange

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.

Looking at the sheer volume of data about XSLT, you might think you need to be a rocket scientist or code junkie to make XSLT work, but you don't have to be an XSLT expert to get the job done with the minimalist, practical approach to XSLT that I show in this article. First, consider that XSLT builds on XSL, which is a stylesheet language for XML files. An application usually specifies XSL files to a parser, which uses the file's XSL declarations and script to process XML data. You can think of XSL as a sort of controller that determines how to render XML. The result of the parser's XSL-to-XML process is an output file or stream that conforms to the XSL declarations and script.

You can work with XSL in a couple of ways. You can start by using Notepad or a text editor to generate XSL, then test the XSL in your application's code. But a better approach is to use a tool that lets you test the XML and XSL without accessing the database or having to run your application each time. Without one of these tools, working with XML can be exasperating because of its idiosyncrasies. For example, XML is case-sensitive. If you use a text editor to build XML or XSL files, you risk using incorrect case and thereby creating code that doesn't work.

XSLTester is an open-source tool that you can download from http://www.vbxml.com. Figure 2 shows this tool with the incoming XML in the left pane and the resulting XML after parsing in the right pane. To use my test XML with XSLTester, I created part of the ASP code for this article's application. This code generated an XML stream that I then saved to a file and used with the XSLT file. Using Microsoft Internet Explorer (IE) 5.5, I copied the XML from the output stream into XSLTester's XML Document window. I used this output as a working XML file for testing.

Next, I opened the Style Sheet view by selecting View, StyleSheet Document. I added Listing 1's XSLT code for the customer to this pane, then saved the stylesheet as ProductsALFKI.xsl. (ALFKI is the CustomerID for the test customer.) In the XSLT code, all XSL files are stored in XML, as Listing 1 shows. The XSLT code starts with the standard XML declaration, then declares the XSL namespace. The next code section does all the work of transforming the document. The match = "/" statement tells the parser to process all elements in the incoming XML. The next line outputs the starting tag, which is the root tag for the output document.

Next is the for-each declarative block of code. The parser executes the statements in the block once for each element in the SELECT part of the statement. In this case, all Product elements under the Products root are selected. The code then outputs the Product tag for the current element.

The next few lines output each of the elements that have a product. The format is the same for each element. The first part of the statement outputs the element tag name. Then the value-of select statement selects the particular element to output and extracts its value. The closing element tag completes the statement. You can see the format in the ProductID statement:

For a ProductID with a value of 18, you'd get the output

18

The remainder of the script closes all tags, including the and tags and the XSL tags.

After you create and save the XSLT code, you're ready to test it. You can use a tool like XSLTester to test and tweak your XML and XSL/XSLT code. When you're satisfied that the code is correct, you can implement it programmatically, as you'll see in a moment.

To test the XSLT code, click Transform on the XSL-Tester toolbar. XSLTester processes the XSLT against the XML file and shows the output in the Transform Output window. If your XML or XSL/XSLT code contains any errors, the Transform Output window reports them.

You can apply different XSLT files against the same XML and get different results. In the case of Company X, we need to output several different XML streams for different customers. In fact, we don't know how many streams we might need over time or in what formats.

To handle the various customer requests, the application uses a separate XSLT file for each user. The XSLT files are stored in the Web site's XSLTCustomer folder. Each XSLT file is named Productsxxxxx.xsl, where xxxxx is the CustomerID field. For example, the customer with the ID of ALFKI would have an XSLT file called ProductsALFKI.xsl. This filename separates the XSLT code for each customer, and the Products prefix separates the XSLT code for Products.

Listing 2 shows the XSLT for the CustomerID BOLID. The only difference between the XSLT files in Listings 1 and 2 is that Listing 2 doesn't have the tag. As a result, the XML output for BOLID doesn't have a UnitsOnOrder element.

These two XSLT files show that you can tailor the output from a database without touching the database or any other code. Just change the XSLT file, and you can have a new format for each customer.

Creating the ASP

So, how do you tie the XSLT code and the ASP code together? That's where a standard ASP script comes in. In the sample application, I created a reference to ADO in Visual InterDev. This reference puts a metatag in the global.asa file referencing the ADO type library, allowing the use of ADO constants in my script. Listing 3 shows the ReqProc.asp code, which handles all processing and management of the data-transformation process. A customer simply passes in a URL to receive a custom-tailored XML stream.

The ASP code in Listing 3 begins at callout A by declaring some variables. I commented out the Response.Buffer property during testing. By default, Internet Information Services (IIS) 5.0 turns buffering on, which results in unclear error messages. During development, I usually turn off buffering in the page, then turn it back on by commenting out the line when I know the code works. Also, note that the code sets the Response object's ContentType property to return XML, instead of the default HTML, with the statement

response.ContentType = "text/xml"

This statement is important because the application is sending not HTML but a text stream.

Callout B in Listing 3 shows standard ADO code for accessing the database. In the production application, this script (including the ADO code) will be a COM object that provides all the database support for the application. The CheckCustomer function at callout C verifies that a CustomerID is valid. If either the CustomerID or password is invalid, the function returns a value of false and the application doesn't return the XML stream. The code at callout D dimensions the main variables for the application and opens the Recordset. This code is generic for ASP and ADO.

The real work starts at callout E in Listing 3. For reasons that I address later, I chose to create the XML in script rather than use ADO or SQL Server XML features. The first step in generating the XML is to output the XML tag, which is the first line in callout E. The rest of the code in callout E loops through the Recordset and builds the resulting XML stream by concatenating the XML tags and the values from the Recordset.

The first line of code at callout F in Listing 3 stores the XML stream in the sourceFile variable. Then, the code creates the path for the XSLT file to use for the current CustomerID. The server object's MapPath method returns a physical path to the XSLT file, then instantiates the XML parser that will handle the XML stream. Next, the code sets the async property of the parser object to false to force synchronous processing of the XML document. (With synchronous processing, the parser processes the request completely before returning control to the program.) Finally, the code loads the XML stream into the parser object.

The next step is loading the XSLT file. First, the code instantiates another instance of the parser and sets the async property to false. Different naming conventions clearly separate the XML and the XSLT objects.

The code loads the XSLT file exactly like the XML stream, except that this time it uses the Load method because the XSLT code is in a file instead of a variable. The next line does the bulk of the work in the application by calling the transformNode method of the XML parser object (the instance containing the XML) and passing in a reference to the StyleSheet object. The variable sOutPut now contains the new XML stream.

To complete the XML output stream, the next line of ASP code prefixes the XML declaration to sOutPut. First, the encoding parameter specifies the character-set encoding of the XML to let the parser know how to interpret the data. Finally, the vbCrLf constant adds a line break in the XML output for readability.

The code at callout G in Listing 3 is the main body of the application. First, it retrieves the incoming CustomerID and passwords from the Request object. Then, the If statement calls the CheckCustomer() function to check whether the customer is valid and authorized to use the service. If CheckCustomer() is true, the code calls ProcessXML to extract the data from the database and generate the XML. Then, the code uses Response.Write to write the output. Listing 4 shows the HTML code of a simple test page I used during development to execute the Web service.

Custom Made

Let me point out two things about this article's example. First, because it doesn't use ADO or SQL Server to generate the XML, you can use this code with any version of SQL Server and with versions of ADO that don't have XML-generation features. Also, I've found that the database server is often the bottleneck in an application. Although SQL Server 2000 could generate the XML, putting the XML in the ASP code lets you run the service in a farm of servers without dragging down the database server. And, this code can generate data in many XML formats and even non-XML formats, which is a criteria of one of our clients.

Second, although the ASP/ADO code isn't the fastest way to do things, not every application needs to run the fastest. You can optimize the ASP/ADO code by placing it in a COM object, using stored procedures rather than SQL, and not returning Recordsets when you don't need them. Of course, with any application you must perform real-world testing.

XML and XSLT are powerful and easy to use in a data-exchange environment, and you don't need to be an XML or XSLT guru to use them. This solution isn't designed to handle large data streams for thousands of users, but it works well for smaller data streams and hundreds of users. And if you need to add a layer of security to the application, you can place the ASP file in a directory secured with Secure Sockets Layer (SSL). Using SSL encrypts the ASP code's output, so the receiver of the output must also use SSL when retrieving the data from the service.