Sdílet prostřednictvím

  • Článek

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.Customer and 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 <Customers> and <Orders> elements.

First, the 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 CustomerID and 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_preparedocument.

Parsed XML tree