(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

XML и SQL Server

Источник: kbyte
Алексей Немиро

Чтение XML

SQL Server, еще с 2000 года, имеет набор стандартных средств для работы с XML. В частности, в базе данных можно хранить данные типа XML, создавать их налету и читать.

Поскольку XML как таковой является строкой, то для того чтобы SQL Server мог работать с XML как с набором данных, нужно ему об этом рассказать. Сделать это можно при помощи системной хранимой процедуры sp_xml_preparedocument. Процедураsp_xml_preparedocument создает представление указанного XML-документа (по сути, таблица) и передает его дескриптор (идентификатор, числовой код). Последующая работа с XML происходит именно через дескриптор, посредствам функции OPENXML. После завершения работы, документ можно удалить при помощи процедуры sp_xml_removedocument, хотя это делать вовсе и не обязательно, поскольку данные и так удалятся по завершению работы текущей сессии. Чтобы было более понятно, о чем идет речь, давайте рассмотрим простой пример чтения XML.

01  DECLARE @h int; 
02  DECLARE @data nvarchar(max); 
03  SET @data = 
04  '<www> 
05   <site name="Портал для программистов" url="http://kbyte.ru" /> 
06   <site name="Поисковая система" url="http://yandex.ru" /> 
07   <site name="Социальная сеть" url="http://facebook.com" /> 
08  </www>'; 
09   
10  exec sp_xml_preparedocument @h OUTPUT, @data; 
11   
12  SELECT FROM OPENXML (@h, '/www/site') 
13  WITH ([name] nvarchar(50), [url] nvarchar(100)); 
14   
15  exec sp_xml_removedocument @h; 

Как видите, у нас есть две переменные. Переменная @h имеет числовой тип данных, она предназначена для хранения ссылки на дескриптор представления XML. Переменная @data имеет строкой тип данных, которая служит для передачи XML. Системная процедура sp_xml_preparedocument создает на основе указанных в переменной @data данных представление XML и передает в переменную @h ссылку на это представление.

Затем, при помощи инструкции SELECT FROM выводятся данные XML-документа, сформированные в функции OPENXML. Первый параметр функции OPENXML указывает ссылку на дескриптор документа XML, второй параметр указывает ветку, из которой нужно провести выборку данных. Так, в приведенном примере, берутся все элементы site, относящиеся к элементу www. Далее, операторWITH указывает имена атрибутов и выходящий тип данных, которые будут переданы в таблицу. У нас выводятся все атрибуты: nameтипа nvarchar(50)url типа nvarchar(100). Если, например, убрать url, то в выдаче просто не будет такой колонки. Если указать неверный тип данных, то может произойти ошибка. Если указать несуществующее имя, то в выдаче поле будет иметь значение NULL.

 

Функция OPENXML также может принимать и третий параметр - flags, который указывает на тип сопоставления, по умолчанию он имеет нулевое значение - сопоставление по атрибутам. Именно поэтому, в указанном выше примере данные выводятся из XML-атрибутов, а не элементов. Параметр flasg может принимать следующие значения:

0 - значение по умолчанию, как уже было сказано, будет использоваться атрибутная модель сопоставления;
1 - приоритетно используется атрибутная модель, а сопоставление с использованием элементов на втором плане;
2 - сопоставление с использованием элементов;
8 - позволят выбрать метод и приоритет сопоставления.

Т.е. если требуется вывести данные из элементов, то нужно использовать опцию 2, как показано в следующем примере.

01  DECLARE @h int; 
02  DECLARE @data nvarchar(max); 
03  SET @data = 
04  '<www> 
05   <site> 
06   <name>Портал для программистов</name> 
07   <url>http://kbyte.ru</url> 
08   </site> 
09   <site> 
10   <name>Поисковая система</name> 
11   <url>http://yandex.ru</url> 
12   </site> 
13   <site> 
14   <name>Социальная сеть</name> 
15   <url>http://facebook.com</url> 
16   </site> 
17  </www>';<strong> 
18   
19  </strong>exec sp_xml_preparedocument @h OUTPUT, @data; 
20   
21  SELECT FROM OPENXML (@h, '/www/site', 2) 
22  WITH ([name] nvarchar(50), [url] nvarchar(100)); 
23   
24  exec sp_xml_removedocument @h; 

Результат выполнения запроса

Рис. 1. Вывод XML в SQL Server в виде таблицы.

Как видите, все относительно просто, хотя может быть и еще проще. Начиная с SQL Server 2005 появился тип данных XML, который позволяет обходиться без всего выше описанного. Тип данных XML имеет встроенные методы, которые дают возможность работать с XML без создания дескрипторов.

01  DECLARE @data xml; 
02  SET @data = 
03  '<www> 
04   <site name="Портал для программистов" url="http://kbyte.ru" /> 
05   <site name="Поисковая система" url="http://yandex.ru" /> 
06   <site name="Социальная сеть" url="http://facebook.com" /> 
07  </www>'; 
08   
09  SELECT 
10  n.value('@name''nvarchar(50)'AS name, 
11  n.value('@url''nvarchar(100)'AS url 
12  FROM @data.nodes('/www/site'AS node(n) 

В этом примере данные берутся напрямую из указанного документа при помощи метода nodes и каждая пачка данных передается в поле n (имя может быть любым) типа node. Значение из конкретного атрибута берется при помощи функции value, которая принимает два параметра. Первый параметр указывает имя XML-сущности, в данном случае, символ "собака" (@) говорит о том, что нужно получить значение из атрибута. Использовать атрибуты достаточно просто, именно поэтому они фигурируют во всех примерах данной статьи. Работа с элементами может показаться несколько сложной для понимания.

01  DECLARE @data xml; 
02  SET @data = 
03  '<www> 
04   <site> 
05   <name>Портал для программистов</name> 
06   <url>http://kbyte.ru</url> 
07   </site> 
08   <site> 
09   <name>Поисковая система</name> 
10   <url>http://yandex.ru</url> 
11   </site> 
12   <site> 
13   <name>Социальная сеть</name> 
14   <url>http://facebook.com</url> 
15   </site> 
16  </www>'; 
17   
18  SELECT 
19  n.value('name[1]''nvarchar(50)'AS name, 
20  n.value('url[1]''nvarchar(50)'AS url 
21  FROM @data.nodes('/www/site'AS node(n) 

В пример, каждый элемент site теоретически может иметь множество вложенных элементов name и url, т.е. фактически каждый из этих элементов является массивом. Именно поэтому в квадратных скобках указывается индекс элемента, в нашем случае это единица (отсчет начинается с 1).

Тип данных XML имеет та же и другие методы, такие как queryexistmodify, но в рамках данной статьи я их рассматривать не буду, если у вас возникнет потребность в использовании этих методов, вам всегда помогут на форуме для программистов.

 

Выдача XML

Создавать XML гораздо проще чем, читать. Во-первых, можно сгенерировать XML в обычной строковой переменной, как показано в следующем примере. Собственно, так можно сделать не только в SQL Server, но и в других СУБД.

01  -- временная таблица, чтобы было на чем показывать пример 
02  CREATE TABLE #tmp (id int identity, name nvarchar(50), url nvarchar(100)); 
03   
04  INSERT INTO #tmp 
05  SELECT 'Портал для программистов''http://kbyte.ru'; 
06  INSERT INTO #tmp 
07  SELECT 'Поисковая система''http://yandex.ru'; 
08  INSERT INTO #tmp 
09  SELECT 'Социальная сеть''http://facebook.com'; 
10   
11  -- основная часть примера 
12   
13  DECLARE @xml AS nvarchar(1000); 
14  SET @xml = '<www>'; 
15  SELECT @xml = @xml + '<site name="' name '" url="' + url + '" />' FROM #tmp; 
16  SET @xml = @xml + '<www>'; 
17   
18  SELECT @xml; 
19   
20  -- удаляем временную таблицу 
21  DROP TABLE #tmp; 

Во-вторых, в SQL Server 2005 появились средства, которые позволяют буквально парой дополнительных команд превратить любую таблицу в XML. Для выдачи результатов в XML-формате, в инструкции SELECT FROM нужно использовать команду FOR XML. Эта команда умеет генерировать XML в нескольких режимах: RAWAUTOEXPLICIT и PATH.

1  SELECT FROM #tmp FOR XML AUTO; 

Рис. 2. Результат автоматического формирования XML.

Режим RAW и AUTO самые простые. RAW создает для каждой строки данных XML-элемент, по умолчанию, с именем row, где в качестве атрибутов выступают колонки таблицы. Режим AUTO генерирует XML в зависимости от представленных данных, в нашем случае получается результат совсем далекий от ожидаемого. Более гибкие и, соответственно, сложные режимы это EXPLICIT и PATH.

01  -- временная таблица, чтобы было на чем показывать пример 
02  CREATE TABLE #tmp (id int identity, name nvarchar(50), url nvarchar(100)); 
03   
04  INSERT INTO #tmp 
05  SELECT 'Портал для программистов''http://kbyte.ru'; 
06  INSERT INTO #tmp 
07  SELECT 'Поисковая система''http://yandex.ru'; 
08  INSERT INTO #tmp 
09  SELECT 'Социальная сеть''http://facebook.com'; 
10   
11  -- основная часть примера 
12  SELECT name, url FROM #tmp FOR XML PATH('site'), ROOT('www'); 
13   
14  -- удаляем временную таблицу 
15  DROP TABLE #tmp; 

Рис. 3. Результат формирования XML в режиме PATH.

В этом примере используется режим PATH, первый, и единственный обязательный, параметр которого содержит имя основного элемента - site, в который будут вложены значения полей таблицы. Директива ROOT указывает имя родительской ветки - www. Если требуется изменить имена элементов XML, то это можно сделать стандартными средствами SQL, т.е. путем создания алиасов (синонимов). Символ "собаки" (@) в имени алиса превратит колонку в выдаче в XML-атрибут.

1  SELECT name AS 'the_name', url AS '@address' FROM #tmp FOR XML PATH('site'), ROOT('www'); 

Ссылки по теме


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Microsoft »
Обсудить материал в конференции Дизайн, графика, обработка изображений »
Написать редактору 
 Рекомендовать » Дата публикации: 19.09.2013 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Microsoft Visual Studio Professional w/MSDN AllLng License/Software Assurance Pack OLP 1 License No Level Qualified
Microsoft System Center Standard Core Sngl License/Software Assurance Pack OLP 2Licenses NoLevel CoreLic Qualified
Microsoft Office 365 Персональный 32-bit/x64. 1 ПК/MAC + 1 Планшет + 1 Телефон. Все языки. Подписка на 1 год.
Microsoft SQL Server Standard Core 2017 Sngl OLP 2Licenses NoLevel CoreLic Qualified
Microsoft SQL Server Standard Edition 2017 Sngl OLP 1License NoLevel
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Компьютерный дизайн - Все графические редакторы
Краткие описания программ и ссылки на них
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Беговая дорожка (3)
Купила беговую дорожку вот здесь https://4gym.com.ua/product/technogym-artis-run Очень классная,...
 
Аналоги виагры (3)
Если мужчине, по каким либо причинам, не подходит виагра, существуют качественные аналоги...
 
работа на дому! (8)
Доброго времени суток дорогие друзья. Многоуровневый маркетинг окончательно признан...
 
Отличается ли ДрифтКазино от беттинга? (16)
Друзья, давно заметил, что на Дрифте уже несколько месяцев во всю рекламируется и предлагается...
 
Актуальное зеркало БК Лигаставок (2)
Актуальное зеркало букмекерской конторы Лигаставок https://superbet.guru/bk-ligastavok-mirror/...
 
 
 



    
rambler's top100 Rambler's Top100