2 - Retrieving XML Data Using Transact-SQL

In Chapter 1, I described the use of XML in business integration solutions and the relationship between relational data and XML documents. Now let’s turn our attention to extracting data from Microsoft SQL Server in XML format.

Most database application developers are accustomed to retrieving large sets of data from a database server in a rowset format, such as a Microsoft ActiveX Data Objects (ADO) recordset. In a typical application, a SQL SELECT statement is used to select rows from one or more tables in the database and return those rows to the client for processing. SQL Server 2000 extends the SELECT statement to enable the retrieval of data as XML.

The ability to extract SQL Server data as XML is extremely useful in a number of scenarios. Most important, the data is retrieved in a neutral format, which is essential for the creation of business integration solutions in which business documents might need to be exchanged between different systems and different organizations. In this chapter, I’ll show you how the developers of the Northwind Traders’ e-commerce solution can extract order data to generate XML invoices that can be sent to customers over the Internet.