OPENXML: примеры (SQL)

Базовые примеры использования OPENXML

Пример демонстрирует обработку XML, содержащего атрибуты, с флагом 1 (по умолчанию).

DECLARE @xml XML = '
<Users>
  <User ID="1" Name="Алексей" Age="30"/>
  <User ID="2" Name="Мария" Age="25"/>
</Users>';
DECLARE @hdoc INT;

EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml;

SELECT *
FROM OPENXML(@hdoc, '/Users/User')
WITH (
    ID INT '@ID',
    Name NVARCHAR(50) '@Name',
    Age INT '@Age'
);

EXEC sp_xml_removedocument @hdoc;

ID | Name   | Age
1  | Алексей| 30
2  | Мария  | 25

Пример с флагом 2 для элемент-ориентированного XML.

DECLARE @xml XML = '
<Users>
  <User>
    <ID>1</ID>
    <Name>Алексей</Name>
    <Age>30</Age>
  </User>
  <User>
    <ID>2</ID>
    <Name>Мария</Name>
    <Age>25</Age>
  </User>
</Users>';
DECLARE @hdoc INT;

EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml;

SELECT *
FROM OPENXML(@hdoc, '/Users/User', 2)
WITH (
    ID INT 'ID',
    Name NVARCHAR(50) 'Name',
    Age INT 'Age'
);

EXEC sp_xml_removedocument @hdoc;

ID | Name   | Age
1  | Алексей| 30
2  | Мария  | 25

Пример с комбинированным флагом 3, когда данные могут находиться в атрибутах или элементах.

DECLARE @xml XML = '
<Users>
  <User ID="1">
    <Name>Алексей</Name>
    <Age>30</Age>
  </User>
</Users>';
DECLARE @hdoc INT;

EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml;

SELECT *
FROM OPENXML(@hdoc, '/Users/User', 3)
WITH (
    ID INT '@ID',
    Name NVARCHAR(50) 'Name',
    Age INT 'Age'
);

EXEC sp_xml_removedocument @hdoc;

ID | Name   | Age
1  | Алексей| 30

Альтернативные возможности MS SQL для работы с XML

В современных версиях SQL Server для разбора XML часто используют встроенные методы типа данных XML, которые не требуют подготовки документа через sp_xml_preparedocument и считаются более предпочтительными.

• Метод nodes(): Позволяет разбивать XML на набор строк, каждая из которых соответствует указанному узлу. Работает напрямую с переменными или столбцами типа XML. Пример:

  DECLARE @xml XML = '<Users><User>Алексей</User><User>Мария</User></Users>';
  SELECT T.c.value('.', 'NVARCHAR(50)') AS Name
  FROM @xml.nodes('/Users/User') AS T(c);

• Метод value(): Извлекает скалярное значение из XML. Эффективен для получения одиночных значений.
• Метод query(): Выполняет XQuery-выражение и возвращает результат в виде XML.
• Метод exist(): Проверяет наличие узла или его значения, возвращает 0 или 1.

Эти методы обычно проще в использовании, не требуют управления дескрипторами и менее ресурсоемки. Однако OPENXML может быть полезен в сложных сценариях, где требуется гибкое сопоставление с реляционной схемой или при работе с устаревшим кодом.

Распространенные ошибки при работе с OPENXML

Ошибка, связанная с отсутствием вызова sp_xml_removedocument, приводит к утечке памяти, так как внутреннее представление XML остается в памяти SQL Server.

DECLARE @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT, '<root/>';
-- Забыли вызвать sp_xml_removedocument

Использование некорректного дескриптора после удаления документа вызывает ошибку.

DECLARE @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT, '<root/>';
EXEC sp_xml_removedocument @hdoc;
-- Попытка использовать недействительный дескриптор
SELECT * FROM OPENXML(@hdoc, '/root');

Сообщение об ошибке: Недопустимый идентификатор документа 0.

Неправильный XPath в rowpattern может привести к пустому результату без ошибки.

DECLARE @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT, '<Users><User>Алексей</User></Users>';
SELECT * FROM OPENXML(@hdoc, '/User'); -- Должно быть '/Users/User'
EXEC sp_xml_removedocument @hdoc;

Ошибка при указании неверного типа данных в схеме WITH приводит к невозможности преобразования данных.

DECLARE @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT, '<User ID="не_число"/>';
SELECT * FROM OPENXML(@hdoc, '/User')
WITH (ID INT '@ID');
EXEC sp_xml_removedocument @hdoc;

Ошибка преобразования типа данных.

Изменения в OPENXML в новых версиях SQL Server

В последних версиях SQL Server (начиная с 2005) корпорация Майкрософт рекомендует использовать встроенные методы данных XML (nodes(), value() и др.) вместо OPENXML, так как они интегрированы в ядро СУБД и обеспечивают лучшую производительность.

