FLWOR utasítás és iteráció (XQuery)

A következőkre vonatkozik:SQL Server

Az XQuery az FLWOR iterációs szintaxist határozza meg. Az FLWOR az , , forlet, whereés order by. betűszóreturn.

Az FLWOR utasítás a következő részekből áll:

• Egy vagy több FOR záradék, amely egy vagy több iterátorváltozót köt a bemeneti szekvenciákhoz.

  A bemeneti szekvenciák lehetnek más XQuery-kifejezések, például XPath-kifejezések. Ezek vagy csomópontok sorozatai vagy atomi értékek sorozatai. Az atomi értékütemezések konstruktor- vagy konstruktorfüggvényekkel hozhatók létre. A létrehozott XML-csomópontok nem használhatók bemeneti sorozatként az SQL Serverben.

• Nem kötelező let záradék. Ez a záradék egy értéket rendel az adott változóhoz egy adott iterációhoz. A hozzárendelt kifejezés lehet XQuery-kifejezés, például XPath-kifejezés, és csomópontok sorozatát vagy atomi értékek sorozatát is visszaadhatja. Az atomi értékütemezések konstruktor- vagy konstruktorfüggvényekkel hozhatók létre. A létrehozott XML-csomópontok nem használhatók bemeneti sorozatként az SQL Serverben.

• Iterátorváltozó. Ez a változó a kulcsszó használatával as választható típusmegáltalással rendelkezhet.

• Nem kötelező where záradék. Ez a záradék egy szűrő predikátumot alkalmaz az iterációra.

• Nem kötelező order by záradék.

• Egy return kifejezés. A záradékban szereplő return kifejezés az FLWOR utasítás eredményét hozza létre.

Az alábbi lekérdezés például az <Step> első gyártási helyen az elemeken iktat, és visszaadja a <Step> csomópontok sztringértékét:

DECLARE @x AS 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)
');

Az eredmény a következő:

Manu step 1 at Loc 1 Manu step 2 at Loc 1 Manu step 3 at Loc 1

A következő lekérdezés az előzőhöz hasonló, azzal a kivételrel, hogy a ProductModel tábla Utasítások oszlopában van megadva, egy beírt xml-oszlopban. A lekérdezés végigvezeti az összes gyártási lépést, <step> elemet egy adott termék első műhelyhelyén.

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;

Jegyezze fel az előző lekérdezésben szereplő következőket:

• Az $Step iterátor változója.

• Az elérési út kifejezés//AWMI:root/AWMI:Location[1]/AWMI:step a bemeneti sorozatot hozza létre. Ez a sorozat az első<step>elemcsomópont <Location> elemcsomópontjainak sorrendje.

• A választható predikátum záradék wherenem használatos.

• A return kifejezés egy sztringértéket ad vissza az <step> elemből.

A sztringfüggvény (XQuery) a csomópont sztringértékének lekérésére <step> szolgál.

A részleges eredmény a következő:

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

Ilyenek például az engedélyezett egyéb bemeneti sorozatok:

DECLARE @x AS XML;

SET @x = '';

