FOR XML-Abfragen im Vergleich zu geschachtelten FOR XML-Abfragen

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

In diesem Artikel wird eine FOR XML-Abfrage mit einer einzelnen Ebene mit einer geschachtelten FOR XML-Abfrage verglichen. Ein Vorteil, den die Verwendung geschachtelter FOR XML-Abfragen bietet, besteht darin, dass Sie eine Kombination aus attributzentriertem und elementzentriertem XML-Code für Abfrageergebnisse angeben können. Das Beispiel veranschaulicht dies.

Beispiel

Die folgende SELECT -Abfrage ruft Informationen zur Produktkategorie und Produktunterkategorie aus der AdventureWorks2022 -Datenbank ab. In der Abfrage gibt es keine geschachtelte FOR XML-Anweisung.

USE AdventureWorks2022;  
GO  
SELECT   ProductCategory.ProductCategoryID,   
         ProductCategory.Name as CategoryName,  
         ProductSubCategory.ProductSubCategoryID,   
         ProductSubCategory.Name  
FROM     Production.ProductCategory, Production.ProductSubCategory  
WHERE    ProductCategory.ProductCategoryID = ProductSubCategory.ProductCategoryID  
ORDER BY ProductCategoryID  
FOR XML AUTO, TYPE  
GO

Dies ist das Teilergebnis:

<ProductCategory ProductCategoryID="1" CategoryName="Bike">  
  <ProductSubCategory ProductSubCategoryID="1" Name="Mountain Bike"/>  
  <ProductSubCategory ProductSubCategoryID="2" Name="Road Bike"/>  
  <ProductSubCategory ProductSubCategoryID="3" Name="Touring Bike"/>  
</ProductCategory>  
...

Wenn Sie die ELEMENTS -Direktive in der Abfrage angeben, erhalten Sie ein elementzentriertes Ergebnis, wie es im folgenden Ergebnisfragment gezeigt wird:

<ProductCategory>  
  <ProductCategoryID>1</ProductCategoryID>  
  <CategoryName>Bike</CategoryName>  
  <ProductSubCategory>  
    <ProductSubCategoryID>1</ProductSubCategoryID>  
    <Name>Mountain Bike</Name>  
  </ProductSubCategory>  
  <ProductSubCategory>  
     ...  
  </ProductSubCategory>  
</ProductCategory>

Gehen wir nun davon aus, dass Sie eine XML-Hierarchie generieren möchten, die eine Kombination aus attributzentriertem und elementzentriertem XML-Code darstellt, wie das im folgenden Fragment gezeigt wird:

<ProductCategory ProductCategoryID="1" CategoryName="Bike">  
  <ProductSubCategory>  
    <ProductSubCategoryID>1</ProductSubCategoryID>  
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>  
  <ProductSubCategory>  
     ...  
  <ProductSubCategory>  
     ...  
</ProductCategory>

Im oben gezeigten Fragment sind die Informationen zur Produktkategorie (z. B. die Kategorie-ID und der Kategoriename) Attribute. Allerdings sind die Informationen zur Unterkategorie elementzentriert. Um das <ProductCategory> Element zu erstellen, können Sie eine FOR XML Abfrage wie in der folgenden Abbildung gezeigt schreiben:

SELECT ProductCategoryID, Name as CategoryName  
FROM Production.ProductCategory ProdCat  
ORDER BY ProductCategoryID  
FOR XML AUTO, TYPE

Dies ist das Ergebnis:

< ProdCat ProductCategoryID="1" CategoryName="Bikes" />  
< ProdCat ProductCategoryID="2" CategoryName="Components" />  
< ProdCat ProductCategoryID="3" CategoryName="Clothing" />  
< ProdCat ProductCategoryID="4" CategoryName="Accessories" />

Um die geschachtelten <ProductSubCategory> Elemente im gewünschten XML-Code zu erstellen, fügen Sie dann eine geschachtelte FOR XML Abfrage hinzu, wie in den folgenden Beispielen gezeigt:

SELECT ProductCategoryID, Name as CategoryName,  
       (SELECT ProductSubCategoryID, Name SubCategoryName  
        FROM   Production.ProductSubCategory  
        WHERE ProductSubCategory.ProductCategoryID =   
              ProductCategory.ProductCategoryID  
        FOR XML AUTO, TYPE, ELEMENTS  
       )  
FROM Production.ProductCategory  
ORDER BY ProductCategoryID  
FOR XML AUTO, TYPE

