Методические указания к выполнению ЛР4 - Полуструктурированные данные в формате XML и запросы к ним, отображение в реляционную модель, страница 3

for xml raw('road'), elements

RAW позволяет переименовывать элементы, соответствующие строкам,

ELEMENTS указывает, что поля будут выводится как содержимое вложенных элементов.

Данные выводятся:

• каждая строка результата соответствует одному элементу, имя которого задается явно for xml raw('road') или по-молчанию for xml raw, тогда элемент будет называться row;

• каждое поле результата соответствует вложенному элементу с именем поля.

Результат:

<road><src>москва </src><dest>СПб </dest><len>999</len><type>жд </type><cost>100</cost><id>1</id></road>

<road><src>москва </src><dest>анапа </dest><len>22</len><type>жд </type><cost>150</cost><id>2</id></road>

<road><src>СПб </src><dest>лондон </dest><len>6</len><type>авиа </type><cost>200</cost><id>3</id></road>

...

1. Все поля выводятся как атрибуты

select * from Roads

for xml raw('road')

Без явного указания вывода элементами они выводятся как значения атрибутов.

Данные выводятся:

• каждая строка результата соответствует одному элементу, имя которого задается явно for xml raw('road') или по-молчанию for xml raw,

• каждое поле результата соответствует значению атрибута с именем поля.

Результат:

<road src="москва " dest="СПб " len="999" type="жд " cost="100" id="1"/>

<road src="москва " dest="анапа " len="22" type="жд " cost="150" id="2"/>

<road src="СПб " dest="лондон " len="6" type="авиа " cost="200" id="3"/>

...

1. Добавление корневого элемента

Без корневого элемента XML документ не будет полным, а является только фрагментом XML документа.

Для добавления к XML-выводу корневого элемента используется конструкция ROOT. При этом можно делать вывод либо элементный, либо атрибутный:

select * from Roads

for xml auto, root

По умолчанию название корневого элемента будет root.

Для указания другого имени его можно указать как параметр ROOT:

select * from Roads

for xml auto, root('myRoot')

Результат:

<myRoot>

<Roads src="москва " dest="СПб " len="999" type="жд " cost="100" id="1"/>

<Roads src="москва " dest="анапа " len="22" type="жд " cost="150" id="2"/>

<Roads src="СПб " dest="лондон " len="6" type="авиа " cost="200" id="3"/>

...

</myRoot>

1. Добавление схемы документа

XML-Схема определяет структуру XML документа: допустимые элменты и порядок их вложенности (состав, количество, вложенность), атрибуты и допустимые значения атрибутов элментов (типы данных, обязательность и т.д.).

Для добавления к XML-выводу XML-схемы используется конструкция SCHEMA. Остальные настройки вывода произвольны:

select title,period,road from Builder

for xml raw('build'),root('test'), xmlschema

Будет сформирован и выведен XML-документ, в элементы которого добавляется указание на схему xmlns="urn:schemas-microsoft-com:sql:SqlRowSet3" (атрибут названия схемы).

Перед документом будет выведана его схема (описание структуры).

Результат:

<test>

<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet3" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified"><xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/><xsd:element name="build"><xsd:complexType><xsd:attribute name="title" use="required"><xsd:simpleType><xsd:restriction base="sqltypes:nchar" sqltypes:localeId="1049" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth"><xsd:maxLength value="10"/></xsd:restriction></xsd:simpleType></xsd:attribute><xsd:attribute name="period" type="sqltypes:int"/><xsd:attribute name="road" type="sqltypes:int"/></xsd:complexType></xsd:element></xsd:schema>

<build xmlns="urn:schemas-microsoft-com:sql:SqlRowSet3" title="CMY-1 " period="1" road="1"/>

<build xmlns="urn:schemas-microsoft-com:sql:SqlRowSet3" title="CMY-150 " period="23" road="2"/>

<build xmlns="urn:schemas-microsoft-com:sql:SqlRowSet3" title="CMY-20 " period="8" road="13"/>

<build xmlns="urn:schemas-microsoft-com:sql:SqlRowSet3" title="CMY-21 " period="8" road="13"/>

...

</test>

1. Добавление значений NULL

Если поля результата запроса имеют значения NULL, то они пропускаются в итоговом выводе, например:

select top(2) * from roads

москва СПб NULL жд NULL 1

москва анапа 22 жд 150 2

select top(2) * from roads

for xml raw('road'), root

<root>

<road src="москва " dest="СПб " type="жд " id="1"/>

<road src="москва " dest="анапа " len="22" type="жд " cost="150" id="2"/>

</root>

select top(2) * from roads

for xml raw('road'), elements, root

<root>

<road><src>москва </src><dest>СПб </dest><type>жд </type><id>1</id></road>

<road><src>москва </src><dest>анапа </dest><len>22</len><type>жд </type><cost>150</cost><id>2</id></road>

</root>

Для явного вывода NULL-значений используется элементый вывод и конструкция XSINIL:

