Using FOR XML and OPENXML to Publish and Process XML Data
You can execute SQL queries to return results as XML instead of standard rowsets. These queries can be executed directly or executed from within stored procedures and user-defined functions. To retrieve the results directly, first use the FOR XML clause of the SELECT statement. Then, within the FOR XML clause, specify an XML mode: RAW, AUTO, EXPLICIT or PATH.
For example, the following
SELECT statement retrieves information from the
Sales.SalesOrderHeader tables in the
AdventureWorks database. This query specifies the
AUTO mode in the
FOR XML clause:
USE AdventureWorks GO SELECT Cust.CustomerID, OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status, Cust.CustomerType FROM Sales.Customer Cust INNER JOIN Sales.SalesOrderHeader OrderHeader ON Cust.CustomerID = OrderHeader.CustomerID FOR XML AUTO
Whereas you can use the FOR XML clause to retrieve data as an XML document, you can use the Transact-SQL OPENXML function to insert data represented as an XML document. OPENXML is a rowset provider similar to a table or a view, and provides a rowset over XML documents that are in memory. OPENXML allows access to XML data as if it were a relational rowset, by providing a rowset view of the internal representation of an XML document. The records in the rowset can be stored in database tables. OPENXML can be used in SELECT and SELECT INTO statements where a source table or view can be specified.
The following example shows the use of
OPENXML in an
INSERT statement and a
SELECT statement. The sample XML document contains
sp_xml_preparedocument stored procedure parses the XML document. The parsed document is a tree representation of the nodes (elements, attributes, text, and comments) in the XML document.
OPENXML then refers to this parsed XML document and provides a rowset view of all or parts of this XML document. An
INSERT statement using
OPENXML can insert data from such a rowset into a database table. Several
OPENXML calls can be used to provide a rowset view of various parts of the XML document and process them, for example, by inserting them into different tables. This process is also referred to as shredding XML into tables.
In the following example, an XML document is shredded in a way that
<Customers> elements are stored in the
Customers table and
<Orders> elements are stored in the
Orders table by using two
INSERT statements. The example also shows a
SELECT statement with
OPENXML that retrieves
OrderDate from the XML document. The last step in the process is to call
sp_xml_removedocument. This is done in order to release the memory allocated to contain the internal XML tree representation that was created during the parse phase.
-- Create tables for later population using OPENXML. CREATE TABLE Customers (CustomerID varchar(20) primary key, ContactName varchar(20), CompanyName varchar(20)) GO CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime) GO DECLARE @docHandle int DECLARE @xmlDocument nvarchar(max) -- or xml type SET @xmlDocument = N'<ROOT> <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1"> <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/> <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/> </Customers> <Customers CustomerID="XYZBB" ContactName="Steve" CompanyName="Company2">No Orders yet! </Customers> </ROOT>' EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument -- Use OPENXML to provide rowset consisting of customer data. INSERT Customers SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers') WITH Customers -- Use OPENXML to provide rowset consisting of order data. INSERT Orders SELECT * FROM OPENXML(@docHandle, N'//Orders') WITH Orders -- Using OPENXML in a SELECT statement. SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime) -- Remove the internal representation of the XML document. EXEC sp_xml_removedocument @docHandle
The following illustration shows the parsed XML tree of the previous XML document that was created by using sp_xml_pareparedocument.