Статья
09/28/2010

Использование предложений FOR XML и OPENXML для публикации и обработки XML-данных

Можно выполнять запросы SQL, возвращающие результаты в формате XML, а не в виде стандартных наборов строк. Эти запросы могут выполняться как напрямую, так и из хранимых процедур или пользовательских функций. Для получения результатов напрямую сначала необходимо использовать предложение FOR XML инструкции SELECT. Затем необходимо задать режим XML внутри предложения FOR XML: RAW, AUTO, EXPLICIT или PATH.

Например, следующая инструкция SELECT получает данные из таблиц Sales.Customer и Sales.SalesOrderHeader базы данных AdventureWorks. В этом запросе задается режим AUTO в предложении FOR XML:

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

В то время как предложение FOR XML можно использовать для получения данных в виде XML-документа, для вставки данных в виде XML-документа используется функция Transact-SQL OPENXML. Функция OPENXML является поставщиком набора строк, подобно таблице или представлению, она предоставляет набор строк, соответствующий находящимся в памяти XML-документам. Функция OPENXML обеспечивает доступ к XML-данным как к реляционному набору строк, предоставляя представление внутренней структуры XML-документа в виде набора строк. Записи в наборе строк могут храниться в таблицах базы данных. Функцию OPENXML можно использовать в инструкциях SELECT и SELECT INTO, позволяющих указать таблицу или представление, являющихся источником данных.

В следующем примере показано применение процедуры OPENXML в инструкции INSERT и инструкции SELECT. Образец XML-документа содержит элементы <Customers> и <Orders>.

Сначала вызывается хранимая процедура sp_xml_preparedocument для проведения синтаксического анализа XML-документа. В процессе синтаксического анализа создается древовидное представление узлов (элементов, атрибутов, текста и комментариев), входящих в XML-документ. Затем OPENXML ссылается на этот проанализированный XML-документ и формирует представление наборов строк для всех частей XML-документа. Инструкция INSERT, использующая функцию OPENXML, может вставлять данные из такого набора строк в таблицу базы данных. Можно вызывать функцию OPENXML несколько раз, получая и обрабатывая представление в виде набора строк различных частей XML-документа. Например, их можно вставить в различные таблицы. Данный процесс также называют разделение XML-данных по таблицам.

В следующем примере XML-документ разрезается таким образом, что элементы <Customers> сохраняются в таблице Customers, а элементы <Orders> сохраняются в таблице Orders с помощью двух инструкций INSERT. Этот пример также демонстрирует инструкцию SELECT, использующую функцию OPENXML, которая получает элементы CustomerID и OrderDate из XML-документа. Последним шагом обработки является повторный вызов процедуры sp_xml_removedocument. Это позволяет освободить память, выделенную для внутреннего древовидного представления XML, создаваемого в фазе синтаксического анализа.

-- 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

На следующем рисунке показано XML-дерево, полученное в результате анализа предыдущего XML-документа и созданное с помощью хранимой процедуры sp_xml_preparedocument.

Проанализированное дерево XML