Использование предложений 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_pareparedocument.
.gif "Проанализированное дерево XML")
См. также
Справочник
Запросы XML с использованием OPENXML
Создание XML с помощью предложения FOR XML
Базовый синтаксис предложения FOR XML
Основные понятия
Тип данных xml
Образцы приложений XML