Теперь рассмотрим, как формировать иерархические документы. Возьмем такой запрос:
| select pub_name,city,fname,lname from publishers as p join employee as e on p.pub_id = e.pub_id where pub_name like 'Binnet%' or pub_name like 'New Moon%' order by pub_name,city,fname,lname |
Он возвращает имена всех служащих издательств Binnet & Hardley и New Moon Books. Результат запроса (20 записей) я приводить не буду, отмечу только, что он содержит большое количество повторяющихся названий издательств, т.к. результат представлен в реляционной форме. Мы же хотим получить следующее:
| <pubs PubName="Binnet & Hardley" City="Washington"> <employee First_Name="Anabela" Last_Name="Domingues" /> <employee First_Name="Daniel" Last_Name="Tonini" /> <employee First_Name="Elizabeth" Last_Name="Lincoln" /> <employee First_Name="Helen" Last_Name="Bennett" /> <employee First_Name="Lesley" Last_Name="Brown" /> <employee First_Name="Martine" Last_Name="Rance" /> <employee First_Name="Paolo" Last_Name="Accorti" /> <employee First_Name="Paul" Last_Name="Henriot" /> <employee First_Name="Peter" Last_Name="Franken" /> <employee First_Name="Victoria" Last_Name="Ashworth" /> </pubs><pubs PubName="New Moon Books" City="Boston"> <employee First_Name="Gary" First_Name="Thomas" /> <employee First_Name="Howard" First_Name="Snyder" /> <employee First_Name ="Karin" First_Name="Josephs" /> <employee First_Name ="Laurence" Last_Name="Lebihan" /> <employee First_Name ="Martin" Last_Name="Sommer" /> <employee First_Name ="Mary" Last_Name="Saveley" /> <employee First_Name ="Matti" Last_Name="Karttunen" /> <employee First_Name ="Palle" Last_Name="Ibsen" /> <employee First_Name ="Roland" Last_Name="Mendel" /> <employee First_Name ="Timothy" Last_Name="O'Rourke" /> </pubs> |
И как, спросите вы? Примерно так:
| 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.
| tag | parent | pubs!1!PubName | pubs!1!City | employee!2!First_Name | employee!2!Last_Name |
| 1 | 0 | New Moon Books | Boston | NULL | NULL |
| 1 | 0 | Binnet & Hardley | Washington | NULL | NULL |
Таблица 1.
Затем второй (Таблица 2).
| tag | parent | pub_name | city | fname | lname |
| 2 | 1 | Binnet & Hardley | Washington | Paolo | Accorti |
| 2 | 1 | Binnet & Hardley | Washington | Victoria | Ashworth |
| 2 | 1 | Binnet & Hardley | Washington | Helen | Bennett |
| 2 | 1 | Binnet & Hardley | Washington | Lesley | Brown |
| ... | ... | ... | ... | ... | ... |
Таблица 2.
Затем происходит сортировка, и на основе полей tag и parent SQL Server формирует иерархический XML документ.
| ПРИМЕЧАНИЕДля отладки подобных запросов лучше не указывать оператор FOR XML EXPLICIT. Тогда данные будут представлены в обычной реляционной форме. |
На этом мы, пожалуй, закончим рассмотрение оператора FOR XML EXPLICIT – приведение примеров использования всех атрибутов заняло бы слишком много места.
Функция 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-документ:
| <?xml version="1.0" encoding="windows-1251" ?><rsdn> <forums date="09.01.03"> <forum name="WinAPI" totalposts="16688" description="Системноепрограммирование"> <moderators/> <top-poster>Alex Fedotov</top-poster> </forum> <forum name="COM" totalposts="10116" description="Компонентныетехнологии"> <moderators/> <top-poster>Vi2</top-poster> </forum> <forum name="Delphi" totalposts="5001" description="Delphi и Builder"> <moderators> <moderator name="Sinclair"/> <moderator name="Hacker_Delphi"/> </moderators> <top-poster>Sinclair</top-poster> </forum> <forum name="DB" totalposts="6606" description="Базыданных"> <moderators> <moderator name="_MarlboroMan_"/> </moderators> <top-poster>Merle</top-poster></forum> </forums></rsdn> |
Вот запрос, возвращающий общее количество сообщений для каждого форума:
| exec sp_xml_preparedocument @hdoc out, @_xmlbodyselect [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.parentidexec sp_xml_removedocument @hdoc |
Результатом его будет следующая таблица:
| totalposts |
| 16688 |
| 10116 |
| 5001 |
| 6606 |
Не советую использовать подобный метод в рабочих проектах, и не только потому, что он неэффективен (как видно из примера, XML-документ сканируется дважды). Рассмотрим пример, выдающий тот же самый результат с использованием XPath.
| exec sp_xml_preparedocument @hdoc out, @_xmlbodyselect * 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, @_xmlbodyselect forum as 'Форум', case when moders is null then 'нет' else moders end as 'Модератор', [date] as 'Датасоздания' from openxml(@hdoc,'/rsdn/forums/forum[attribute::totalposts > "6000"]') with ( moders varchar(50) 'moderators/moderator/attribute::name', forum varchar(50) 'attribute::name', [date] varchar(50) 'parent::node()/attribute::date' )exec sp_xml_removedocument @hdoc |
Часть запроса, использующую XPath, можно переписать в сокращенной форме :
| openxml(@hdoc,'/rsdn/forums/forum[@totalposts > "6000"]') with( moders varchar(50) 'moderators/moderator/@name', forum varchar(50) '@name', [date] varchar(50) '../@date') |
Везде далее я буду пользоваться сокращенной записью.
Чтобы разобраться с флагами OPENXML, рассмотрим слегка модифицированный пример из MSDN:
| DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='<root> <Customer cid= "C1" city="Issaquah"> <name>Janine</name> <Order oid="O1" date="1/20/1996" amount="3.5" /> <Order oid="O2" date="4/30/1997" amount="13.4"> Customer was very satisfied </Order> </Customer> <Customer cid="C2" city="Oelde" > <name>Ursula</name> <Order oid="O4" date="1/20/1996" amount="10000">Happy Customer.</Order> <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue white red"> Sad Customer. <Urgency>Important</Urgency></Order> </Customer></root>'-- Создание внутреннего представления XML-документа.EXEC sp_xml_preparedocument @idoc OUTPUT, @docSELECT * FROM OPENXML (@idoc, '/root/Customer', 2) WITH ( cid char(5) '@cid', [name] varchar(20), oid char(5) 'Order/@oid', amount float 'Order/@amount', comment varchar(100) 'Order/text()' )-- ОчисткаEXEC sp_xml_removedocument @idoc |
Результат будет следующим:
| cid | name | oid | amount | comment |
| C1 | Janine | O1 | 3.5 | Customer was very satisfied |
| C2 | Ursula | O4 | 10000.0 | Happy Customer. |
Отметим некоторые особенности:
В качестве режима отображения XML-данных на поля реляционной таблицы использовалось значение 2 (element-centric mapping). Это означает, что по умолчанию имена колонок получаемой реляционной таблицы будут соответствовать именам вложенных XML-элементов. Кроме этого, возможно использование значений 0, 1 и 8. 0 используется по умолчанию и означает использование attribute-centric mapping. 1, как ни странно, означает то же самое. Флаги 1 и 2 можно комбинировать по "или", т.е. если подставить 3, сначала будет произведена попытка найти атрибут с именем, соответствующим имени колонки, а затем (если атрибут не найден) будет произведен поиск элемента с соответствующим именем (иначе будет возвращен NULL). Благодаря тому, что в качестве флага было указано значение 2, для поля cid пришлось явно указать XPath-запрос, указывающий, что на эту колонку отображается атрибут cid. Для поля name не потребовалось непосредственного указания XPath-выражения. Если бы в качестве флага использовалось значение 1 (использование отображения атрибутов), то картина изменилась бы на противоположную: т.е. для cid не нужно бы было ничего указывать, а для name пришлось бы написать шаблон (т.е. просто выражение ‘name’).