Delen via

Genereer broers en zussen met een geneste AUTO-modusquery

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

In het volgende voorbeeld ziet u hoe u broers en zussen genereert met behulp van een geneste AUTO-modusquery. De enige andere manier om dergelijke XML te genereren, is door de EXPLICIETe modus te gebruiken. Dit kan echter lastig zijn.

Voorbeeld

Met deze query wordt XML gemaakt die verkoopordergegevens levert. Dit omvat het volgende:

  • Kopinformatie over verkooporders, SalesOrderID, SalesPersonIDen OrderDate. AdventureWorks2025 slaat deze informatie op in de tabel SalesOrderHeader.

  • Details van verkooporders. Dit omvat een of meer bestelde producten, de eenheidsprijs en de bestelde hoeveelheid. Deze informatie wordt opgeslagen in de SalesOrderDetail tabel.

  • Informatie van verkoper. Dit is de verkoper die de bestelling heeft genomen. De tabel SalesPerson bevat de SalesPersonID. Voor deze query moet u deze tabel koppelen aan de Employee tabel om de naam van de verkoper te vinden.

De twee verschillende SELECT query's die volgen, genereren XML met een klein verschil in vorm.

De eerste query genereert XML waarin <SalesPerson> en <SalesOrderHeader> worden weergegeven als onderliggende elementen van <SalesOrder>:

SELECT
      (SELECT top 2 SalesOrderID, SalesPersonID, CustomerID,
         (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
           from Sales.SalesOrderDetail
            WHERE  SalesOrderDetail.SalesOrderID =
                   SalesOrderHeader.SalesOrderID
            FOR XML AUTO, TYPE)
        FROM  Sales.SalesOrderHeader
        WHERE SalesOrderHeader.SalesOrderID = SalesOrder.SalesOrderID
        for xml auto, type),
        (SELECT *
         FROM  (SELECT SalesPersonID, EmployeeID
              FROM Sales.SalesPerson, HumanResources.Employee
              WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As
                     SalesPerson
         WHERE  SalesPerson.SalesPersonID = SalesOrder.SalesPersonID
       FOR XML AUTO, TYPE)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
      FROM Sales.SalesOrderHeader, Sales.SalesPerson
      WHERE SalesOrderHeader.SalesPersonID = SalesPerson.SalesPersonID
     ) as SalesOrder
ORDER BY SalesOrder.SalesOrderID
FOR XML AUTO, TYPE;

In de vorige query voert de buitenste SELECT-instructie het volgende uit:

  • Voer queries uit op de rijset SalesOrder, die is opgegeven in de FROM-clausule. Het resultaat is een XML met een of meer <SalesOrder> elementen.

  • Hiermee specificeert u de modus van AUTO en de richtlijn TYPE. AUTO-modus transformeert het queryresultaat in XML, en de TYPE-instructie retourneert het resultaat als xml- type.

  • Bevat twee geneste SELECT verklaringen gescheiden door een komma. De eerste geneste SELECT haalt verkoopordergegevens, header en details op, en de tweede geneste SELECT-instructie haalt gegevens van de verkoopmedewerker op.

    • De SELECT-instructie waarmee SalesOrderID, SalesPersonIDen CustomerID zelf worden opgehaald, bevat een andere geneste SELECT ... FOR XML-instructie (met AUTO modus en TYPE instructie) die informatie over verkoopordergegevens retourneert.

De SELECT-instructie waarmee de informatie van de verkoper wordt opgehaald, voert een query uit op een rijset, SalesPerson, die is gemaakt in de FROM-clausule. Voor FOR XML queries moet u een naam opgeven voor de anonieme rijenset gegenereerd in de FROM clausule. In dit geval is de opgegeven naam SalesPerson.

Dit is het gedeeltelijke resultaat:

<SalesOrder>
  <Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  </Sales.SalesOrderHeader>
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</SalesOrder>
...

Met de volgende query worden dezelfde verkoopordergegevens gegenereerd, behalve dat in de resulterende XML de <SalesPerson> als een sibling van <SalesOrderDetail>verschijnt.

<SalesOrder>
    <SalesOrderHeader ...>
          <SalesOrderDetail .../>
          <SalesOrderDetail .../>
          ...
          <SalesPerson .../>
    </SalesOrderHeader>

</SalesOrder>
<SalesOrder>
  ...
</SalesOrder>

Dit is de query:

SELECT SalesOrderID, SalesPersonID, CustomerID,
             (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
              from Sales.SalesOrderDetail
              WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
              FOR XML AUTO, TYPE),
              (SELECT *
               FROM  (SELECT SalesPersonID, EmployeeID
                    FROM Sales.SalesPerson, HumanResources.Employee
                    WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
               WHERE  SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
         FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=43659 or SalesOrderID=43660
FOR XML AUTO, TYPE;

Dit is het resultaat:

<Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="117">
  <Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
  <Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>

Omdat de TYPE instructie een queryresultaat retourneert als xml- type, kunt u een query uitvoeren op de resulterende XML met behulp van verschillende xml- gegevenstypemethoden. Zie xml-gegevenstypemethodenvoor meer informatie. Noteer in de volgende query het volgende:

  • De vorige query wordt toegevoegd in de FROM-clausule. Het queryresultaat wordt geretourneerd als een tabel. Let op de XmlCol alias die wordt toegevoegd.

  • De SELECT clausule specificeert een XQuery voor de XmlCol die is geretourneerd in de FROM clausule. De query() methode van het xml- gegevenstype wordt gebruikt bij het opgeven van de XQuery. Zie query() Method (XML-gegevenstype)voor meer informatie.

    SELECT XmlCol.query('<Root> { /* } </Root>')
FROM (
SELECT SalesOrderID, SalesPersonID, CustomerID,
             (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
              from Sales.SalesOrderDetail
              WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
              FOR XML AUTO, TYPE),
              (SELECT *
               FROM  (SELECT SalesPersonID, EmployeeID
                    FROM Sales.SalesPerson, HumanResources.Employee
                    WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
               WHERE  SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
         FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID='43659' or SalesOrderID='43660'
FOR XML AUTO, TYPE ) as T(XmlCol);

Zie ook

  • Last updated on