exec sp_xml_preparedocument @doc output,@x

Пример 1.

Выбрать все города (элементы /r/rr/city ) как результирующие строки и из каждой извлечь 'title' и 'period':

select * from openxml(@doc,'/r/rr/city')

with(title char(10) 'title',

perd char(10)'period');

Результат:

NULL NULL

CMY-1 1

Dor1 14

NULL NULL

CMY-150 23

Пример 2.

Выбрать все города (элементы /r/rr/city ) как результирующие строки и из каждой извлечь значение атрибута 'from/@name' и содержимое элемента 'to/name/text()':

select * from openxml(@doc,'/r/rr/city')

with(frm char(10) 'from/@name',

too char(10)'to/name/text()');

Результат:

mos Piter

NULL NULL

NULL NULL

Riga Sochi

NULL NULL

Построение запросов к XML данным с помощью языка Xquery

К переменным (или полям таблиц), содержащим XML, можно составлять запросы с помощью функций:

• exist(XPath) – проверяет существование указанного XPath-выражения (возвращает 1 , если есть, 0 иначе).

Пример:

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>'

select @x.exist('//city');

select @x.exist('//city[from/@name="mos"]');

select @x.exist('//city[to/name/text()="Piter"]');

select @x.exist('//city[period>20]');

В квадратных скобках указывается условие на значение.

• value(XPath, тип) – возвращает одно значение указанного XPath-выражения, преобразуя его к заданному типу данных. Выражение должно быть скалярным (единственным), иначе возникает ошибка.

Пример:

select @x.value('(//to)[1]','CHAR(10)');

select @x.value('(//from)[1]/@name','CHAR(10)');

• query(XPath) - возвращает множество фрагментов (узлов) XML, которые соответствуют указанном XPath-выражению.

Пример 1:

select @x.query('//city[from/@name="mos"]');

Результат:

<city><from name="mos" /><to><name>Piter</name></to></city>

Пример 2:

select @x.query('//from[@name="mos"]/../../city[title]');

Результат:

<city>

<title>CMY-1 </title>

<period>1</period>

</city>

<city>

<title>Dor1 </title>

<period>14</period>

</city>

• modify( строка ) – добавляет, удаляет или заменяет узлы XML. Строка задает запрос на модификацию данных.

Формат строки для вставки:

insert Expression1 (

{as first | as last} into | after | before

Expression2 )

Примеры:

set @x.modify('insert <city title="London"/> into (//rr)[1]');

set @x.modify('insert <city><title name="Paris"/></city> as first into (//rr)[1]');

set @x.modify('insert attribute period {"10"} into (//city)[1]');

select @x;

Результат:

<r>

<rr>

<city period="10"><title name="Paris" /></city>

<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>

<city title="London" />

</rr>

<rr>

<city><from name="Riga"/><to><name>Sochi</name></to></city>

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

</rr>

</r>

Формат строки для изменения (изменяет одно значение):

replace value of

Expression1

with

Expression2

Примеры:

