Запросы XQuery, использующие иерархию
Большинство XML-столбцов в базе данных AdventureWorks представляют собой полуструктурированные документы. Поэтому документы, хранящиеся в каждой строке, могут выглядеть по-разному. Примеры запросов в этом подразделе показывают, как извлечь данные из этих различных документов.
Примеры
А.Получение сведений о размещении цехов и о первом этапе производства в этих цехах из документов инструкций производства
Для продукта модели 7 запрос возвращает XML-структуру, которая содержит элемент <ManuInstr> с атрибутами ProductModelID и ProductModelName и один или несколько дочерних элементов <Location>.
Каждый элемент <Location> имеет свой собственный набор атрибутов и один дочерний элемент <step>. Этот дочерний элемент <step> соответствует первому этапу производства в данном цехе:
SELECT Instructions.query('
declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
<ManuInstr ProdModelID = "{sql:column("Production.ProductModel.ProductModelID") }"
ProductModelName = "{ sql:column("Production.ProductModel.Name") }" >
{
for $wc in //AWMI:root/AWMI:Location
return
<Location>
{$wc/@* }
<step1> { string( ($wc//AWMI:step)[1] ) } </step1>
</Location>
}
</ManuInstr>
') as Result
FROM Production.ProductModel
WHERE ProductModelID=7
В приведенном запросе обратите внимание на следующие моменты.
Ключевое слово namespace в XQuery Prolog определяет префикс пространства имен. Затем данный префикс используется в теле запроса.
Токены переключения контекста, {) и (}, используются для переключения запроса из режима построения XML в режим вычисления.
Функция sql:column() используется для включения реляционного значения в формируемую XML-структуру.
При формировании элемента <Location>, $wc/@* возвращает все атрибуты размещения цехов.
Функция string() возвращает строковое значение из элемента <step>.
Промежуточный результат:
<ManuInstr ProdModelID="7" ProductModelName="HL Touring Frame">
<Location LocationID="10" SetupHours="0.5"
MachineHours="3" LaborHours="2.5" LotSize="100">
<step1>Insert aluminum sheet MS-2341 into the T-85A
framing tool.</step1>
</Location>
<Location LocationID="20" SetupHours="0.15"
MachineHours="2" LaborHours="1.75" LotSize="1">
<step1>Assemble all frame components following
blueprint 1299.</step1>
</Location>
...
</ManuInstr>
Б.Поиск всех телефонных номеров в столбце AdditionalContactInfo
Следующий запрос возвращает дополнительные телефонные номера заказчиков поиском по всей иерархии элемента <telephoneNumber>. Поскольку элемент <telephoneNumber> может находиться в любом месте иерархии, запрос использует нисходящий поиск от корня иерархии (//):
SELECT AdditionalContactInfo.query('
declare namespace ci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
for $ph in /ci:AdditionalContactInfo//act:telephoneNumber
return
$ph/act:number
') as x
FROM Person.Contact
WHERE ContactID = 1
Результат:
<act:number
xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">
111-111-1111
</act:number>
<act:number
xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">
112-111-1111
</act:number>
Чтобы получить телефонные номера только верхнего уровня (дочерние элементы <telephoneNumber> элемента <AdditionalContactInfo>), следует изменить выражение FOR в запросе на:
for $ph in /ci:AdditionalContactInfo/act:telephoneNumber.