В прошлой статье мы узнали, каким образом из базы можно извлекать реляционные данные в виде XML структуры. Сегодня мы познакомимся с принципами хранения структуры XML данных в базе.
Основной элемент поддержки XML в MS SQL Server – тип данных “xml”. Данный тип может использоваться для объявления переменных и как тип колонки в таблице наравне со стандартными типами данных SQL (int, nvarchar и т.д.).
Для типа данных XML доступны следующие специализированные операции. Приведем их краткое описание:
- query() – осуществляет запросы к XML данным;
- nodes() – извлекает поддерево из структуры XML.
- value() – позволяет извлекать значения атрибутов из XML элемента;
- exist() – проверяет существуют ли результаты запроса;
- modify() – производит обновление XML данных;
Команда «query()».
Эта команда позволяет писать запросы к XML дереву. Команда возвращает набор результатов, соответствующих запросу. Рассмотрим пример. Допустим, у нас есть следующая XML структура:
1: DECLARE @xmlData XML =N'
2: <Shops>
3: <Shop id="1">
4: <device name="Sensation" vendor="HTC" id="1" />
5: <device name="iPhone" vendor="Apple" id="2" />
6: </Shop>
7: <Shop id="2">
8: <device name="Mozart" vendor="HTC" id="3" />
9: <device name="Lumia" vendor="Nokia" id="4" />
10: </Shop>
11: </Shops>';
1: SELECT @xmlData.query('/Shops/Shop/device')
1: <device name="Sensation" vendor="HTC" id="1" />
2: <device name="iPhone" vendor="Apple" id="2" />
3: <device name="Mozart" vendor="HTC" id="3" />
4: <device name="Lumia" vendor="Nokia" id="4" />
Стоит отметить, что имена элементов и атрибутов регистрозависимые, как в XML структуре, так и в тексте запроса.
Также, в запросе, можно наложить некоторые ограничения на выбираемые данные, помимо пути. Например, давайте напишем запрос, который будет выбирать из структуры XML все устройства, производителем которых является компания «HTC»:
1: SELECT @xmlData.query('/Shops/Shop/device/.[@vendor cast as xs:string? = "HTC"]') as Data
Команда «nodes()».
Разбивает XML структуру на одно или несколько поддеревьев, в соответствии с указанным запросом. Для примера будем использовать ту же структуру данных @xmlData.
Выполним следующий запрос:1: SELECT shop.query('.') as data FROM @xmlData.nodes('/Shops/Shop') col(shop)
Данный запрос разобьет исходную структуру на строки, по количеству элементов <Shop> и вернет нам две строки:
Разберем текст запроса подробнее.
‘@xmlData.nodes('/Shops/Shop')’ - собственно разбивает данные по указанному запросу.
‘col(shop)’ – это псевдоним для результатов разбиения. Данный псевдоним необходим для дальнейшей работы с результатами.
‘shop.query('.')’ – здесь осуществляется запрос к каждой строке результатов, при помощи псевдонима. Данный подзапрос не осуществляет фильтрации, выбирая все данные из поддерева.
Команда «value()».
С помощью данной команды можно извлекать значения из XML дерева. Команда умеет конвертировать строковые литералы из XML текста в любые типы данных среды MS SQL Server.
В качестве примера давайте преобразуем XML структуру @xmlData в табличное представление, выполнив следующий запрос:
1: SELECT
2: device.value('@id', 'int') as Id,
3: device.value('@vendor', 'nvarchar(50)') as Company,
4: device.value('@name', 'nvarchar(50)') as Name
5: FROM @xmlData.nodes('/Shops/Shop/device') col(device)
Команда value() принимает два параметра: название атрибута с префиксом @ и название типа данных, к которому необходимо привести переменную.
Аналогичным способом можно получить доступ к значению, расположенным внутри элемента хмл, например:
1: DECLARE @data XML = N'
2: <Shops>
3: <Shop id="1">Winodws Marketplace</Shop>
4: </Shops>';
5:
6: SELECT
7: shop.value('@id', 'int') as Id,
8: shop.value('.[1]', 'nvarchar(50)') as CompanyName
9: FROM @data.nodes('/Shops/Shop') col(shop)
Также можно получить доступ к родительскому элементу любого уровня. Давайте модернизируем наш первый запрос к @xmlData, добавив в выборку Id магазина, в котором находится устройство:
1: SELECT
2: device.value('@id', 'int') as Id,
3: device.value('@vendor', 'nvarchar(50)') as Company,
4: device.value('@name', 'nvarchar(50)') as Name,
5: device.value('../@id', 'int') as ShopId
6: FROM @xmlData.nodes('/Shops/Shop/device') col(device)
Результат отобразился как надо, однако при больших объемах данных такой запрос будет крайне медленным и неэффективным. Проблема в том, что для каждого устройства среда выполнения запроса ищет родительский элемент и считывает его атрибут. Перепишем запрос следующим образом:
1: SELECT
2: device.value('@id', 'int') as Id,
3: device.value('@vendor', 'nvarchar(50)') as Company,
4: device.value('@name', 'nvarchar(50)') as Name,
5: shop.value('@id', 'int') as ShopId
6: FROM
7: @xmlData.nodes('/Shops/Shop') col(shop)
8: CROSS APPLY
9: shop.nodes('device') tab(device)
Результат будет аналогичным, однако теперь мы поменяли логику запроса таким образом, что вначале будут выбираться все магазины, а потом, для каждого магазина, будут присоединяться устройства. Поскольку устройств всегда будет намного больше, чем магазинов, данный запрос даст нам существенный прирост производительности.
Команда «exist()».
Данная команда возвращает значения типа bit. 1 – если результаты, соответствующие запросу существуют, и 0 – если не существуют.
Запрос формируется таким же образом, как и в команде query(). Команда используется, как правило, для наложения ограничений в запросе выборки, применяя фильтрующий запрос exist() к поддереву. Приведем пример:
1: SELECT
2: shop.value('@id', 'int') as ShopId
3: FROM
4: @xmlData.nodes('/Shops/Shop') col(shop)
5: WHERE shop.exist('device[@vendor cast as xs:string? = "Nokia"]') = 1
Данный запрос выберет Id всех магазинов, в которых присутствуют устройства от компании «Nokia»:
На этом данная статья подходит к концу. В следующей статье мы рассмотрим последнюю команду типа «xml» - modify(), а также обсудим различные способы оптимизации взаимодействия с XML структурами в среде Microsoft SQL Server.
Этот комментарий был удален автором.
ОтветитьУдалитьЭтот комментарий был удален автором.
ОтветитьУдалитьСпасибо, нашёл решение своей задачи.
ОтветитьУдалитьСпасибо, очень доступно!
ОтветитьУдалитьБольшое спасибо, решил свою задачу благодаря статье. Лаконично и очень полезно!
ОтветитьУдалитьСпасибо автору. Статья полезная - все "объяснено на пальцах" - просто и доступно
ОтветитьУдалитьКратко и по делу.
ОтветитьУдалитьБлагодарю от души!
Большое спасибо. Полезная статья
ОтветитьУдалитьДоступно и понятно
ОтветитьУдалитьСпасибо большое за статью! Очень доходчиво! Лучше статьи по работе с xml в MSSQL не найти!
ОтветитьУдалитьСпасибо! Пригодилось
ОтветитьУдалитьНаконец-то адекватное раскрытие темы с рабочими примерами! Лайк однозначно!
ОтветитьУдалить