set @x.modify('replace value of (//name[text()="Sochi"]/text())[1]

with "OLYMP"');

set @x.modify('replace value of (//from[@name="Riga"]/@name)[1]

with "EUROPE"');

select @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="EUROPE" /><to><name>OLYMP</name></to></city>

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

</rr></r>

Формат строки для удаления (удаляет множество узлов):

delete Expression

Пример:

set @x.modify('delete //city[from]');

select @x;

Результат:

<r><rr>

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

<city><title>Dor1 </title><period>14</period></city>

</rr>

<rr>

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

</rr></r>

• nodes(XPath) as Table(столбец) – преобразует XML в набор строк. Каждое XPath выражение будет преобразовано в строку. Table(столбец) – название таблицы и столбца, куда будет помещен результат. Поскольку результатом (полем) является фрагмент XML, то для извлечения из него значений применяются другие функции по работе с XML, например, value().

Пример (каждый city становится строкой, из которой извлекаются значения атрибутов и вложенных элементов):

select col.value('title[1]','char(10)') as title,

col.value('(from/@name)[1]','char(10)') as frm,

col.value('(to/name)[1]','char(10)') as tto

from @x.nodes('//city') tab(col);

Результат:

title frm tto

NULL mos Piter

CMY-1 NULL NULL

Dor1 NULL NULL

NULL Riga Sochi

CMY-150 NULL NULL

Запросы на языке Xquery с применением FLWOR

Запросы на языках XPath и XQuery выполняются в окне запросов с применением функции query().

FOR переменная IN XPath-выражение

LET перемнная:=значение

WHERE условие

ORDER BY поле

RETURN выражение

Примеры:

declare @x xml;

-- XML -DOCUMENT

set @x = '<db>

<person pid="1">

<fio>Ivanov</fio>

<adr city="Mos"/>

</person>

<person pid="2">

<fio>Vasilov</fio>

<adr city="Mos"/>

<child>Tolja</child>

</person>

<person pid="3">

<fio>Petrov</fio>

<adr city="Mos"/>

<child>Lena</child>

<child>Olja</child>

</person>

<person pid="4">

<fio>Larin</fio>

<adr city="Tomsk"/>

</person>

<org><adr city="Mos"/><title>OOO Rasvet</title>

<worker wid="1"/>

<worker wid="2"/>

</org>

<org><adr city="SPB"/><title>GosOrg</title>

<worker wid="1"/>

<worker wid="4"/>

</org>

</db>'

-- Все адреса организаций

select @x.query('//org/adr');

-- Все города

select @x.query('distinct-values( for $a in //adr

return $a/@city )');

-- Города без Петрова

select @x.query('//person/adr[not(//person[fio/text()="Petrov"]/adr/@city=./@city ) ]');

select @x.query('for $a in //person/adr

where not(//person[fio/text()="Petrov"]/adr/@city = $a/@city)

return string($a/@city)');

--Работники GosOrg

--XPAth

select @x.query('//person[@pid=//org[title/text()="GosOrg"]/worker/@wid]/fio/text()');

--FLOWR

select @x.query('for $a in //person

where $a/@pid = //org[title/text()="GosOrg"]/worker/@wid

order by ($a/fio/text())[1]

return $a/fio/text()');

--персоны без детей

select @x.query('//person[not(child)]');

-- персоны без работы

select @x.query('//person[not(//org/worker/@wid = ./@pid)]/fio');

select @x.query('for $p in //person

where not( //org/worker/@wid = $p/@pid )

return $p/fio');

-- Москва

select @x.query('let $a:=//adr[@city="Mos"]

for $b in $a

return $b ');

-- -- Примеры distinct-values - только для скалярных значений (не для узлов)

select @x.query('let $a:=//adr[@city="Mos"]/@city

let $b:=distinct-values($a)

return $b ');

-- -- Города без петрова

select @x.query(' distinct-values( for $w in //adr

let $c:=$w/@city

where $w/../fio/text()!="Petrov"

return $w/@city ) ');

-- Создание организаций и сотрудников "на лету"

select @x.query('for $w in //org

return <work>

{attribute name {data($w/title)} }

{ for $p in //person[@pid=$w/worker/@wid]

return <person>{data($p/fio)}</person>

}

</work>');

результат:

<work name="OOO Rasvet">

<person>Ivanov</person>

<person>Vasilov</person>

</work>

<work name="GosOrg">

<person>Ivanov</person>

<person>Larin</person>

</work>

Пример задания

Задание 1. Преобразование XML и реляционных данных

1.1. Определить в среде MS SQL Server Management Studio схему БД:

В среде SQL Server Management Studio создать БД lab4_91. В БД создать таблицы (или использовать имеющиеся):

"Hotel"(отель), содержит свойства:

• hname - название – строковое varchar(10), (КЛЮЧ),

• hcity – город – строковое varchar(15),

• stars – количество звезд – целое.

Создать таблицу "Client" (клиент), содержит свойства:

• fio - ФИО – строковое, varchar(50),

• num - номер – целое,

• ofhotel — отель — varchar(10),(ВНЕШНИЙ КЛЮЧ)

• id - идентификатор (КЛЮЧ - PK) – целое, автоинкремент.

Открыть таблицы на редактирование и заполнить тестовыми данными.

1.2. Преобразовать реляционные данные в формат XML:

В среде построения запросов SQL Server Management Studio продемонстрировать просмотр содержимого таблиц Клиент и Отель в формате xml (конструкции select ... for xml) в следующих вариантах:

• автоматический формат (Клиент),

• все поля — элементы (Клиент),

• все поля - атрибуты (Клиент),

• добавление корневого элемента ( persons) (Клиент),

• переименование строк (raw = Person) (Клиент),

• получение xml-схемы по умолчанию (Клиент),

• отображение значений NULL (Клиент),

• получение произвольной структуры документа (Отель+ Клиент), например:

<hotels>

<hotel star=”5” city=”Mos” >

<name> название </name>

<persons>

<person num =”1234”> Петров </person >

<person num =”454”> Иванов </person >

….

</persons>

</hotel>