Udostępnij za pośrednictwem

Generowanie rodzeństwa za pomocą zagnieżdżonego zapytania w trybie AUTO

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

W poniższym przykładzie pokazano, jak wygenerować rodzeństwo przy użyciu zagnieżdżonego zapytania w trybie AUTO. Jedynym innym sposobem generowania takiego kodu XML jest użycie trybu JAWNE. Może to być jednak kłopotliwe.

Przykład

To zapytanie konstruuje kod XML, który zawiera informacje o zamówieniach sprzedaży. Obejmuje to następujące elementy:

  • Informacje nagłówka zamówienia sprzedaży: SalesOrderID, SalesPersonID, i OrderDate. AdventureWorks2022 przechowuje te informacje w tabeli SalesOrderHeader.

  • Szczegóły zamówienia sprzedaży. Obejmuje to co najmniej jeden zamówiony produkt, cenę jednostkową i ilość zamówioną. Te informacje są przechowywane w tabeli SalesOrderDetail.

  • Informacje o osobach sprzedaży. Jest to sprzedawca, który przyjął zamówienie. Tabela SalesPerson zawiera SalesPersonID. W przypadku tego zapytania musisz dołączyć tę tabelę do tabeli Employee, aby znaleźć nazwę osoby sprzedaży.

Dwa odrębne zapytania SELECT, które następują, generują XML z niewielką różnicą w strukturze.

Pytanie pierwsze generuje XML, w którym <SalesPerson> i <SalesOrderHeader> pojawiają się jako równorzędne elementy <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;

W poprzednim zapytaniu najbardziej zewnętrzna instrukcja SELECT wykonuje następujące czynności:

  • Zapytanie do zestawu wierszy SalesOrder, określonego w klauzuli FROM. Wynikiem jest kod XML z co najmniej jednym elementem <SalesOrder>.

  • Określa tryb AUTO i dyrektywę TYPE. AUTO tryb przekształca wynik zapytania w kod XML, a dyrektywa TYPE zwraca wynik jako typ xml.

  • Zawiera dwie zagnieżdżone instrukcje SELECT, rozdzielone przecinkiem. Pierwsze zagnieżdżone zapytanie SELECT pobiera informacje o zamówieniach sprzedaży, nagłówkach i szczegółach, a drugie zagnieżdżone zapytanie SELECT pobiera informacje o sprzedawcy.

    • Instrukcja SELECT, która pobiera SalesOrderID, SalesPersonIDi CustomerID, zawiera zagnieżdżoną instrukcję SELECT ... FOR XML (z trybem AUTO i dyrektywą TYPE), która zwraca szczegółowe informacje o zamówieniu sprzedaży.

Instrukcja SELECT, która pobiera informacje o osobie sprzedaży, wykonuje zapytania dotyczące zestawu wierszy, SalesPerson, utworzonego w klauzuli FROM. Aby zapytania FOR XML działały, należy podać nazwę anonimowego zestawu wierszy wygenerowanego w klauzuli FROM. W tym przypadku podana nazwa to SalesPerson.

Jest to wynik częściowy:

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

Następujące zapytanie generuje te same informacje o zamówieniu sprzedaży, z tą różnicą, że w wynikowym kodzie XML <SalesPerson> jest wyświetlana jako element równorzędny <SalesOrderDetail>:

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

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

Jest to zapytanie:

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;

Jest to wynik:

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

Ponieważ dyrektywa TYPE zwraca wynik zapytania jako typ xml, możesz wykonać zapytanie względem wynikowego kodu XML przy użyciu różnych metod typu danych xml. Aby uzyskać więcej informacji, zobacz metody typów danych xml . W poniższym zapytaniu zwróć uwagę na następujące kwestie:

  • Poprzednie zapytanie jest dodawane w klauzuli FROM. Wynik zapytania jest zwracany jako tabela. Zwróć uwagę na dodany alias XmlCol.

  • Klauzula SELECT określa zapytanie XQuery dla XmlCol, które jest zwrócone w klauzuli FROM. Metoda query() typu danych xml jest używana w specyfikacji zapytania XQuery. Aby uzyskać więcej informacji, zobacz 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);

Zobacz też