Transact-SQL — функции для работы с XML

Сегодня мы начнем рассматривать достаточно полезную возможность SQL сервера от компании Microsoft – это возможность хранить и обрабатывать данные в формате XML. В данном материале мы рассмотрим основные функции Transact-SQL для обработки данного типа данных.

XML очень популярный тип данных, так как данными такого типа достаточно легко обмениваться со множеством различных приложений, поэтому начинающий программист T-SQL должен иметь представление о том, как хранить эти данные и как их обрабатывать на SQL.

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

Итак, приступим, для того чтобы хранить в таблице XML-данные, необходимо выбрать соответствующий тип, он так и называется xml. Запросы будем выполнять в среде Management Studio.

Примечание! Все примеры выполнены в Microsoft SQL Server 2008.

CREATE TABLE [table_test]( [id] [bigint] NOT NULL, [xmldate] [xml] NULL ) ON [PRIMARY] GO

Как видите все просто, мы создали таблицу, в которой два столба:

• Id- идентификатор записи;
• Xmldate – соответственно, какие-то XML данные.

Теперь давайте запишем туда что-нибудь, для этого выполним простой запрос INSERT.

INSERT INTO table_test (id,xmldate) VALUES(1,' Иван Иванов ')

Здесь мы просто вручную разметили xml документ и записали его в нашу таблицу. Для того чтобы просто увидеть данные в этой таблице, xml отображается в читабельном виде, выполните запрос используя оператор select:

SELECT xmldate FROM table_test

Вы получите данные вида

Функции T-SQL для работы с XML данными

Данные функции называют методами, и начнем мы с метода query.

query – делает выборку в самом xml документе, который хранится в нашей таблице, и принимает он один параметр — это строка запроса к xml документу, т.е. что именно Вы хотите получить из xml. Возвращает также данные типа xml. Например, выше мы создали таблицу, в которой корневой тег catalog, а нам, допустим, нужно получить только тег name, для этого выполните следующий запрос.

SELECT xmldate.query('/catalog/name') FROM table_test

и в ответ Вы получите данные вида.

Надеюсь, смысл этого метода понятен.

Далее рассмотрим функцию modify.

modify – как понятно из названия она изменяет данные xml. Принимает один параметр — это собственно сами инструкции по изменению xml документа. Используется в конструкции update.

Для удаления узла используйте запрос.

UPDATE table_test SET xmldate.modify('delete /catalog/lastname')

Здесь мы удалили узел lastname, для проверки используйте запрос select к данной таблице.

Для вставки узла используйте запрос.

UPDATE table_test SET xmldate.modify('insert Иванов as last into (/catalog)[1] ')

Здесь мы добавили узел lastname обратно в каталог. Для проверки используйте тот же самый запрос select.

Для обновления значения в узле используйте следующий запрос.

UPDATE table_test SET xmldate.modify('replace value of(/catalog/name[1]/text())[1] with "Сергей" ')

Здесь в первом узле name (ну он у нас один) мы изменили значение «Иван» на «Сергей».

Переходим к методу value.

value – возвращает значения узла. Очень удобно использовать, когда Вы хотите запросом получить не xml документ, а его значения. Принимает два параметра, первый — это откуда брать значение, а второй — какой тип данных Вы при этом хотите получить.

SELECT xmldate.value('/catalog[1]/name[1]','char(50)') FROM table_test

Иногда требуется запросом из таблицы не с xml данными получить данные xml, это тоже можно сделать, например, в следующем запросе мы записываем в переменную типа XML строку таблицы table с идентификатором 2, а затем просто получаем эти значения.

DECLARE @xml xml; SET @xml=(SELECT ID, test FROM table WHERE FOR XML RAW, TYPE); SELECT @xml; SELECT @xml.value('/row[1]/@ID[1]','int') as id, @xml.value('/row[1]/@test[1]','char(50)') AS text

И еще один пример для наглядности (картинка ниже), он похож на предыдущий, но в нем мы просто подставляем статические данные.

У меня на этом все. Удачи!

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, которые помогут Вам «с нуля» научиться работать с SQL и программировать на T-SQL в Microsoft SQL Server.

Основные понятия о программировании для SQLXML 4.0

Компонент SQLXML 3.0 стал доступен в виде веб-версии. Он обеспечивает дополнительную XML-функциональность на стороне клиента и расширение существующих функций: схемы XSD, пакетную загрузку XML, поддержку веб-служб (SOAP), диаграммы обновления и т. д.

