This article throroughly describes a very important issue of integrating XML with SQL Server. It describes all the techniques and syntax in details with a small example.
SQL Server 2000 has introduced features, such as URL queries and XPath queries, for integrating XML with SQL Server. Some of these features are: Xpath Queries, Direct URL Queries, OPENXML funtion, and FOR XML clause. In this article, I would try to describe these features in short.
The FOR XML clause of the SELECT statement:
SQL queries are used to query the database and display data as a rowset. SQL Server enables you to display data in XML format also. SQL Server provides the SQL-XML mapping rules for extracting XML data from a database and storing XML data in the database. FOR XML clause of the SELECT statement is used to extract data from a database and display it as an XML document.
You can retrieve data in three different modes by using this clause namely: AUTO, RAW AND EXPLICIT.
The Syntax is as follows:
SELECT table1.column1, table2.column2 FROM table1, table2 FOR XML mode
The AUTO mode returns the query results as nested XML elements. Each table in the FROM clause, which has a column listed in the SELECT statement is represented as an XML element. The columns are mapped as attributes to the respective elements.
The RAW mode takes the query results and transforms each row into an XML element with a generic row identifier as the element tag. This mode maps the columns that have non-null values to an attribute of an XML element. The name of the attribute is the same as that of the column.
The EXPLICIT mode query produces a univrsal table that contains all the information about the resultant XML tree. If EXPLICIT mode is specied, then you must ensure that the generated XML document is well-formed and valid.
The URL Query:
A URL Query allows to embed a SELECT statement along with the URL to generate and XML document with the required data.
The syntax of the url query is as follows:
http:/// ?sql= &root=root-element-name
where,
<server-name> is the name of the web server
<virtual-directory> is the name of the virtual directory configured on the web server
<sql-statement> is the SELECT statement with the FOR XML clause. WHERE clause can also be used.
root=root-element-name defines the root element in the XML document
The OPENXML Function:
The OPENXML Function is used to retrieve the data from the XML document as a rowset. As OPENXML is a rowset function, it can be used in T-SQL statements. SQL Server provides the following system procedures that can be used with the OPENXML function.
sp_xml_preparedocument
sp_xml_removedocument
The sp_xml_preparedocument system stored procedure is used to create an internal representation of the XML document which can be used to insert data into a database. This procedure returns a handle that can be used to access the internal representation of the XML document. On the other hand, sp_xml_removedocument is used to remove the internal representation of the document.
The following examples explains this:
Say you have a sales table. The following code which is quite self-explanatory will insert xml data in the sales table.
declare @idoc int
set @doc = ‘
exec sp_xml_preparedocument @idoc, output, @doc
select * from openxml(@idoc, ‘/root/sales’, 1)
with (orderid char(4),
productname varchar(30),
ship_add varchar(40);
price_per_qty money,
quantity int)
insert sales
select * form openxml(@idoc, ‘/root/sales’)
with sales
exec sp_xml_removedocument @idoc