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

Скриншот 1

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

  • 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

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

Скриншот 2

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

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

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

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

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

Скриншот 3

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

Далее рассмотрим функцию 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

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

Скриншот 4

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

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.