REDMOND, Wash., June 13, 2001 — One of the hottest topics in the information technology industry is XML (eXtensible Markup Language). The Microsoft SQL Server 2000 relational database engine natively supports XML. But what does XML support in SQL Server mean for IT professionals?
In a relational database such as SQL Server 2000, all operations performed on the database use what is known as a SELECT statement to produce a result in the form of a table. Traditional client/server applications that execute a SELECT statement process the results by fetching one row or block of rows from the tabular result set at a time, then mapping the column values into program variables.
Web-application programmers, on the other hand, are more familiar with hierarchical representations of data in XML or HTML documents. XML integration in SQL Server 2000 allows programmers to send and receive information in XML format and to make updates, insertions and deletions in the SQL Server database using XML.
Microsoft has introduced several new features to provide XML support: the ability to access SQL Server over HTTP through a URL; support for XML-data schemas and the ability to specify XPath queries against those schemas; and the ability to retrieve and write XML data.
How XML Puts the
“
E-
”
in Commerce
XML, besides having become accepted as the standard technology for interchanging data on the Internet, is quickly becoming the preferred way to integrate e-commerce systems. Companies building business-to-consumer, business-to-business, and extranet Web solutions look to XML to simplify the integration of back-end systems and to provide a means to transfer data through firewalls.
“E-commerce solutions put heavy demands on software, and the database is no exception,” says Michael Rys, a SQL Server program manager at Microsoft. “High transactional loads are the norm as users from all over the world access sites around the clock. Programmatic flexibility is critical to ensure interoperability with existing systems and the ability to respond quickly to changing business requirements.”
SQL Server 2000 provides extensive database programming capabilities built on Web standards, while at the same time delivering the scalability and availability required for e-commerce.
Many companies have looked to middle-tier XML solutions to solve their data communication problems, and developers have also realized the value of high-speed storage and generation capabilities of XML documents and data.
“Microsoft SQL Server 2000 offers integrated, best-of-breed XML support that is flexible, high-performance and easy to use for Web developers and database programmers alike,” Rys says.
Consider a hypothetical example of a typical business-to-business application that employs SQL Server. The application accepts purchase orders from business partners over the Web, consisting of two major pieces: an Internet Information Services (IIS) Web server running the SQL Server 2000 middle-tier components and a database server running SQL Server 2000.
IIS are the Web services built into the Microsoft Windows 2000 Server operating system. In a typical transaction, a business partner sends an XML message with the purchase order to the Web server. The Web server translates the message into a call to a stored procedure in the database. The procedure uses the XML functions of SQL Server 2000 to validate the purchase order and enter it into the database. The procedure then returns an XML result with details of the transaction, such as the receiver’s reference number for the order. The Web server wraps this result in an XML message that it returns as a response to the business partner.
NATO Unified with SQL Server 2000 XML Support
A real-world example of the power of SQL Server 2000 and XML can be found in a recent implementation at NAMSA, the North Atlantic Treaty Organization’s Maintenance and Supply Agency. NAMSA is NATO’s primary logistics support management organization, and recently inaugurated its eBid online procurement system.
The eBid business-to-business system was built in only three months using Windows 2000 Server and SQL Server 2000 for the database. Microsoft Commerce Server 2000 defines and links different stages of the procurement process enabling tailored processing of orders.
Data is described in XML and presented using eXtensible Stylesheet Language (XSL), which makes it possible to communicate and transfer data easily with other systems. These standards are particularly useful for online procurement because eBid can transfer a great deal of information about transactions — suppliers’ names and addresses, order numbers and payment details, etc. — without the laborious task of converting all the data.
The system — which is centered on the open IP Security Protocol, and which uses Windows 2000 Active Directory and Distributed Security Services — allows qualified vendors to do business with NAMSA over the Internet in a simple and secure manner, 24 hours a day, seven days a week.
All of NAMSA’s supply requests for proposals (RFPs) are put on the system — about 3,000 every month, for anything from nuts and bolts to AK-47 barrels. Some 10,000 firms are expected to use eBid. To access the system, a supplier logs on to a Web site to register and, if eligible, is issued a user ID and password. From there, a company can look at the RFPs and make an offer.
“Now the procurement people are able to redirect the time they spend faxing and manually handling this work to actually analyzing how they can improve the procurement process and focus on negotiating larger contracts,” says Jedediah Grant, the eBid Project Manager.
NAMSA can expect to save the equivalent of about US$38,000 a month in staff time and just under $1,000 in fax and telephone costs, meaning that the overall investment will pay for itself within five months.
Suppliers also like the system because it adds convenience and creates more opportunities to do business with NATO, as they can see all RFPs in one location. More participants in the system are expected to drive down the cost of supplies for NATO and the national armed forces. Grant describes it as a “win-win-win” situation for buyers, suppliers and customers.
Some Specific Features of XML
The rich XML functionality in SQL Server 2000 shields Web developers from the intricacies of relational database programming; instead, they use technologies such as URL queries, XML updategrams and XPath.
The HTTP access to SQL Server allows developers to specify SQL queries directly in the URL. Using the FOR XML clause returns the result as an XML document instead of a standard rowset.
“SQL Server 2000 can provide an XML view of relational data as well as map XML data into relational tables,” Microsoft’s Rys says. “Whereas XML Views enable relational tables to be accessed as if they were XML documents, OpenXML allows XML documents to be addressed with relational SQL syntax.”
SQL Server 2000 supports an OPENXML clause in queries that extracts information contained in XML documents. This feature can make it easier to write applications that pull information from XML documents that are stored in database tables, or that compare data in XML documents with other table data.
On receiving an XML-formatted purchase order, for example, an application could extract data such as customer identity and quantity, and write them into a row in the database. More generally, the feature could make it easier to build knowledge management applications that log and retrieve structured documents, such as engineering change management and bug-tracking systems.
The newest XML feature, XML updategrams, is a way to specify an update to a SQL Server 2000 database through XML.
“You specify what the XML data looks like now and what you want it to look like when the updategram is executed,” Rys says, “and the updategram processor automatically generates and executes the SQL statements required to produce the desired change.”
Updategrams can be used to replace existing data access components in a middle tier, which typically consists of business logic and data access code. This code interfaces with the database using disconnected record sets and command objects that call SQL Server stored procedures.
Typically, most data access tiers individually deal with specific database tables or groups of related tables — inhibiting performance and often requiring several round trips to the database to complete a transaction. By including all of the data in an XML document that is mapped to database tables and columns, the entire database update can be accomplished in a single process that can include inserting, updating and deleting data.
SQL Server also supports the XSL Internet standard. An XSL style sheet can remove, insert and reorder elements of an XML document in its presentation. XSL includes a query language called XPath for extracting information from XML source documents. The data access components delivered with SQL Server 2000 support XPath as an alternate query language (alongside SQL). XPath queries allow a developer to select a particular element or set of elements from a document; for example, selecting all the “line item” elements in a purchase order document.
SQL Server 2000 provides XML and Web support through both the database server itself and middle-tier components that can run on the IIS Web server. The database server can build XML documents and extract information from them, and the middle-tier components let Web browsers and applications send queries to the database server and get XML or HTML documents in response using standard HTTP.
