Извлечение вложенных xml–данных
Вложенные XML-данные позволяют отразить родительско-дочерние взаимоотношения в виде XML-иерархии. Доступно несколько способов создания вложенных элементов XML с использованием предложения FOR XML, в том числе:
Определение соединений в запросах в режиме AUTO.
Использование параметра TYPE во вложенных запросах для получения значений с типом xml.
Объединение нескольких универсальных таблиц с использованием инструкции UNION ALL в запросе в режиме EXPLICIT.
18. Пример извлечения данных из реляционной таблицы и преобразования к XML:
use AdventureWorksLT
select 1 as Tag, Null as Parent,
b.Name as [Type_category!1!Category],
NULL as [product!2!Name],
NULL as [product!2!Price]
FROM
SalesLT.Product a inner join SalesLT.ProductCategory b
On a.ProductCategoryID = b.ProductCategoryID
UNION ALL
select 2 as Tag, 1 as Parent, b.Name, a.Name, a.ListPrice
FROM
SalesLT.Product a inner join SalesLT.ProductCategory b on
a.ProductCategoryID = b.ProductCategoryID
ORDER BY [Type_category!1!Category],[product!2!Name]
FOR XML EXPLICIT
Результат:
<Type_category Category="Bib-Shorts" />
<Type_category Category="Bib-Shorts" />
<Type_category Category="Bib-Shorts">
<product Name="Men's Bib-Shorts, L" Price="89.9900" />
<product Name="Men's Bib-Shorts, M" Price="89.9900" />
<product Name="Men's Bib-Shorts, S" Price="89.9900" />
</Type_category>
<Type_category Category="Bike Racks">
<product Name="Hitch Rack - 4-Bike" Price="120.0000" />
</Type_category>
Индивидуальные задание:
На основе базы данных, созданной в лабораторных работах №1-3, выполнить следующие операции:
Преобразовать структуру и данные каждой из таблиц и запросов в XML формат, используя опции FOR XML RAW, FOR XML AUTO, FOR XML EXPLICIT, FOR XML PATH и вывести результаты на экран.
По таблицам разработать структуру XSD-схем и сохранить их в коллекции схем.
На основе схем сделать таблицу с двумя полями XML, один по схеме первой таблицы, другой – по схеме второй таблицы.
Используя сторонний редактор XML, создать XML файлы с примером заполнения данных о трех-пяти объектах в соответствии со структурой схем, ранее созданных. Добавить эти файлы в качестве значений полей таблицы, созданной на шаге 3.
Для выполнения заданий 2 и 3 можно ориентироваться на следующий пример:
Пример. Схема заказа на покупку
В следующем примере показана схема po.xsd, определяющая заказ на покупку. В данном примере иллюстрируется использование деклараций element и attribute. В примере также показаны определения типов simpleType и complexType.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://tempuri.org/po.xsd"
xmlns="http://tempuri.org/po.xsd" elementFormDefault="qualified">
<xs:annotation>
<xs:documentation xml:lang="en">
Purchase order schema for Example.com.
Copyright 2000 Example.com. All rights reserved.
</xs:documentation>
</xs:annotation>
<xs:element name="purchaseOrder" type="PurchaseOrderType"/>
<xs:element name="comment" type="xs:string"/>
<xs:complexType name="PurchaseOrderType">
<xs:sequence>
<xs:element name="shipTo" type="USAddress"/>
<xs:element name="billTo" type="USAddress"/>
<xs:element ref="comment" minOccurs="0"/>
<xs:element name="items" type="Items"/>
</xs:sequence>
<xs:attribute name="orderDate" type="xs:date"/>
</xs:complexType>
<xs:complexType name="USAddress">
<xs:annotation>
<xs:documentation>
Purchase order schema for Example.Microsoft.com.
Copyright 2001 Example.Microsoft.com. All rights reserved.
</xs:documentation>
<xs:appinfo>
Application info.
</xs:appinfo>
</xs:annotation>
<xs:sequence>
<xs:element name="name" type="xs:string"/>
<xs:element name="street" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="state" type="xs:string"/>
<xs:element name="zip" type="xs:decimal"/>
</xs:sequence>
<xs:attribute name="country" type="xs:NMTOKEN"
fixed="US"/>
</xs:complexType>
<xs:complexType name="Items">
<xs:sequence>
<xs:element name="item" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="productName" type="xs:string"/>
<xs:element name="quantity">
<xs:simpleType>
<xs:restriction base="xs:positiveInteger">
<xs:maxExclusive value="100"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="USPrice" type="xs:decimal"/>
<xs:element ref="comment" minOccurs="0"/>
<xs:element name="shipDate" type="xs:date" minOccurs="0"/>
</xs:sequence>
<xs:attribute name="partNum" type="SKU" use="required"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
<!-- Stock Keeping Unit, a code for identifying products -->
<xs:simpleType name="SKU">
<xs:restriction base="xs:string">
<xs:pattern value="\d{3}-[A-Z]{2}"/>
</xs:restriction>
</xs:simpleType>
</xs:schema>
Пример. Документ экземпляра заказа на покупку
В следующем примере показан документ экземпляра po.xml для схемы заказа на покупку, проверенный с помощью схемы po.xsd из предыдущего примера.
XML
<?xml version="1.0"?>
<purchaseOrder xmlns="http://tempuri.org/po.xsd" orderDate="1999-10-20">
<shipTo country="US">
<name>Alice Smith</name>
<street>123 Maple Street</street>
<city>Mill Valley</city>
<state>CA</state>
<zip>90952</zip>
</shipTo>
<billTo country="US">
<name>Robert Smith</name>
<street>8 Oak Avenue</street>
<city>Old Town</city>
<state>PA</state>
<zip>95819</zip>
</billTo>
<comment>Hurry, my lawn is going wild!</comment>
<items>
<item partNum="872-AA">
<productName>Lawnmower</productName>
<quantity>1</quantity>
<USPrice>148.95</USPrice>
<comment>Confirm this is electric</comment>
</item>
<item partNum="926-AA">
<productName>Baby Monitor</productName>
<quantity>1</quantity>
<USPrice>39.98</USPrice>
<shipDate>1999-05-21</shipDate>
</item>
</items>
</purchaseOrder>
Пример создания схемы отражения реляционной модели в xml
CREATE XML SCHEMA COLLECTION [dbo].[MyCollection11]
AS
N'<?xml version = "1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Postavka" type="PostavkaType"/>
<xsd:complexType name="PostavkaType">
<xsd:sequence>
<xsd:element name="Postavshik" type="PostavshikType"/>
<xsd:element name="Oborudovanie" type="OborudovanieType"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="PostavshikType">
<xsd:sequence>
<xsd:element name="Postavshik_name" type="xsd:string"/>
<xsd:element name="Rukovoditel_postavshika" type="xsd:string"/>
<xsd:element name="Adress_firmi_postavshika" type="xsd:string"/>
<xsd:element name="Telefon_postavshika" type="xsd:integer"/>
</xsd:sequence>
<xsd:attribute name="KodPostavshika" type="xsd:integer"/>
</xsd:complexType>
<xsd:complexType name="OborudovanieType">
<xsd:sequence>
<xsd:element name="item" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Nazvanie_oborudovaniya" type="xsd:string"/>
<xsd:element name="Gruppa" type="xsd:string"/>
<xsd:element name="Mestopolozenie" type="xsd:string"/>
<xsd:element name="Period_iznosa" type="xsd:integer"/>
<xsd:element name="Seriinii_nomer" type="xsd:integer"/>
<xsd:element name="Stoimost_priobreteniya" type="xsd:string"/>
<xsd:element name="Naznaceniya" type="xsd:string"/>
</xsd:sequence>
<xsd:attribute name="KodOborudovanie" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="KodPostavshika" type="xsd:integer"/>
</xsd:complexType>
</xsd:schema>'
Пример создания xml на базе схемы
use AdventureWorksLT
declare @x xml ([dbo].[MyCollection11])
set @x = '<Postavka>
<Postavshik KodPostavshika="1">
<Postavshik_name>Поставщик1 </Postavshik_name>
<Rukovoditel_postavshika>Руководитель1</Rukovoditel_postavshika>
<Adress_firmi_postavshika>Адрес1</Adress_firmi_postavshika>
<Telefon_postavshika>123456</Telefon_postavshika>
</Postavshik>
<Oborudovanie KodPostavshika="1">
<item KodOborudovanie = "1">
<Nazvanie_oborudovaniya>Оборудование1</Nazvanie_oborudovaniya>
<Gruppa>Группа1</Gruppa>
<Mestopolozenie>Место1</Mestopolozenie>
<Period_iznosa>2</Period_iznosa>
<Seriinii_nomer>123</Seriinii_nomer>
<Stoimost_priobreteniya>123</Stoimost_priobreteniya>
<Naznaceniya>Назначение1</Naznaceniya>
</item>
<item KodOborudovanie = "2">
<Nazvanie_oborudovaniya>Оборудование2</Nazvanie_oborudovaniya>
<Gruppa>Группа2</Gruppa>
<Mestopolozenie>Место2</Mestopolozenie>
<Period_iznosa>2</Period_iznosa>
<Seriinii_nomer>123</Seriinii_nomer>
<Stoimost_priobreteniya>123</Stoimost_priobreteniya>
<Naznaceniya>Назначение1</Naznaceniya>
</item>
</Oborudovanie>
</Postavka>'
SELECT @x