SELECT @x.query('
for $a in (1, 2, 3)
  return $a');
-- result = 1 2 3

DECLARE @x AS 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 AS 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

Az SQL Serverben a heterogén szekvenciák nem engedélyezettek. Az atomértékek és csomópontok keverékét tartalmazó sorozatok nem engedélyezettek.

Az iterációt gyakran használják az XML-konstrukciós (XQuery) szintaxissal együtt az XML-formátumok átalakításában, ahogyan az a következő lekérdezésben is látható.

Az AdventureWorks mintaadatbázisban a tábla oszlopában InstructionsProduction.ProductModel tárolt gyártási utasítások a következő formában találhatók:

<Location LocationID="10" LaborHours="1.2"
            SetupHours=".2" MachineHours=".1">
  <step>describes 1st manu step</step>
   <step>describes 2nd manu step</step>
   ...
</Location>
...

Az alábbi lekérdezés olyan új XML-fájlt hoz létre, amely azokat az <Location> elemeket tartalmazza, amelyekben a műhely helyének attribútumai gyermekelemként vannak visszaadva:

<Location>
   <LocationID>10</LocationID>
   <LaborHours>1.2</LaborHours>
   <SetupHours>.2</SetupHours>
   <MachineHours>.1</MachineHours>
</Location>
...

A lekérdezés a következő:

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;

Vegye figyelembe az előző lekérdezés alábbi szempontjait:

• Az FLWOR utasítás egy adott termék elemeinek sorozatát <Location> kéri le.

• Az adatfüggvény (XQuery) az egyes attribútumok értékének kinyerésére szolgál, így azokat az eredményként kapott XML-fájlhoz szövegcsomópontokként adja hozzá, nem attribútumként.

• A záradékban lévő RETURN kifejezés a kívánt XML-fájlt hozza létre.

Ez egy részleges eredmény:

<Location>
  <LocationID>10</LocationID>
  <LaborHours>2.5</LaborHours>
  <SetupHours>0.5</SetupHours>
  <MachineHours>3</MachineHours>
</Location>
<Location>
   ...
<Location>
...

let A záradék használata

A let záradék használatával ismétlődő kifejezéseket nevezhet el, amelyekre hivatkozhat a változóra hivatkozva. A változóhoz let rendelt kifejezés minden alkalommal be lesz szúrva a lekérdezésbe, amikor a változóra hivatkozik a lekérdezés. Ez azt jelenti, hogy az utasítás végrehajtása annyiszor történik meg, amennyi a kifejezésre hivatkozik.

Az adatbázisban a AdventureWorks2025 gyártási utasítások információkat tartalmaznak a szükséges eszközökről és az eszközök felhasználási helyéről. Az alábbi lekérdezés a let záradék használatával felsorolja az éles modell létrehozásához szükséges eszközöket, valamint azokat a helyeket, ahol az egyes eszközökre szükség van.

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 A záradék használata

A where záradék használatával szűrheti az iteráció eredményeit. Ezt a következő példában szemlélteti.

A kerékpárgyártás során a gyártási folyamat számos munkaterületen megy keresztül. Minden műhelyhely a gyártási lépések sorozatát határozza meg. Az alábbi lekérdezés csak azokat a munkaterületeket kéri le, amelyek kerékpármodellt gyártanak, és háromnál kevesebb gyártási lépéssel rendelkeznek. Vagyis háromnál <step> kevesebb elemük van.

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;

Jegyezze fel a következőket az előző lekérdezésben:

• A where kulcsszó a függvényt használja a count() gyermekelemek számának <step> megszámlálására az egyes műhelyhelyeken.

• A return kifejezés az iteráció eredményeiből hozza létre a kívánt XML-t.

Az eredmény a következő:

<Location LocationID="30"/>

A záradékban szereplő where kifejezés eredménye logikai értékké alakul az alábbi szabályok használatával, a megadott sorrendben. Ezek megegyeznek az elérésiút-kifejezések predikátumainak szabályaival, kivéve, hogy az egész számok nem engedélyezettek:

1. Ha a where kifejezés üres sorozatot ad vissza, akkor a tényleges logikai értéke Hamis.

2. Ha a where kifejezés egy egyszerű logikai típusú értéket ad vissza, akkor ez az érték a tényleges logikai érték.

3. Ha a where kifejezés olyan sorozatot ad vissza, amely legalább egy csomópontot tartalmaz, a tényleges logikai érték Igaz.

4. Ellenkező esetben statikus hibát okoz.

Többváltozós kötés az FLWOR-ban

Egyetlen FLWOR-kifejezéssel rendelkezhet, amely több változót köt össze a bemeneti szekvenciákhoz. Az alábbi példában a lekérdezés egy nem beírt XML-változóval van megadva. A FLOWR kifejezés az egyes <Step> elemek első <Location> elemét adja vissza.

DECLARE @x AS 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)
');

Jegyezze fel az előző lekérdezésben szereplő következőket:

• A for kifejezés definiálja $Loc és $FirstStep változók.

• A two kifejezések /ManuInstructions/Location és $FirstStep in $Loc/Step[1]az , összefüggésben vannak azzal, hogy az értékek $FirstStep a függvény értékeitől függenek $Loc.

• A társított $Loc kifejezés elemek sorozatát <Location> hozza létre. Minden <Location> elemhez $FirstStep egyetlen <Step> elemből, egyetlentonból álló sorozatot hoz létre.

• $Loc a változóhoz $FirstStep társított kifejezésben van megadva.

Az eredmény a következő:

Manu step 1 at Loc 1
Manu step 1 at Loc 2

A következő lekérdezés hasonló, azzal a kivételrel, hogy a tábla Utasítások oszlopában van megadva, beírt xml-oszlopbanProductModel. Az XML-konstrukció (XQuery) a kívánt XML létrehozásához használható.

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;