OPENXML остается поддерживаемой функцией для обратной совместимости. Существенных изменений в ее синтаксисе или поведении в новых версиях не произошло, однако в документации указано, что будущие версии могут ее удалить.

Важным аспектом является то, что OPENXML использует внутренний парсер MSXML, который может иметь отличия от парсера, используемого типом XML. Например, различия в обработке пространств имен или типов данных.

Расширенные сценарии применения OPENXML

Пример обработки XML с пространствами имен. Необходимо указать префикс и URI пространства имен в третьем параметре sp_xml_preparedocument.

DECLARE @xml XML = '
<root xmlns:ns="http://example.com">
  <ns:User ID="1" Name="Алексей"/>
</root>';
DECLARE @hdoc INT;

EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml, '<root xmlns:ns="http://example.com"/>';

SELECT *
FROM OPENXML(@hdoc, '/root/ns:User')
WITH (
    ID INT '@ID',
    Name NVARCHAR(50) '@Name'
);

EXEC sp_xml_removedocument @hdoc;

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

DECLARE @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT, '
<Users>
  <User>Первый</User>
  <User>Второй</User>
</Users>';

SELECT *
FROM OPENXML(@hdoc, '/Users/User', 2)
WITH (
    UserName NVARCHAR(50) '.',
    Position INT '@mp:xmltext') AS MetaProp;

EXEC sp_xml_removedocument @hdoc;

UserName | Position
Первый   | 2
Второй   | 4

Сложное сопоставление, когда XML содержит вложенные структуры, которые нужно преобразовать в плоскую таблицу.

DECLARE @xml XML = '
<Orders>
  <Order OrderID="101">
    <Products>
      <Product ProdID="P1" Qty="2"/>
      <Product ProdID="P2" Qty="1"/>
    </Products>
  </Order>
</Orders>';
DECLARE @hdoc INT;

EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml;

SELECT *
FROM OPENXML(@hdoc, '/Orders/Order/Products/Product')
WITH (
    OrderID INT '../@OrderID',
    ProdID NVARCHAR(10) '@ProdID',
    Qty INT '@Qty'
);

EXEC sp_xml_removedocument @hdoc;

OrderID | ProdID | Qty
101     | P1     | 2
101     | P2     | 1

Пример массового обновления таблицы данных, переданных в виде XML-параметра.

DECLARE @xml XML = '
<Users>
  <User ID="1" NewName="Александр"/>
  <User ID="2" NewName="Мария"/>
</Users>';
DECLARE @hdoc INT;

CREATE TABLE #TempUsers (ID INT, Name NVARCHAR(50));
INSERT INTO #TempUsers VALUES (1, 'СтароеИмя1'), (2, 'СтароеИмя2');

EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml;

UPDATE T
SET Name = X.NewName
FROM #TempUsers T
INNER JOIN OPENXML(@hdoc, '/Users/User', 1)
    WITH (ID INT '@ID', NewName NVARCHAR(50) '@NewName') AS X
    ON T.ID = X.ID;

SELECT * FROM #TempUsers;

DROP TABLE #TempUsers;
EXEC sp_xml_removedocument @hdoc;

ID | Name
1  | Александр
2  | Мария

Аналоги OPENXML в других СУБД и языках

MySQL: Функции для извлечения данных из XML не столь развиты. Часто используют строковые функции или загрузку данных через промежуточное преобразование. Для разбора XML можно использовать функцию ExtractValue, но она предназначена для извлечения отдельных значений.

SET @xml = '<Users><User>Алексей</User></Users>';
SELECT ExtractValue(@xml, '/Users/User') AS Name;

Name
Алексей

Oracle: Предлагает мощные XML-функции, такие как XMLTable, которая преобразует XML в набор строк аналогично OPENXML, но с более богатым синтаксисом XQuery.

SELECT *
FROM XMLTable('/Users/User'
    PASSING XMLType('<Users><User ID="1">Алексей</User></Users>')
    COLUMNS 
        ID VARCHAR2(10) PATH '@ID',
        Name VARCHAR2(50) PATH '.');

ID | Name
1  | Алексей

PostgreSQL: Для работы с XML доступны функции xpath и xmlelement, а также тип данных XML. Функция xpath возвращает массив значений, соответствующих XPath-выражению.

SELECT xpath('/Users/User/text()',
    '<Users><User>Алексей</User></Users>'::XML) AS Name;

Name
{Алексей}

SQLite: Не имеет встроенной поддержки XML. Обычно XML обрабатывается на стороне приложения или с помощью расширений.

Sybase ASE: Поддерживает функцию OPENXML с синтаксисом, аналогичным SQL Server.

Языки программирования: В приложениях на C#, Java, Python и других часто используют специализированные библиотеки для парсинга XML (например, XmlDocument, LINQ to XML в .NET; DOM, SAX в Java; ElementTree в Python), которые предоставляют более гибкие и мощные средства, чем SQL-функции.