select top(2) * from roads

for xml raw('road'), elements xsinil, root

К корневому элементу будет добавлен атрибут xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance", указывающий на используемую схему.

Те атрибуты, которые имеют NULL значения, выводятся как пустые элементы атрибутом xsi:nil="true".

Результат:

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<road><src>москва </src><dest>СПб </dest><len xsi:nil="true"/><type>жд </type><cost xsi:nil="true"/><id>1</id></road>

<road><src>москва </src><dest>анапа </dest><len>22</len><type>жд </type><cost>150</cost><id>2</id></road>

</root>

1. Произвольный вывод

Можно получить результат выполнения запроса в виде XML с произвольной структурой. Явно указывается вложенность элементов и их названия, названия атрибутов и соответствие между полями результата и частями XML-документа.

Для этого используется конструкция PATH с указанием названия элемента.

К перечню полей в запросе добавляется путь (в формате XPATH) к их расположению в итоговом документе, например,

select year_beg as 'name/@beg',

title as 'name',

src as 'name/from/city/text()',

dest as 'name/to/@city'

from Roads,Builder

where road=id

for xml path('roads')

Части from и where формируют соединение по внешнему ключу.

Поля результата выводятся как

year_beg as 'name/@beg' – значение атрибута @beg элемента name,

title as 'name' – содержимое элемента name,

src as 'name/from/city/text()', содержимое элемента city, вложенного в элемент from, вложенного в элемент name,

dest as 'name/to/@city' – значение атрибута @city элемента to , вложенного в элемент name.

Последовательность полей в запросе должна указываться от элементов верхних уровней к элементам вложенных уровней (в противном случае будет ошибка).

Указание на атрибуты должно предварять указание на содержимое их элементов.

Результат:

<roads>

<name beg="2011">CMY-1

<from>

<city>москва </city>

</from>

<to city="СПб "/>

</name>

</roads>

<roads>

<name beg="1990">CMY-150

<from>

<city>москва </city>

</from>

<to city="анапа "/>

</name>

</roads>

...

Для получения вложенных XML-фрагментов допускается сочетать конструкции FOR XML, например, запрос к связанным таблицам, когда в качестве поля одной выводится XML по результату обращения к другой:

select src,

dest,

(select title, period

from Builder

where id=road

for xml raw('buld'),elements, type)

from Roads

for xml raw('road'), elements

Результат:

<road>

<src>москва </src>

<dest>СПб </dest>

<buld><title>CMY-1 </title><period>1</period></buld>

<buld><title>Dor1 </title><period>14</period></buld>

</road>

<road>

<src>москва </src>

<dest>анапа </dest>

<buld><title>CMY-150 </title><period>23</period></buld>

</road>

...

<road>

<src>минск </src>

<dest>орел </dest>

<buld><title>CMY-20 </title><period>8</period></buld>

<buld><title>CMY-21 </title><period>8</period></buld>

<buld><title>СМУ-22 </title><period>8</period></buld>

</road>

<road>

<src>Москва </src>

<dest>Калуга </dest>

</road>

Преобразование XML-документа в реляционную таблицу

Исходными данными является XML-документ, который надо привести к виду реляционной таблицы.

Документ (его содержимое) либо задается явно в окне запроса (или в процедуре), например:

declare @x xml

set @x = '...’

Или получается из указанного файла:

declare @x xml;

SET @x =( SELECT * FROM

OPENROWSET( BULK 'C:\a.xml', SINGLE_BLOB) AS x

);

, где 'C:\a.xml' – путь к файлу с XML-документом, например,

<a>

<b>111</b>

<b>2212121</b>

</a>

После определения переменной, содержащей текст XML, необходимо:

• выполнить разбор XML с помощью системной процедуры sp_xml_preparedocument:

declare @doc int

exec sp_xml_preparedocument @doc output,@x

, где @doc – идентификатор разобранного документа для последующей работы с ним.

• через идентификатор документа к последнему можно обращаться посредством функции openxml, которая принимает на вход XML документ и возвращает запрашиваемй набор строк (который адресуется конструкцией XPath)

select ...

from openxml(идентификатор, XPath, флаг)

with(имя тип XPath, ... );

, где флаг указывает способо разбора XML (относительн атрибутов=1, относительно элементов=2, комбинированный=3).

with(имя тип XPath, ... ) – задает перечень и типы полей, в которые будет записан результат. XPath не обязательный параметр и позволяет идентифицировать извлекаемое выражение.

Например,

declare @x xml

set @x = '<r>

<rr>

<city>

<from name="mos" />

<to>

<name>Piter</name>

</to>

</city>

<city>

<title>CMY-1 </title>

<period>1</period>

</city>

<city>

<title>Dor1 </title>

<period>14</period>

</city>

</rr>

<rr>

<city>

<from name="Riga" />

<to>

<name>Sochi</name>

</to>

</city>

<city>

<title>CMY-150 </title>

<period>23</period>

</city>

</rr>

</r>'

declare @doc int