Сегодня мы начнем рассматривать достаточно полезную возможность 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,'<catalog>
<name>Иван</name>
<lastname>Иванов</lastname>
</catalog>')
Здесь мы просто вручную разметили 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 <lastname>Иванов</lastname> 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 id=2
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.
