Использование XML совместно с SQL
Информация - Компьютеры, программирование
Другие материалы по предмету Компьютеры, программирование
="Martin" Last_Name="Sommer" />
И как, спросите вы? Примерно так:
select 1 as tag, -- первый подзапрос
0 as parent,
pub_name as pubs!1!PubName,
city as pubs!1!City,
NULL as employee!2!First_Name,
NULL as employee!2!Last_Name
from publishers as pubs
where pub_name like Binnet% or pub_name like New Moon%
union all select 2 as tag, -- второй подзапрос
1 as parent,
pubs.pub_name,
pubs.city,
fname,
lname
from employee as e, publishers as pubs
where (pub_name like Binnet% or pub_name like New Moon%)
and pubs.pub_id = e.pub_id
order by pubs!1!PubName, pubs!1!City,
employee!2!First_Name, employee!2!Last_Name
for xml explicitДавайте рассмотрим все по порядку. Сначала выполняется первый подзапрос. Его результат приведен в таблице 1.
tagparentpubs!1!PubNamepubs!1!Cityemployee!2!First_Nameemployee!2!Last_Name10New Moon BooksBostonNULLNULL10Binnet & HardleyWashingtonNULLNULLТаблица 1.
Затем второй (Таблица 2).
tagparentpub_namecityfnamelname21Binnet & HardleyWashingtonPaoloAccorti21Binnet & HardleyWashingtonVictoriaAshworth21Binnet & HardleyWashingtonHelenBennett21Binnet & HardleyWashingtonLesleyBrown..................Таблица 2.
Затем происходит сортировка, и на основе полей tag и parent SQL Server формирует иерархический XML документ.
ПРИМЕЧАНИЕ
Для отладки подобных запросов лучше не указывать оператор FOR XML EXPLICIT. Тогда данные будут представлены в обычной реляционной форме.На этом мы, пожалуй, закончим рассмотрение оператора FOR XML EXPLICIT приведение примеров использования всех атрибутов заняло бы слишком много места.
OPENXML
Функция OPENXML является аналогом OPENROWSET, OPENDATASOURCE и OPENQUERY, которые позволяют выполнять запросы из удаленных источников. Вот ее синтаксис:
OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]])
[WITH (SchemaDeclaration | TableName)]Аргументы:
idoc хендл XML-документа, полученный при помощи хранимой процедуры sp_xml_preparedocument;
rowpattern локализуемая группа XPath или, проще говоря, XPath-выражение;
flags набор флагов, указывающих на то, как должны быть сопоставлены данные документа XML и реляционного набора строк;
ShemaDeclaration определение полей реляционного набора строк в формате:
ColName ColType [ColPattern | MetaProperty]Где
ColName имя поля.
ColType тип поля. Допускаются все типы SQL Server.
ColPattern - локализуемая группа XPath для поля.
MetaProperty метасвойство. Его мы рассматривать не будем.
XML-документ подготавливается с помощью хранимой процедуры sp_xml_preparedocument. Процедура использует анализатор MSXML для проверки документа на правильность и возвращает хендл документа. После завершения работы с OPENXML хендл нужно закрыть с помощью процедуры sp_xml_removedocument.
ПРИМЕЧАНИЕ
sp_xml_preparedocument подготавливает XML-документ, представляя его в виде объектной модели DOM (Document Object Model). Если вы работаете с большими документами, это может вызвать некоторые проблемы.Как видно из синтаксиса, вы можете не указывать флаги и определения полей для реляционного набора строк. В этом случае SQL Server создаст внутреннее представление XML-документа в так называемом "edge table"-формате. Он практически не читаем, однако при большом желании его можно использовать. Описание этого формата выходит за рамки данной статьи, но в качестве доказательства того, что с ним можно работать, приведу пример. Пусть у нас имеется такой XML-документ:
<forum name="WinAPI" totalposts="16688"
description="Системное программирование">
<forum name="COM" totalposts="10116"
description="Компонентные технологии">
<forum name="Delphi" totalposts="5001"
description="Delphi и Builder">
Вот запрос, возвращающий общее количество сообщений для каждого форума:
exec sp_xml_preparedocument @hdoc out, @_xmlbody
select [text] as totalposts
from openxml(@hdoc,/rsdn/forums/forum) as f
join (select [id],localname \
from openxml(@hdoc,/rsdn/forums/forum)
where localname = totalposts) as d on d.[id] = f.parentid
exec sp_xml_removedocument @hdocРезультатом его будет следующая таблица:
totalposts166881011650016606Не советую использовать подобный метод в рабочих проектах, и не только потому, что он неэффективен (как видно из примера, XML-документ сканируется дважды). Рассмотрим пример, выдающий тот же самый результат с использованием XPath.
exec sp_xml_preparedocument @hdoc out, @_xmlbody
select *
from openxml(@hdoc,/rsdn/forums/forum)
with(totalposts varchar(100) attribute::totalposts)
exec sp_xml_removedocument @hdocЗдесь, чтобы ограничить реляционный набор строк, я воспользовался XPath-выражением.
Выражение attribute::totalposts означает, что для поля totalposts будет использоваться значение одноименного атрибута. Гораздо чаще в XPath-выражениях используется сокращенная запись:
attribute:: можно заменить символом @;
self::node() можно заменить на точку (.);
parent::node() можно заменить на две точки (..).
Другие сокращения можно найти в спецификации XPath.
Давайте рассмотрим более сложный пример: выберем название форума, модератора и дату создания статистики для всех форумов, у которых больше 6000 сообщений.
exec sp_xml_preparedocument @hdoc out, @_xmlbody
select