Over the past few years, XML and the languages/vocabularies defined with XML have established themselves as the most prevalent and promising lingua franca of business-to-business, business-to-consumer, or generally, any-to-any data interchange and integration. One of the major reasons for the emergence of XML in this space is that XML is a simple, platform-independent, Unicode-based syntax for which simple and efficient parsers are widely available. Another important factor in favor of XML is its ability to not only represent structured data, but to provide a uniform syntax for structured data, semi-structured data (data that is sparse or is of heterogeneous types), and marked-up content.
A large amount of the data that is interchanged originates from relational databases and is finally stored again in relational databases, since they have a proven track record in providing the required efficient and flexible management of data in such usage contexts.
Since relational systems today are predominantly used to manage structured data (an educated guess would be 80 percent or more), most of the XML generated and being consumed at this time in the context of data interchange is also fitting the relational model of structured data. Therefore, relational systems have first focused on providing XML capabilities that fit the most common usage scenario while still providing some support for dealing with data that does not fit into the structured mold. For example, Microsoft SQL Server 2000 provides the mechanisms to easily and efficiently transform relational schemata into a specific XML language (i.e., schema) used for the data transport and back into relations.
This chapter focuses on how Microsoft SQL Server 2000 provides the basic technology to enable a relational database to become a component in an application scenario that uses an XML-based data interchange. It will provide an overview of the features that are needed to provide database access, that transform relational query results into XML, and how to elegantly provide rowset abstractions over XML to shred XML into relations. Next it will present the mechanism that provides queryable and updateable XML views that also can be used to efficiently bulk load XML data into the relational database. The chapter will discuss how these features can be used in the context of any-to-any data interchange and emphasize the ease-of-use of building the XML views. The discussion of generating XML for SQL queries will describe canonical, heuristic-based, and user-defined strategies and point out their use scenarios and also some of their potential shortcomings.