Всем привет! В этой статье мы поговорим о типе представления данных 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 документ, может избавить разработчика от необходимости выполнения нескольких запросов.
Рассмотрим следующий пример. Допустим, в нашей базе есть две таблицы Магазины и Устройства, которые связанны внешним ключом, как показано ниже:
Нам необходимо извлечь все магазины и все устройства, которые продаются в каждом магазине. Стандартным образом мы могли бы выполнить эту задачу двумя способами: соединить две таблицы при помощи команды 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 данные в таблицы базы данных.
Спасибо за внимание!
Этот комментарий был удален администратором блога.
ОтветитьУдалить