The code in Listing 3 takes advantage of ADO 2.6 and the SQL Server OLE DB provider's ability to accept an Output Stream parameter. This parameter's value can be any object that provides an IStream or ISequentialStream interface. Because the ASP Response object provides an IStream interface, you can pass the Response object to ADO, letting the transformed query result stream directly to the client. Listing 4 shows the XSL stylesheet that transforms the output into an HTML table.
Deciding When to Use XML
Can you give me some criteria for deciding when I should use SQL Server 2000's XML features?
Many readers have asked how to determine XML's relevance to their organizations and tasks. XML itself doesn't solve business problems; XML is an enabling technology that provides a ubiquitous, cross-platform way to exchange data.
Most applications exchange data. Viewing Web pages in a browser, transmitting purchase orders between companies, or getting a stock quote from your favorite Internet startup company all require two or more parties to exchange data. The parties must agree both on the protocol to use to exchange data and on that data's format. XML's inherent properties make it a natural format for the data. For example, you can use XML as a replacement for EDI in cross-enterprise data exchange. Writing programs that use the tag-based XML format is easier, more flexible, and more extensible than using a position-based format such as EDI. XML is also more cost-effective than a position-based format because you can transmit XML across the Internet instead of through expensive value-added networks (VANs), many of which charge per-character transmission costs. XML simplifies such tasks as producing a purchasing system that services a company and its vendors and creating a sales reporting functionality that aggregates data across multiple divisions. XML makes such systems ubiquitous across platforms and easier to build and maintain than they were without XML.
These tasks will become easier still as second-generation XML technologies—which have more integrated tools and operating environments—come to market. New technologies such as Universal Description, Discovery, and Integration (UDDI), Web Services Description Language (WSDL), Simple Object Access Protocol (SOAP), and Microsoft .NET provide higher-level abstractions on top of XML's enabling technology. These technologies, and others to come, provide common programming abstractions that use XML. A programming abstraction lets you use XML as the data-transmission format without having to write code to produce the appropriate format.
But you don't need to wait for next-generation technology. XML-based Web services exist today. AccuWeather, for example, provides local weather forecasts in XML format. Screaming Media generates news feeds in XML format. Your favorite Web sites might use XML and Extensible Style Language Transformations (XSLT) to generate their content dynamically. Your local bank or credit union might even provide your account statement in XML format. If your work involves the exchange of data, XML is an ideal choice for the data format.
Specifying a VB Data Type for Passing an XML Document as a Parameter
Which data type should I specify in Visual Basic (VB) when passing an XML document as a parameter to a SQL Server stored procedure?
In July 2001, I examined how you use an XML template with an ADO CommandStream when passing an XML document as a parameter to a SQL Server stored procedure. Although this technique works well for stored procedures that return a resultset, you need a different technique if the stored procedure returns just a return value (e.g., the number of records inserted into the database).
An alternative to using ADO CommandStream is to pass the XML document as a parameter. For this approach, load the XML document into a string, create an ADO parameter, and assign the string to the parameter. The type of ADO parameter you create depends on the data type that the stored procedure uses when it declares the parameter. Choose the wide-character (Unicode) variations of the parameter types—adWChar, adVarWChar, and adLongVarWChar—because VB and most scripting languages provide support for Unicode strings. Table 1 shows the mappings between the SQL Server, OLE DB, and ADO data types that you use to pass a parameter to a stored procedure. The most flexible choice is to use ntext as the parameter's data type in the stored procedure because ntext accepts Unicode text and strings (e.g., XML documents) of arbitrary size. Listing 5 shows the VB sample code to create an ADO parameter and pass it to a stored procedure. (The downloadable vesion of this listing, available at http://www.sqlmag.com, includes a VB project and code that lets you pass XML documents as parameters. For download information, see "More on the Web.") Listing 6 presents the sample runOpenXMLwithReturn stored procedure that the VB program calls.
End of Article
Prev. page
1
[2]
next page -->
