We will investigate how we can build a business application that’s completely
XML-enabled. We will look at each tier of the three tier application model and
discuss our options to leverage XML in the most efficient way; not only in terms
of performance, but also with respect to the development effort and code
The data tier is where we encapsulate all the details of data access. In our
case we architect it to interface with the rest of the application only through
XML. Our data layer is going to return query results in XML and accepts XML
documents to insert them into a SQL Server 2000 database to avoid any
unnecessary conversions in the business layer. But before we start studying
different approaches to architect a data access layer we give a brief overview
of SQL Server’s built-in XML support, the SQLXML library and the managed .Net
classes to access SQLXML.
The release of SQL Server 2000 started adding features to integrate XML with
the relational world. The goal was to simplify development of XML-enabled
applications and data exchange over the internet (what else?). Microsoft has
been adding more features to this list, but will look at those separately.
Initially SQL Server 2000 shipped with the flowing XML related features:
SQL language extensions to retrieve XML formatted data directly from the
SQL language extensions to execute SQL queries against XML documents.
Support for exposing data in SQL Server through IIS. You can return data
from in XML format as a response to HTTP requests, sort of as the forerunner of
SOAP web services.
A SQLOLEDB OLE DB provider to improve performance when executing XML queries
XML Views of relational data defined by schemas in the XDR format.
XPath queries to retrieve XML formatted data from the database.
Execution of XML query templates with optional XSL transformations.
Discussing all of these features in depth is beyond the scope of this book.
We will explore how we can retrieve XML directly from SQL Server and insert data
from XML documents in the following sections, but we have before we move on to
the SQLXML web release and the managed classes to access data in SQL Server. We
have to skip features enabling SQL Server access over HTTP because ADO.NET and
the managed classes in SQLXML provide more flexible and re-usable means to
access a database. We will also not discuss XML views defined by XDR schemas,
since they are superceded by views defined by XSD schemas. Check the SQL Server
Books online documentation on the MSDN web site, or install it from your SQL
Server CD if you are interested in these features.
The SQL/XML, or XML-Related Specifications, extension to the SQL standard is defined by ISO/IEC 9075-14:2003 (see SQL:2003). SQL/XML specifies SQL-based extensions for using XML in conjunction with SQL. The XML data type is introduced, as well as several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL database.
The SQL/XML specification includes functions to construct XML data. These functions allow the user to construct new XML elements or attributes with values e.g. from relational tables. Other functions such as XMLCONCAT or XMLAGG can be used to combine small XML fragments into larger ones. The list of available construction functions includes:
SQL/XML also defines functions which allow the user to embed XQuery expressions in SQL statements. These functions include:
While XMLQUERY returns values of type XML, the function XMLTABLE can take XML data as input and produce a relational table as output. Predicates on XML data, such as search conditions, can be expressed with the XMLEXISTS predicate, typically in the WHERE clause of a SQL statement.
Further information and examples of the SQL/XML functions are provided in the external links below.