Beachten Sie in der vorhergehenden Abfrage Folgendes:

  • Mit der inneren FOR XML -Abfrage werden Informationen zur Produktunterkategorie abgerufen. Die ELEMENTS -Direktive wird der inneren FOR XML -Abfrage hinzugefügt, um elementzentrierten XML-Code zu generieren, der dem von der äußeren Abfrage generierten XML-Code hinzugefügt wird. Standardmäßig generiert die äußere Abfrage attributzentrierten XML-Code.

  • In der inneren Abfrage wird die TYPE -Direktive angegeben, sodass das Ergebnis den xml -Typ aufweist. Wenn TYPE nicht angegeben wird, wird das Ergebnis als nvarchar(max) -Typ zurückgegeben, und die XML-Daten werden als Entitäten zurückgegeben.

  • Die TYPE -Direktive wird auch in der äußeren Abfrage angegeben. Deshalb wird das Ergebnis dieser Abfrage als xml -Typ an den Client zurückgegeben.

Dies ist das Teilergebnis:

<ProductCategory ProductCategoryID="1" CategoryName="Bike">  
  <ProductSubCategory>  
    <ProductSubCategoryID>1</ProductSubCategoryID>  
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>  
  <ProductSubCategory>  
     ...  
  <ProductSubCategory>  
     ...  
</ProductCategory>

Die folgende Abfrage ist lediglich eine Erweiterung der vorherigen Abfrage. Sie zeigt die vollständige Produkthierarchie in der AdventureWorks2022 -Datenbank. Hierzu gehören folgende Elemente:

  • Produktkategorien

  • Produktunterkategorien in jeder Kategorie

  • Produktmodelle in jeder Unterkategorie

  • Produkte in jedem Produktmodell

Die folgende Abfrage kann ggf. für das Verständnis der AdventureWorks2022 -Datenbank nützlich sein:

SELECT ProductCategoryID, Name as CategoryName,  
       (SELECT ProductSubCategoryID, Name SubCategoryName,  
               (SELECT ProductModel.ProductModelID,   
                       ProductModel.Name as ModelName,  
                       (SELECT ProductID, Name as ProductName, Color  
                        FROM   Production.Product  
                        WHERE  Product.ProductModelID =   
                               ProductModel.ProductModelID  
                        FOR XML AUTO, TYPE)  
                FROM   (SELECT distinct ProductModel.ProductModelID,   
                               ProductModel.Name  
                        FROM   Production.ProductModel,   
                               Production.Product  
                        WHERE  ProductModel.ProductModelID =   
                               Product.ProductModelID  
                        AND    Product.ProductSubCategoryID =   
                               ProductSubCategory.ProductSubCategoryID)   
                                  ProductModel  
                FOR XML AUTO, type  
               )  
        FROM Production.ProductSubCategory  
        WHERE ProductSubCategory.ProductCategoryID =   
              ProductCategory.ProductCategoryID  
        FOR XML AUTO, TYPE, ELEMENTS  
       )  
FROM Production.ProductCategory  
ORDER BY ProductCategoryID  
FOR XML AUTO, TYPE

Dies ist das Teilergebnis:

<Production.ProductCategory ProductCategoryID="1" CategoryName="Bikes">  
  <Production.ProductSubCategory>  
    <ProductSubCategoryID>1</ProductSubCategoryID>  
    <SubCategoryName>Mountain Bikes</SubCategoryName>  
    <ProductModel ProductModelID="19" ModelName="Mountain-100">  
      <Production.Product ProductID="771"   
                ProductName="Mountain-100 Silver, 38" Color="Silver" />  
      <Production.Product ProductID="772"   
                ProductName="Mountain-100 Silver, 42" Color="Silver" />  
      <Production.Product ProductID="773"   
                ProductName="Mountain-100 Silver, 44" Color="Silver" />  
        ...  
    </ProductModel>  
     ...

Wenn Sie die ELEMENTS -Direktive aus der geschachtelten FOR XML -Abfrage entfernen, mit der die Produktunterkategorien generiert werden, ist das gesamte Ergebnis attributzentriert. Sie können diese Abfrage dann ohne Schachtelung schreiben. Das Hinzufügen der ELEMENTS -Direktive ergibt einen XML-Code, der teilweise attributzentriert und teilweise elementzentriert ist. Dieses Ergebnis kann nicht durch eine FOR XML-Abfrage mit nur einer einzigen Ebene generiert werden.

Siehe auch