Итерация и инструкция FLWOR (XQuery)
Применимо к:
SQL Server
Язык XQuery определяет синтаксис итераций инструкции FLWOR. Слово FLWOR — это сокращение от слов for, let, where, order by и return.
Инструкция FLWOR состоит из следующих частей.
Одно или несколько предложений FOR, которые привязывают одну или несколько переменных-итераторов к входным последовательностям.
Входные последовательности также могут быть выражениями XQuery (например, выражениями XPath). Они являются либо последовательностями узлов, либо последовательностями атомарных значений. Последовательности атомарных значений могут быть получены с помощью литералов или функций-конструкторов. Созданные XML-узлы не допускаются в качестве входных последовательностей в SQL Server.
Необязательное предложение
let. Это предложение присваивает значение данной переменной для конкретной итерации. Присвоенное выражение может быть выражением XQuery, например выражением XPath, и может возвращать либо последовательность узлов, либо последовательность атомарных значений. Последовательности атомарных значений могут быть получены с помощью литералов или функций-конструкторов. Созданные XML-узлы не допускаются в качестве входных последовательностей в SQL Server.Переменная-итератор. Дополнительно для этой переменной с помощью ключевого слова
asможно указать тип.Необязательное предложение
where. Это предложение применяется в качестве предиката фильтра при итерации.Необязательное предложение
order by.Выражение
return. Выражение в предложенииreturnконструирует результат, возвращаемый инструкцией FLWOR.
Например, следующий запрос выполняет итерацию элементов в первом производственном расположении и возвращает строковое значениеStep><узлов:<Step>
declare @x xml
set @x='<ManuInstructions ProductModelID="1" ProductModelName="SomeBike" >
<Location LocationID="L1" >
<Step>Manu step 1 at Loc 1</Step>
<Step>Manu step 2 at Loc 1</Step>
<Step>Manu step 3 at Loc 1</Step>
</Location>
<Location LocationID="L2" >
<Step>Manu step 1 at Loc 2</Step>
<Step>Manu step 2 at Loc 2</Step>
<Step>Manu step 3 at Loc 2</Step>
</Location>
</ManuInstructions>'
SELECT @x.query('
for $step in /ManuInstructions/Location[1]/Step
return string($step)
')
Результат:
Manu step 1 at Loc 1 Manu step 2 at Loc 1 Manu step 3 at Loc 1
Следующий запрос похож на предыдущий, за тем исключением, что он применяется к типизированному XML-столбцу Instructions в таблице ProductModel. Запрос выполняет итерацию по всем производственным шагам, <step> элементам в первом рабочем центре для конкретного продукта.
SELECT Instructions.query('
declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
for $Step in //AWMI:root/AWMI:Location[1]/AWMI:step
return
string($Step)
') as Result
FROM Production.ProductModel
where ProductModelID=7
Обратите внимание на следующие данные из предыдущего запроса:
Переменная
$Stepявляется переменной-итератором.Выражение
//AWMI:root/AWMI:Location[1]/AWMI:stepпути, создает входную последовательность. Эта последовательность представляет собой последовательность дочерних <step> элементов первого <Location> узла элемента.Необязательное предложение-предикат
whereне указано.Выражение
returnвозвращает строковое значение из <step> элемента.
Строковая функция (XQuery) используется для извлечения строкового <step> значения узла.
Частичный результат:
Insert aluminum sheet MS-2341 into the T-85A framing tool.
Attach Trim Jig TJ-26 to the upper and lower right corners of
the aluminum sheet. ....
Ниже приведены примеры других допустимых входных последовательностей:
declare @x xml
set @x=''
SELECT @x.query('
for $a in (1, 2, 3)
return $a')
-- result = 1 2 3
declare @x xml
set @x=''
SELECT @x.query('
for $a in
for $b in (1, 2, 3)
return $b
return $a')
-- result = 1 2 3
declare @x xml
set @x='<ROOT><a>111</a></ROOT>'
SELECT @x.query('
for $a in (xs:string( "test"), xs:double( "12" ), data(/ROOT/a ))
return $a')
-- result test 12 111
В SQL Server разнородные последовательности не допускаются. В частности, недопустимы последовательности, содержащие и атомарные значения, и узлы.
Итерация часто используется вместе с синтаксисом построения XML в преобразовании форматов XML, как показано в следующем запросе.
В примере базы данных AdventureWorks инструкции по производству, хранящиеся в столбце "Инструкции " таблицы Production.ProductModel , имеют следующую форму:
<Location LocationID="10" LaborHours="1.2"
SetupHours=".2" MachineHours=".1">
<step>describes 1st manu step</step>
<step>describes 2nd manu step</step>
...
</Location>
...
Следующий запрос создает новый XML-код с <Location> элементами с атрибутами расположения центра работы, возвращаемыми в качестве дочерних элементов:
<Location>
<LocationID>10</LocationID>
<LaborHours>1.2</LaborHours>
<SetupHours>.2</SetupHours>
<MachineHours>.1</MachineHours>
</Location>
...
Запрос является таковым:
SELECT Instructions.query('
declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
for $WC in /AWMI:root/AWMI:Location
return
<Location>
<LocationID> { data($WC/@LocationID) } </LocationID>
<LaborHours> { data($WC/@LaborHours) } </LaborHours>
<SetupHours> { data($WC/@SetupHours) } </SetupHours>
<MachineHours> { data($WC/@MachineHours) } </MachineHours>
</Location>
') as Result
FROM Production.ProductModel
where ProductModelID=7
Обратите внимание на следующие данные из предыдущего запроса:
Инструкция FLWOR извлекает последовательность <
Location> элементов для определенного продукта.Функция данных (XQuery) используется для извлечения значения каждого атрибута, поэтому они будут добавлены в результирующий XML-код в виде текстовых узлов вместо атрибутов.
Выражение в предложении RETURN конструирует требуемый XML-документ.
Частичный результат:
<Location>
<LocationID>10</LocationID>
<LaborHours>2.5</LaborHours>
<SetupHours>0.5</SetupHours>
<MachineHours>3</MachineHours>
</Location>
<Location>
...
<Location>
...
Использование предложения let
Предложение let может быть использовано для именования повторяющихся выражений, на которые можно ссылаться с помощью ссылок на переменные. Выражение, присвоенное переменной let, будет вставляться в запрос каждый раз, когда эта переменная будет упоминаться в запросе. Это означает, что инструкция будет выполнена столько раз, сколько было ссылок на выражение.
В производственных инструкциях в базе данных AdventureWorks2022 хранятся сведения о необходимых инструментах и о местах их применения. В следующем запросе, чтобы составить список инструментов, необходимых для постройки производственной модели, а также мест, где будет нужен каждый инструмент, применяется предложение let.
SELECT Instructions.query('
declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
for $T in //AWMI:tool
let $L := //AWMI:Location[.//AWMI:tool[.=data($T)]]
return
<tool desc="{data($T)}" Locations="{data($L/@LocationID)}"/>
') as Result
FROM Production.ProductModel
where ProductModelID=7
Применение предложения where
Предложение можно использовать where для фильтрации результатов итерации. Это продемонстрировано в следующем примере.
При изготовлении велосипеда производственный процесс проходит через цепочку цехов. Для каждого цеха определена последовательность производственных операций. Следующий запрос получает только те цеха, которые занимаются изготовлением модели велосипеда и включают менее трех производственных операций, То есть они имеют менее трех <step> элементов.
SELECT Instructions.query('
declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
for $WC in /AWMI:root/AWMI:Location
where count($WC/AWMI:step) < 3
return
<Location >
{ $WC/@LocationID }
</Location>
') as Result
FROM Production.ProductModel
where ProductModelID=7
При рассмотрении предыдущего запроса необходимо отметить следующее.
Ключевое
whereслово использует функцию count() для подсчета количества дочерних <step> элементов в каждом расположении рабочего центра.Выражение
returnконструирует XML-документ на основании результатов итерации.
Результат:
<Location LocationID="30"/>
Результат выражения в предложении where преобразуется в логическое значение по описанным ниже правилам (в указанном порядке). Это те же правила, которые применяются к предикатам в выражениях пути, за исключением того, что в данном случае целочисленные значения недопустимы.
Если выражение
whereвозвращает пустую последовательность, действительное логическое значение равно False.Если выражение
whereвозвращает одно значение простого логического типа, результатом является это действительное логическое значение.Если выражение
whereвозвращает последовательность, в которой содержится хотя бы один узел, действительное логическое значение равно True.В остальных случаях выдается статическая ошибка.
Множественная привязка переменных в инструкции FLWOR
В одном выражении инструкции FLWOR можно привязывать к входным последовательностям несколько переменных. В следующем примере выполняется запрос к нетипизированной переменной типа xml. Выражение FLOWR возвращает первый <Step> дочерний элемент в каждом <Location> элементе.
declare @x xml
set @x='<ManuInstructions ProductModelID="1" ProductModelName="SomeBike" >
<Location LocationID="L1" >
<Step>Manu step 1 at Loc 1</Step>
<Step>Manu step 2 at Loc 1</Step>
<Step>Manu step 3 at Loc 1</Step>
</Location>
<Location LocationID="L2" >
<Step>Manu step 1 at Loc 2</Step>
<Step>Manu step 2 at Loc 2</Step>
<Step>Manu step 3 at Loc 2</Step>
</Location>
</ManuInstructions>'
SELECT @x.query('
for $Loc in /ManuInstructions/Location,
$FirstStep in $Loc/Step[1]
return
string($FirstStep)
')
Обратите внимание на следующие данные из предыдущего запроса:
Выражение
forопределяет$Locи переменные $FirstStep.Выражения
two/ManuInstructions/Locationи$FirstStep in $Loc/Step[1]коррелируются в том, что значения$FirstStepзависят от значений$Loc.Выражение, связанное с
$Locформированием последовательности <Location> элементов. Для каждого <>Locationэлемента создается последовательность одногоStep><элемента$FirstStep, одноэлементного элемента.переменная
$Locуказана в выражении, связанном с переменной$FirstStep.
Результат:
Manu step 1 at Loc 1
Manu step 1 at Loc 2
Следующий запрос аналогичен, за исключением того, что он указан для столбца "Инструкции", типизированного xml-столбца таблицы ProductModel. Xml Construction (XQuery) используется для создания нужного XML-кода.
SELECT Instructions.query('
declare default element namespace "https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
for $WC in /root/Location,
$S in $WC/step
return
<Step LocationID= "{$WC/@LocationID }" >
{ $S/node() }
</Step>
') as Result
FROM Production.ProductModel
WHERE ProductModelID=7
При рассмотрении предыдущего запроса необходимо отметить следующее.
Предложение
forопределяет две переменные:$WCи$S. Выражение, связанное с$WC, формирует последовательность цехов, участвующих в производстве велосипедов. Выражение пути, присвоенное переменной$S, формирует последовательность операций для каждого цеха, содержащегося в переменной$WC.Оператор return создает XML,который содержит <
Step> элемент, содержащий этап производства и LocationID в качестве его атрибута.Пространство имен элемента по умолчанию используется в прологе XQuery, чтобы все объявления пространства имен в результирующем XML-файле отображались на элементе верхнего уровня. Это повышает удобочитаемость результата. Дополнительные сведения о пространствах имен по умолчанию см. в разделе "Обработка пространств имен" в XQuery.
Частичный результат:
<Step xmlns=
"https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
LocationID="10">
Insert <material>aluminum sheet MS-2341</material> into the <tool>T-
85A framing tool</tool>.
</Step>
...
<Step xmlns=
"https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
LocationID="20">
Assemble all frame components following blueprint
<blueprint>1299</blueprint>.
</Step>
...
Применение предложения order by
В языке XQuery сортировка в выражении FLWOR выполняется с помощью предложения order by. Выражения сортировки, передаваемые предложению order by , должны возвращать значения, типы которых допустимы для оператора gt . Каждое сортируемое выражение должно возвращать последовательность, состоящую из одного элемента. По умолчанию сортировка выполняется в порядке возрастания. Для каждого из сортируемых выражений можно указать сортировку по возрастанию или по убыванию.
Заметка
Сортировка по строковым значениям, выполняемым реализацией XQuery в SQL Server, всегда выполняется с помощью двоичного сортировки кодовых точек Юникода.
Следующий запрос получает все телефонные номера указанного заказчика из столбца AdditionalContactInfo. Результат сортируется по номеру телефона.
USE AdventureWorks2022;
GO
SELECT AdditionalContactInfo.query('
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
for $a in /aci:AdditionalContactInfo//act:telephoneNumber
order by $a/act:number[1] descending
return $a
') As Result
FROM Person.Person
WHERE BusinessEntityID=291;
Обратите внимание, что процесс Atomization (XQuery) извлекает атомарное значение <number> элементов перед передачей в него order by. Выражение можно написать с помощью функции data(), но это не обязательно.
order by data($a/act:number[1]) descending
Результат:
<act:telephoneNumber xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">
<act:number>333-333-3334</act:number>
</act:telephoneNumber>
<act:telephoneNumber xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">
<act:number>333-333-3333</act:number>
</act:telephoneNumber>
Вместо объявления пространства имен в прологе запроса можно объявить его с помощью предложения WITH XMLNAMESPACES.
WITH XMLNAMESPACES (
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS act,
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo' AS aci)
SELECT AdditionalContactInfo.query('
for $a in /aci:AdditionalContactInfo//act:telephoneNumber
order by $a/act:number[1] descending
return $a
') As Result
FROM Person.Person
WHERE BusinessEntityID=291;
Сортировка может также выполняться по значению атрибута. Например, следующий запрос извлекает только что созданные <Location> элементы с атрибутами LocationID и WorkHours, отсортированные по атрибуту WorkHours в порядке убывания. В результате цеха, имеющие максимальное число рабочих часов, будут возвращены первыми.
SELECT Instructions.query('
declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
for $WC in /AWMI:root/AWMI:Location
order by $WC/@LaborHours descending
return
<Location>
{ $WC/@LocationID }
{ $WC/@LaborHours }
</Location>
') as Result
FROM Production.ProductModel
WHERE ProductModelID=7;
Результат:
<Location LocationID="60" LaborHours="4"/>
<Location LocationID="50" LaborHours="3"/>
<Location LocationID="10" LaborHours="2.5"/>
<Location LocationID="20" LaborHours="1.75"/>
<Location LocationID="30" LaborHours="1"/>
<Location LocationID="45" LaborHours=".5"/>
В следующем запросе результат сортируется по имени элемента. Запрос получает характеристики указанного продукта из каталога продукции. Спецификации являются дочерними элементами <Specifications> элемента.
SELECT CatalogDescription.query('
declare namespace
pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
for $a in /pd:ProductDescription/pd:Specifications/*
order by local-name($a)
return $a
') as Result
FROM Production.ProductModel
where ProductModelID=19;
Обратите внимание на следующие данные из предыдущего запроса:
Выражение
/p1:ProductDescription/p1:Specifications/*возвращает дочерние <Specifications>элементы .Выражение
order by (local-name($a))сортирует последовательность по локальной части имени элемента.
Результат:
<Color>Available in most colors</Color>
<Material>Almuminum Alloy</Material>
<ProductLine>Mountain bike</ProductLine>
<RiderExperience>Advanced to Professional riders</RiderExperience>
<Style>Unisex</Style>
Узлы, в которых сортируемые выражения возвращают пустую последовательность, будут помещены в начало, как показано в следующем примере:
declare @x xml
set @x='<root>
<Person Name="A" />
<Person />
<Person Name="B" />
</root>
'
select @x.query('
for $person in //Person
order by $person/@Name
return $person
')
Результат:
<Person />
<Person Name="A" />
<Person Name="B" />
Следующий пример показывает, каким образом можно указать несколько критериев сортировки. Запрос в этом примере сортирует <Employee> элементы сначала по заголовку, а затем по значениям атрибутов администратора.
declare @x xml
set @x='<root>
<Employee ID="10" Title="Teacher" Gender="M" />
<Employee ID="15" Title="Teacher" Gender="F" />
<Employee ID="5" Title="Teacher" Gender="M" />
<Employee ID="11" Title="Teacher" Gender="F" />
<Employee ID="8" Title="Administrator" Gender="M" />
<Employee ID="4" Title="Administrator" Gender="F" />
<Employee ID="3" Title="Teacher" Gender="F" />
<Employee ID="125" Title="Administrator" Gender="F" /></root>'
SELECT @x.query('for $e in /root/Employee
order by $e/@Title ascending, $e/@Gender descending
return
$e
')
Результат:
<Employee ID="8" Title="Administrator" Gender="M" />
<Employee ID="4" Title="Administrator" Gender="F" />
<Employee ID="125" Title="Administrator" Gender="F" />
<Employee ID="10" Title="Teacher" Gender="M" />
<Employee ID="5" Title="Teacher" Gender="M" />
<Employee ID="11" Title="Teacher" Gender="F" />
<Employee ID="15" Title="Teacher" Gender="F" />
<Employee ID="3" Title="Teacher" Gender="F" />
Ограничения реализации
Существуют следующие ограничения:
Сортируемые выражения должны быть однородно типизированы. Эта проверка выполняется статически.
Нельзя управлять сортировкой пустых последовательностей.
Ключевые слова empty least, empty greatest и collation в выражении
order byне поддерживаются.
См. также
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по