SQL Server 2005 (9.x) появился SQLXML 4.0, который по-прежнему предоставляет те же функции, что и SQLXML 3.0, а также дополнительные обновления, предоставляемые для поддержки новых функций, появившиеся в SQL Server 2005 (9.x).

Этот раздел содержит сведения об SQLXML 4.0.

Новые возможности SQLXML 4.0 с пакетом обновления 1 (SP1)
Содержит описание обновлений и расширений в SQLXML 4.0, а также ссылки на соответствующие разделы в настоящей документации.

Использование ADO для выполнения запросов SQLXML 4.0
Описывает процесс работы с запросами ADO для SQLXML. В SQLXML 4.0 поддержка ADO более заметна, чем в предыдущих версиях.

Поддержка типов данных xml в SQLXML 4.0
Описывает поддержку типа данных xml, которая была добавлена в SQLXML 4.0.

Требования к запуску примеров SQLXML
Описывает требования для создания рабочих образцов из примеров, входящих в состав SQLXML.

Форматирование XML-кода на клиенте и на сервере (SQLXML 4.0)
Содержит сведения о форматировании на стороне сервера и на стороне клиента, а также их сравнение, в том числе сведения о команде FOR XML для создания XML-документов.

Схемы XSD с заметками в SQLXML 4.0
Содержит сведения об использовании аннотированных XSD-схем в SQLXML 4.0. Кроме того, содержит сведения об аннотированных XDR-схемах для использования в традиционных приложениях.

Использование запросов XPath в SQLXML 4.0
Описывает, как пользоваться подмножеством языка XPath для выполнения запросов к XML-представлениям, создаваемым на основе аннотированной XSD-схемы. Кроме этого, содержит примеры.

Использование диаграмм обновления для изменения данных в SQLXML 4.0
Содержит сведения о диаграммах обновления, которые изменяют данные в базе данных применительно к XML-представлениям на основе аннотированных схем XSD (или XDR).

Компоненты доступа к данным SQLXML 4.0
Содержит документацию по поставщику SQLXMLOLEDB и ссылки на другие компоненты доступа к данным SQLXML 4.0.

Кэширование шаблонов, XSL и схем (SQLXML 4.0)
Описывает функции кэширования, обеспечиваемые SQLXML для повышения производительности.

Проблемы безопасности SQLXML 4.0
Содержит обсуждение проблем, относящихся к SQLXML 4.0.

Рекомендации по использованию SQLXML 4.0 и действующие ограничения
Содержит перечень проблем, которые следует учитывать при работе с SQLXML 4.0.

XML-данные (SQL Server)

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

• Тип данных xml . Значения XML можно естественным образом хранить в столбце типа xml , который можно типизировать в соответствии с коллекцией схем XML или оставить нетипизированным. XML-столбец можно индексировать.
• Возможность указывать запросы XQuery к XML-данным, хранящимся в столбцах и переменных типа xml .
• Расширения в инструкции OPENROWSET, позволяющие выполнять массовую загрузку XML-данных.
• Предложение FOR XML для получения реляционных данных в формате XML.
• Функция OPENXML для получения данных XML в реляционном формате.
• Сжатие XML предоставляет метод сжатия XML-данных вне строки как для XML-столбцов, так и для XML-индексов, повышая требования к производительности. Дополнительные сведения см. в разделах CREATE TABLE (Transact-SQL) и CREATE INDEX (Transact-SQL). Сжатие XML доступно в SQL Server 2022 (16.x) и более поздних версиях, Базе данных SQL Azure и Управляемом экземпляре SQL Azure.

Далее

• Тип данных XML и столбцы (SQL Server)
• XML-индексы (SQL Server)
• Коллекции XML-схем (SQL Server)
• FOR XML (SQL Server)
• OPENXML (Transact-SQL)

См. также

• Примеры массового импорта и экспорта XML-документов (SQL Server)
• Справочник по языку XQuery (SQL Server)
• xml (Transact-SQL)

При подготовке материала использовались источники:
https://info-comp.ru/obucheniest/327-transact-sql-xml.html
https://learn.microsoft.com/ru-ru/sql/relational-databases/sqlxml/sqlxml-4-0-programming-concepts?view=sql-server-ver16
https://learn.microsoft.com/ru-ru/sql/relational-databases/xml/xml-data-sql-server?view=sql-server-ver16