Вопрос 29: Применение XML в SQL. Применение опции For XML. Использование функции OPENXML. Применение XQuery в SQL.

Применение XML

XML-данные можно хранить на сервере в столбце типа xml.

Ниже риведен пример создания таблицы, в котором присутствует стобец типа xml:

CREATE TABLE T(
    cl int primary key, 
    c2 xml
    )

Опция FOR XML

Запрос SELECT возвращает результаты в виде набора строк. При необходимости можно получать результаты SQL-запроса в формате XML. Для этого в запросе необходимо указать предложение FOR XML. Предложение FOR XML может использоваться в запросах верхнего уровня и во вложенных запросах. Предложение FOR XML верхнего уровня можно использовать только в инструкции SELECT. Во вложенных запросах предложение FOR XML можно использовать в инструкциях INSERT, UPDATE и DELETE. FOR XML также можно использовать в инструкциях присваивания.

Режимы работы опции FOR XML

• RAW - режим, при котором в XML документе создается одиночный элемент для каждой строки результирующего набора данных инструкции SELECT;
• AUTO - в данном режиме структура XML документа создается автоматически, в зависимости от инструкции SELECT (объединений, вложенных запросов и так далее);
• EXPLICIT - самый расширенный режим работы конструкции FOR XML, при котором Вы сами формируете структуру итогового XML документа, за счет чего этот режим самый трудоемкий. Данный режим в основном используется для создания XML документов с очень сложной структурой, которую не получается реализовать с помощью других режимов;
• PATH - это своего рода упрощенный режим EXPLICIT, который хорошо справляется со множеством задач по формированию XML документов, включая формирование атрибутов для элементов. Если Вам нужно самим сформировать структуру XML данных, то рекомендовано использовать именно этот режим.

Параметры конструкции FOR XML:

• TYPE – возвращает сформированные XML данные с типом XML, если параметр TYPE не указан, данные возвращаются с типом NVARCHAR(MAX). Параметр необходим в тех случаях, когда над итоговыми XML данными будут проводиться операции, характерные для XML данных, например, выполнение инструкций на языке XQuery;
• ELEMENTS – если указать данный параметр, столбцы возвращаются в виде вложенных элементов;
• ROOT – параметр добавляет к результирующему XML-документу один элемент верхнего уровня (корневой элемент), по умолчанию «root», однако название можно указать произвольное.

Примеры использования: В этом примере используется режим RAW (режим указывается после ключевых слов FOR XML), а также параметр TYPE:

 SELECT ProductId, ProductName, Price
   FROM TestTable
   ORDER BY ProductId
   FOR XML RAW, TYPE

В следующем примере изменили название каждого элемента на Product, для этого указали соответствующий параметр, добавили параметр ELEMENTS, для того чтобы столбцы были отображены в виде вложенных элементов, а также добавили корневой элемент Products с помощью параметра ROOT.

   SELECT ProductId, ProductName, Price
   FROM TestTable
   ORDER BY ProductId
   FOR XML RAW ('Product'), TYPE, ELEMENTS, ROOT ('Products')

Использование функции OPENXML

OPENXML – это специальная функция, которая извлекает данные из XML документа.

Синтаксис функции:

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )   
[ WITH  ]

Аргументы:

• idoc - дескриптор документа внутреннего представления XML-документа;
• rowpattern - шаблон на языке XPath, используемый для идентификации узлов, которые будут обрабатываться как строки;
• flags - тип сопоставления между XML данными и реляционными, например, 0 – используется атрибутивная модель сопоставления, это значение по умолчанию, 1 — использовать атрибутивную модель сопоставления, 2 — использовать сопоставление с использованием элементов, 8 — используемые данные не должны копироваться в свойство переполнения @mp:xmltext.

После функции, с помощью ключевого слова WITH, указывается формат набора выходных строк, т.е. мы перечисляем столбцы и их тип данных

Пример использования: Чтобы сформировать XML документ, мы используем конструкцию FOR XML. Затем с помощью функции OPENXML мы извлечем данные из полученного документа.

--Объявляем переменные
   DECLARE @XML_Doc XML;
   DECLARE @XML_Doc_Handle INT;

   --Формируем XML документ
   SET @XML_Doc = (
                                SELECT ProductId AS "@Id", ProductName, Price
                                FROM TestTable
                                ORDER BY ProductId
                                FOR XML PATH ('Product'), TYPE, ROOT ('Products')
                                );
   --Подготавливаем XML документ
   EXEC sp_xml_preparedocument @XML_Doc_Handle OUTPUT, @XML_Doc;

   --Извлекаем данные из XML документа
   SELECT *
   FROM OPENXML (@XML_Doc_Handle, '/Products/Product', 2)
   WITH (
          ProductId INT '@Id', 
          ProductName VARCHAR(100),
      Price MONEY
      );
   --Удаляем дескриптор XML документа
   EXEC sp_xml_removedocument @XML_Doc_Handle;

В данном примере в переменной @XML_Doc мы сохранили XML документ, потом мы данную переменную передали на анализ системной процедуре sp_xml_preparedocument, которая вернула нам дескриптор XML документа в переменной @XML_Doc_Handle (если XML документ составлен синтаксически неправильно, выйдет ошибка).

В функцию OPENXML мы передаем переменную с дескриптором, указываем, какой элемент будет являться строкой, в нашем случае каждый элемент Product это строка, а также уточняем, какой метод сопоставления использовать, в нашем случае с использованием элементов.

В секции WITH мы перечислили название и тип итоговых столбцов, для ProductId мы указали дополнительный аргумент ‘@Id’ — это говорит о том, что данный столбец в XML документе является атрибутом Id.

После того как XML документ мы обработали, удаляем его дескриптор процедурой sp_xml_removedocument, передав в нее, соответственно, переменную с дескриптором.

Применение XQuery в SQL.

XQuery - язык запросов, используемый для обработки XML-данных.

Основные методы:

Примеры:

Извлечение данных с помощью query:

SELECT DeliveryDriver, DeliveryList.query('/DeliveryList/Delivery') AS Deliveries
FROM Sales.DeliverySchedule

Извлечение данных и перевод в тип nvarchar с помощью value:

SELECT DeliveryList.value('(/DeliveryList/Delivery/Address)[1]', 'nvarchar(100)') as DeliveryAddress
FROM Sales.DeliverySchedule
WHERE ScheduleID = 1;

Обновление таблицы с помощью modify:

UPDATE Sales.DeliverySchedule
SET DeliveryList.modify('replace value of (/DeliveryList/Delivery/Address/text())[1]
                        with "7194 Fourth St. Rockhampton"')
WHERE ScheduleID = 1;