Jegyezze fel a következőket az előző lekérdezésben:

• A for záradék két változót határoz meg, $WC és $S. A társított $WC kifejezés egy kerékpártermék-modell gyártásában a műhelyek helyszíneinek sorozatát hozza létre. A változóhoz $S rendelt elérésiút-kifejezés egy lépéssort hoz létre az egyes műhelyhelyütemezésekhez a $WC.

• A visszatérési utasítás olyan XML-t hoz létre, amely egy <Step> olyan elemet tartalmaz, amely tartalmazza a gyártási lépést és annak LocationID attribútumát.

• Az XQuery prologban az alapértelmezett deklarálási elem névterét használja a rendszer, hogy az eredményül kapott XML-fájlban lévő névtér-deklarációk a legfelső szintű elemnél jelenjenek meg. Ez olvashatóbbá teszi az eredményt. Az alapértelmezett névterekről további információt az XQuery névtereinek kezelése című témakörben talál.

A részleges eredmény a következő:

<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 A záradék használata

A rendezés az XQueryben az order by FLWOR kifejezés záradékával történik. A záradéknak átadott rendezési kifejezéseknek olyan order by értékeket kell visszaadnia, amelyek típusa érvényes az gt operátorra. Minden rendezési kifejezésnek egyetlen elemet tartalmazó sorozatot kell eredményeznie. Alapértelmezés szerint a rendezés növekvő sorrendben történik. Igény szerint megadhat növekvő vagy csökkenő sorrendet az egyes rendezési kifejezésekhez.

Megjegyzés:

Az SQL Server XQuery-implementációja által végrehajtott sztringértékek összehasonlításának rendezése mindig a bináris Unicode kódpont-rendezéssel történik.

Az alábbi lekérdezés lekéri egy adott ügyfél összes telefonszámát az AdditionalContactInfo oszlopból. Az eredmények telefonszám szerint vannak rendezve.

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;

Az atomizálási (XQuery) folyamat lekéri az elemek atomi értékét, <number> mielőtt átadja azt a .order by A kifejezést a data() függvény használatával is megírhatja, de ez nem kötelező.

order by data($a/act:number[1]) descending

Az eredmény a következő:

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

A névterek lekérdezési prologban való deklarálása helyett deklarálhatja őket a használatával 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;

Az attribútumértékek szerint is rendezhet. Az alábbi lekérdezés például lekéri az újonnan létrehozott <Location> elemeket, amelyek LocationID és LaborHours attribútumait csökkenő sorrendben rendezi a LaborHours attribútum. Ennek eredményeképpen a rendszer először a maximális munkaórákkal rendelkező műhelyhelyeket adja vissza.

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;

Az eredmény a következő:

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

A következő lekérdezésben az eredmények elemnév szerint vannak rendezve. A lekérdezés lekéri egy adott termék specifikációit a termékkatalógusból. A specifikációk az elem gyermekei <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;

Jegyezze fel az előző lekérdezésben szereplő következőket:

• A /p1:ProductDescription/p1:Specifications/* kifejezés a gyermek elemét <Specifications>adja vissza.

• A order by (local-name($a)) kifejezés az elemnév helyi része alapján rendezi a sorrendet.

Az eredmény a következő:

<Color>Available in most colors</Color>
<Material>Aluminum Alloy</Material>
<ProductLine>Mountain bike</ProductLine>
<RiderExperience>Advanced to Professional riders</RiderExperience>
<Style>Unisex</Style>

Azok a csomópontok, amelyekben a rendezési kifejezés üres értéket ad vissza, a sorrend elejére vannak rendezve, az alábbi példában látható módon:

DECLARE @x AS 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
');

Az eredmény a következő:

<Person />
<Person Name="A" />
<Person Name="B" />

Az alábbi példában látható módon több rendezési feltételt is megadhat. A példában szereplő lekérdezés először cím, majd rendszergazdai attribútumértékek <Employee> szerint rendezi az elemeket.

DECLARE @x AS 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
');

Az eredmény a következő:

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

Korlátozások

Ezek a korlátozások:

• A rendezési kifejezéseket homogén módon kell begépelni. Ezt a rendszer statikusan ellenőrzi.

• Az üres sorozatok rendezése nem szabályozható.

• A legkisebb üres, az üres legnagyobb és a order by rendezési kulcsszavak nem támogatottak

Kapcsolódó tartalom

• XQuery-kifejezések