17 июля 2012 г.

Работа с XML данными в Microsoft SQL Server 2008. Тип данных «xml».



В прошлой статье мы узнали, каким образом из базы можно извлекать реляционные данные в виде XML структуры. Сегодня мы познакомимся с принципами хранения структуры XML данных в базе.
Основной элемент поддержки XML в MS SQL Server – тип данных “xml”. Данный тип может использоваться для объявления переменных и как тип колонки в таблице наравне со стандартными типами данных SQL (int, nvarchar и т.д.).
Для типа данных XML доступны следующие специализированные операции. Приведем их краткое описание:
  1. query() – осуществляет запросы к XML данным;
  2. nodes() – извлекает поддерево из структуры XML.
  3. value() – позволяет извлекать значения атрибутов из XML элемента;
  4. exist() – проверяет существуют ли результаты запроса;
  5. 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')
Как результат, мы получим все элементы <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
Запрос вернет следующий результат:
image
Команда «nodes()».
Разбивает XML структуру на одно или несколько поддеревьев, в соответствии с указанным запросом. Для примера будем использовать ту же структуру данных @xmlData.
Выполним следующий запрос:
   1:  SELECT shop.query('.') as data FROM @xmlData.nodes('/Shops/Shop') col(shop)
Данный запрос разобьет исходную структуру на строки, по количеству элементов <Shop> и вернет нам две строки:
image

Разберем текст запроса подробнее.
‘@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)
Данный запрос вернет следующий табличный результат:
image
Команда 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)
Результат будет следующим:
image
Также можно получить доступ к родительскому элементу любого уровня. Давайте модернизируем наш первый запрос к @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)
Результат похож на то, что мы хотели:
image
Результат отобразился как надо, однако при больших объемах данных такой запрос будет крайне медленным и неэффективным. Проблема в том, что для каждого устройства среда выполнения запроса ищет родительский элемент и считывает его атрибут. Перепишем запрос следующим образом:
   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»:
image
На этом данная статья подходит к концу. В следующей статье мы рассмотрим последнюю команду типа «xml» - modify(), а также обсудим различные способы оптимизации взаимодействия с XML структурами в среде Microsoft SQL Server.

12 комментариев:

  1. Этот комментарий был удален автором.

    ОтветитьУдалить
  2. Этот комментарий был удален автором.

    ОтветитьУдалить
  3. Спасибо, нашёл решение своей задачи.

    ОтветитьУдалить
  4. Спасибо, очень доступно!

    ОтветитьУдалить
  5. Большое спасибо, решил свою задачу благодаря статье. Лаконично и очень полезно!

    ОтветитьУдалить
  6. Спасибо автору. Статья полезная - все "объяснено на пальцах" - просто и доступно

    ОтветитьУдалить
  7. Кратко и по делу.
    Благодарю от души!

    ОтветитьУдалить
  8. Большое спасибо. Полезная статья

    ОтветитьУдалить
  9. Доступно и понятно

    ОтветитьУдалить
  10. Спасибо большое за статью! Очень доходчиво! Лучше статьи по работе с xml в MSSQL не найти!

    ОтветитьУдалить
  11. Спасибо! Пригодилось

    ОтветитьУдалить
  12. Наконец-то адекватное раскрытие темы с рабочими примерами! Лайк однозначно!

    ОтветитьУдалить