(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 Windows Professional 10, Электронный ключ
Microsoft Office 365 Профессиональный Плюс. Подписка на 1 рабочее место на 1 год
Microsoft Visual Studio Professional w/MSDN AllLng Software Assurance OLP 1 License No Level Qualified
Microsoft SQL CAL 2017 Sngl OLP 1License NoLevel UsrCAL
Microsoft Office 365 для Дома 32-bit/x64. 5 ПК/Mac + 5 Планшетов + 5 Телефонов. Подписка на 1 год.
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Каждый день новые драйверы для вашего компьютера!
ЕRP-Форум. Творческие дискуссии о системах автоматизации
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Excell не печатает :-( (2)
Почему то Excell не печатает страницы в альбомной ориентаций, то есть страница в аольбомной...
 
Как извлечь рисунки из файла Word (41)
Вообще-то есть еще способ - сделать в Word-е Copy рисунка, открыть Microsoft Photo Editor и там:...
 
ODBC: dBase CREATE TABLE... (9)
Есть проблема Из программы на CTD 2.0 PTF3 подключаемся к DBF-файлам Set SqlDatabase='Файлы...
 
Как сменить пароль для схемы SYSTEM? (6)
Как сменить пароль по умолчанию для схемы SYSTEM c 'manager' на свой? То же и для SYS
 
Помощь студентам по программированию (2)
https://xn-d1aqfdigh.xn-p1ai/ Помощь студентам по программированию на языках высокого уровня.
 
 
 



    
rambler's top100 Rambler's Top100