Dela via

Generera syskon med en kapslad AUTO-lägesfråga

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

I följande exempel visas hur du genererar syskon med hjälp av en kapslad AUTO-lägesfråga. Det enda andra sättet att generera sådan XML är att använda EXPLICIT-läget. Detta kan dock vara besvärligt.

Exempel

Den här frågan konstruerar XML som tillhandahåller försäljningsorderinformation. Detta inkluderar följande:

  • Information om försäljningsorderhuvud, SalesOrderID, SalesPersonIDoch OrderDate. AdventureWorks2022 lagrar den här informationen i tabellen SalesOrderHeader.

  • Detaljinformation om försäljningsorder. Detta inkluderar en eller flera beställda produkter, enhetspriset och den beställda kvantiteten. Den här informationen lagras i tabellen SalesOrderDetail.

  • Information om säljare. Det här är säljaren som tog beställningen. Tabellen SalesPerson innehåller SalesPersonID. För den här frågan måste du ansluta den här tabellen till tabellen Employee för att hitta namnet på säljaren.

De två distinkta SELECT frågor som följer genererar XML med en liten skillnad i form.

Den första frågan genererar XML där <SalesPerson> och <SalesOrderHeader> visas som underordnade <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;

I föregående fråga gör den yttersta SELECT-instruktionen följande:

  • Förfrågar den raduppsättning, SalesOrder, som anges i FROM-satsen. Resultatet är en XML med ett eller flera <SalesOrder> element.

  • Anger AUTO läge och TYPE-direktivet. AUTO läge omvandlar frågeresultatet till XML och TYPE-direktivet returnerar resultatet som xml- typ.

  • Innehåller två kapslade SELECT-instruktioner avgränsade med kommatecken. Den första kapslade SELECT hämtar information om försäljningsorder, sidhuvud och information, och den andra kapslade SELECT-instruktionen hämtar information om säljare.

    • Den SELECT-instruktion som hämtar SalesOrderID, SalesPersonIDoch CustomerID innehåller en annan kapslad SELECT ... FOR XML-instruktion (med AUTO-läge och TYPE-direktiv) som returnerar försäljningsorderinformation.

Instruktionen SELECT som hämtar säljareinformation förfrågar en raduppsättning SalesPersonsom har skapats i FROM-villkoret. För att få FOR XML-frågor att fungera måste du ange ett namn för den anonyma resultatuppsättning som genereras av FROM-satsen. I det här fallet är det angivna namnet SalesPerson.

Detta är det partiella resultatet:

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

Följande fråga genererar samma försäljningsorderinformation, förutom att i den resulterande XML-koden visas <SalesPerson> som ett syskon till <SalesOrderDetail>:

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

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

Det här är frågan:

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;

Det här är resultatet:

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

Eftersom TYPE-direktivet returnerar ett frågeresultat som xml- typ kan du fråga den resulterande XML-koden med hjälp av olika xml- datatypsmetoder. Mer information finns i xml-datatypsmetoder. Observera följande i följande fråga:

  • Föregående fråga läggs till i FROM-satsen. Frågeresultatet returneras som en tabell. Observera det XmlCol alias som läggs till.

  • Klausulen SELECT anger ett XQuery mot XmlCol, vilket returneras i klausulen FROM. Den query() metoden för xml- datatyp används för att ange XQuery. Mer information finns i query() Method (xml Data Type).

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

Se även