8 июля 2012 г.

Работа с XML в Microsoft SQL Server 2008. Выборка данных в формате XML.


Всем привет! В этой статье мы поговорим о типе представления данных XML и рассмотрим, какие средства предоставляет Microsoft SQL Server 2008 для работы с XML. Для начала, давайте вспомним, что такое XML и как он выглядит. Язык XML – один из самых распространенных форматов представления иерархических данных практических во всех платформах и технологиях. В основе веб-страниц лежит язык HTML, который является подвидом XML. В основе сервисов SOAP лежит обмен данными в формате XML. Данное представление, также пользуется популярностью на многих платформах в качестве хранилища данных.
Пример данных в формате XML можно увидеть ниже:
   1:  <Devices>
   2:    <Device Id="1" Name="Lumia" Vendor="Nokia" ShopId="2" />
   3:    <Device Id="2" Name="Sensation" Vendor="HTC" ShopId="1" />
   4:    <Device Id="3" Name="Mozart" Vendor="HTC" ShopId="2" />
   5:    <Device Id="4" Name="iPhone" Vendor="Apple" ShopId="1" />
   6:  </Devices>
Однако, как связан формат XML и SQL Server, в котором данные, как известно, хранятся в реляционном виде. Как было сказано выше, XML используется для представления иерархической структуры данных. Соответственно в случае необходимости извлечения из реляционной базы иерархической структуры, использование одного запроса, который будет формировать XML документ, может избавить разработчика от необходимости выполнения нескольких запросов.
Рассмотрим следующий пример. Допустим, в нашей базе есть две таблицы Магазины и Устройства, которые связанны внешним ключом, как показано ниже:
image
Нам необходимо извлечь все магазины и все устройства, которые продаются в каждом магазине. Стандартным образом мы могли бы выполнить эту задачу двумя способами: соединить две таблицы при помощи команды JOIN; или сначала выбрать все магазины, а затем, для каждого магазина запросить список устройств. При помощи XML мы можем сформировать необходимую выборку одним простым запросом:
   1:  SELECT s.Id AS '@Id', s.Name AS '@Name',
   2:      (
   3:          SELECT d.Id AS '@Id', d.Name AS '@Name',     d.Vendor AS '@Vendor'
   4:          FROM Devices d WHERE d.ShopId = s.Id 
   5:          FOR XML PATH('Device'), TYPE
   6:      ) AS 'Devices'
   7:  FROM Shops s
   8:  FOR XML PATH('Shop'), ROOT('Shops')
В результате мы получим следующий документ, который можно будет с легкостью обработать на любом современном языке программирования:
   1:  <Shops>
   2:    <Shop Id="1" Name="PC Shop">
   3:      <Devices>
   4:        <Device Id="2" Name="Sensation" Vendor="HTC" />
   5:        <Device Id="4" Name="iPhone" Vendor="Apple" />
   6:      </Devices>
   7:    </Shop>
   8:    <Shop Id="2" Name="Rozetka">
   9:      <Devices>
  10:        <Device Id="1" Name="Lumia" Vendor="Nokia" />
  11:        <Device Id="3" Name="Mozart" Vendor="HTC" />
  12:      </Devices>
  13:    </Shop>
  14:  </Shops>
Данный запрос позволяет контролировать каким образом будут созданы элменты в документе. В данном случае мы указали название секции верхнего уровня и установили, что все колонки таблиц будут записаны, как атрибуты элементов.
Давайте изменим запрос таким образом, чтобы колонка Name таблицы ‘Магазины’ была отдельным XML элементом, название каждого устройства выводилось внутри тэга, а также добавим в каждый элемент ‘Shop’ комментарий XML:
   1:  SELECT s.Id AS '@Id', 
   2:      CAST('<Name>' + s.Name + '</Name>' as XML) AS 'node()',    
   3:      (
   4:          SELECT d.Id AS '@Id',             
   5:              d.Vendor + ' ' + d.Name AS 'text()'
   6:          FROM Devices d WHERE d.ShopId = s.Id 
   7:          FOR XML PATH('Device'), TYPE
   8:      ) AS 'Devices',
   9:      newid() as 'comment()'
  10:  FROM Shops s
  11:  FOR XML PATH('Shop'), ROOT('Shops')
Полученный результат:
   1:  <Shops>
   2:    <Shop Id="1">
   3:      <Name>PC Shop</Name>
   4:      <Devices>
   5:        <Device Id="2">HTC Sensation</Device>
   6:        <Device Id="4">Apple iPhone</Device>
   7:      </Devices>
   8:      <!--9A6491B4-6EC1-4DA6-9396-AA30C3873FE8-->
   9:    </Shop>
  10:    <Shop Id="2">
  11:      <Name>Rozetka</Name>
  12:      <Devices>
  13:        <Device Id="1">Nokia Lumia</Device>
  14:        <Device Id="3">HTC Mozart</Device>
  15:      </Devices>
  16:      <!--FB4BBBB5-9DF4-47F4-8CD5-181EEA6DDAD1-->
  17:    </Shop>
  18:  </Shops>
В данном запросе мы использовали три внутренних команды: node() – выводит содержимое поля внутрь элемента XML, без каких-либо преобразований; text() – выводит значение внутрь тэга; comment() – печатает значение, заключая его в кавычки комментариев XML.
В запросах выше мы использовали команду FOR XML PATH – это самый мощный режим, позволяющий полностью контролировать все параметры и структуру результирующего документа. Данный режим подходит для большинства задач по извлечению данных в формате XML.
Однако, Microsoft SQL Server предоставляет еще несколько команд, которые используются в частных случаях для выполнения специфических задач:
FOR XML RAW. Эта команда – самый простой способ запросить XML данные. Данная команда представляет каждую строку результирующую строку выборки как один элемент документа, а все колонки – как его атрибуты. Например, следующий запрос:
   1:  SELECT * FROM Devices FOR XML RAW ('Device')
Вернет следующий результат:
   1:  <Device Id="1" Name="Lumia" Vendor="Nokia" ShopId="2" />
   2:  <Device Id="2" Name="Sensation" Vendor="HTC" ShopId="1" />
   3:  <Device Id="3" Name="Mozart" Vendor="HTC" ShopId="2" />
   4:  <Device Id="4" Name="iPhone" Vendor="Apple" ShopId="1" />
Иногда, в наших таблицах хранятся значения со значением NULL. По умолчанию такие значения просто вырезаются из результирующего документа. Изменить это поведение можно модернизировав запрос следующим образом:
   1:  SELECT * FROM Devices FOR XML RAW('Device'), ELEMENTS XSINIL
Теперь, если в столбце Vendor таблицы Devices будет находится значение NULL – в документе оно будет отображено следующим образом:
   1:  <Vendor xsi:nil="true" />
FOR XML AUTO. Данная команда отличается от XML RAW только тем, что поддерживает иерархии при выборке данных.
FOR XML EXPLICIT. Этот режим самый неудобный из всех, представленных в среде SQL Server. Он требует объемных запросов, которые подготавливают данные в специальном формате. Практически не используется на практике.
В этой статье мы рассмотрели, каким образом можно извлекать данные из реляционной базы в формате XML, какие средства и режимы предоставляет SQL Server для формирования и гибкой настройки документов XML. В следующей статье мы узнаем об обратном процессе, каким образом можно вставлять XML данные в таблицы базы данных.
Спасибо за внимание!

1 комментарий: