Query FOR XML nidificate

Articolo
12/15/2008

In SQL Server 2000 è possibile specificare la clausola FOR XML solo al livello principale di una query SELECT. Il valore XML risultante viene restituito principalmente al client per l'ulteriore elaborazione. In SQL Server 2005, con l'introduzione del tipo di dati xml e della direttiva TYPE nelle query FOR XML, i valori XML restituiti dalle query FOR XML possono essere ulteriormente elaborati sul server.

È possibile assegnare il risultato di una query FOR XML a una variabile di tipo xml oppure utilizzare XQuery per eseguire query sul risultato, quindi assegnare tale risultato a una variabile di tipo xml per un'ulteriore elaborazione.
```
DECLARE @x xml
SET @x=(SELECT ProductModelID, Name
        FROM Production.ProductModel
        WHERE ProductModelID=122 or ProductModelID=119
        FOR XML RAW, TYPE)
SELECT @x
-- Result
--<row ProductModelID="122" Name="All-Purpose Bike Stand" />
--<row ProductModelID="119" Name="Bike Wash" />
```
Il valore XML restituito nella variabile @x può essere ulteriormente elaborato utilizzando uno dei metodi con tipo di dati xml. È ad esempio possibile recuperare il valore dell'attributo ProductModelID utilizzando il metodo value() (tipo di dati xml).
```
DECLARE @i int
SET @i = (SELECT @x.value('/row[1]/@ProductModelID[1]', 'int'))
SELECT @i
```
Nell'esempio seguente il risultato della query FOR XML viene restituito come tipo xml, perché nella clausola FOR XML è specificata la direttiva TYPE.
```
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=119 or ProductModelID=122
FOR XML RAW, TYPE,ROOT('myRoot')
```
Risultato:
```
<myRoot>
  <row ProductModelID="122" Name="All-Purpose Bike Stand" />
  <row ProductModelID="119" Name="Bike Wash" />
</myRoot>
```
Poiché il risultato è di tipo xml, è possibile applicare uno dei metodi con tipo di dati xml direttamente al valore XML, come illustrato nella query seguente. Nella query viene utilizzato il metodo query() (tipo di dati xml) per recuperare il primo elemento <row> figlio dell'elemento <myRoot>.
```
SELECT  (SELECT ProductModelID, Name
         FROM Production.ProductModel
         WHERE ProductModelID=119 or ProductModelID=122
         FOR XML RAW, TYPE,ROOT('myRoot')).query('/myRoot[1]/row[1]')
```
Risultato:
```
<row ProductModelID="122" Name="All-Purpose Bike Stand" />
```
È inoltre possibile scrivere query FOR XML nidificate in cui il risultato della query interna viene restituito alla query esterna come tipo di dati xml. Ad esempio:
```
SELECT Col1, 
       Col2, 
       ( SELECT Col3, Col4 
        FROM  T2
        WHERE T2.Col = T1.Col
        ...
        FOR XML AUTO, TYPE )
FROM T1
WHERE ...
FOR XML AUTO, TYPE
```
Dalla query precedente si noti quanto segue:
- Il valore XML generato dalla query FOR XML interna viene aggiunto al valore XML generato dalla query FOR XML esterna.
- Nella query interna viene specificata la direttiva TYPE. I dati restituiti dalla query interna sono pertanto di tipo xml. Se non si specifica la direttiva TYPE, il risultato della query FOR XML interna viene restituito come nvarchar(max) e i dati XML vengono sostituiti con entità.
Le query FOR XML nidificate consentono di avere un maggior controllo nella definizione della forma dei dati XML risultanti.
- In SQL Server 2000, per impostazione predefinita, le query in modalità RAW e AUTO generano valori XML incentrati sugli attributi, come illustrato di seguito:
```
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW
```
  Risultato incentrato sugli attributi:
```
<row ProductModelID="122" Name="All-Purpose Bike Stand" />
<row ProductModelID="119" Name="Bike Wash" />
```
  Se si specifica la direttiva ELEMENTS, è possibile recuperare tutti i valori XML incentrati sugli elementi, come illustrato di seguito:
```
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW, ELEMENTS 
```
  Risultato incentrato sugli elementi:
```
<row>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</row>
<row>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</row>
```
  Per le query FOR XML nidificate in SQL Server 2005 è possibile creare i valori XML in modo che siano in parte incentrati sugli attributi e in parte sugli elementi.
- In SQL Server 2000 è possibile creare elementi di pari livello solo formulando query in modalità EXPLICIT, ma ciò può essere eccessivamente complesso. In SQL Server 2005 è possibile generare gerarchie XML che includono elementi di pari livello eseguendo query FOR XML nidificate in modalità AUTO.
Indipendentemente dalla modalità utilizzata, le query FOR XML nidificate consentono un maggior controllo nella definizione della forma dei valori XML risultanti e possono essere utilizzate in sostituzione delle query in modalità EXPLICIT.

Esempi

A. Confronto di una query FOR XML con una query FOR XML nidificata

La query SELECT seguente recupera informazioni sulla categoria e la sottocategoria di un prodotto dal database AdventureWorks. La query non include istruzioni FOR XML nidificate.

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

Risultato parziale:

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

Se nella query si specifica la direttiva ELEMENTS, verrà restituito un risultato incentrato sugli elementi, come illustrato nel frammento di risultato seguente:

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

Si supponga quindi di voler generare una gerarchia XML costituita da una combinazione di valori XML incentrati sugli elementi e incentrati sugli attributi, come illustrato nel frammento seguente:

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

Nel frammento precedente le informazioni sulla categoria del prodotto, ad esempio il nome e l'ID della categoria, sono attributi. Le informazioni sulla sottocategoria sono invece incentrate sugli elementi. Per costruire l'elemento <ProductCategory> è possibile creare una query FOR XML come illustrato di seguito:

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

Risultato:

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

Per costruire gli elementi <ProductSubCategory> nidificati nel valore XML, è quindi necessario aggiungere una query FOR XML nidificata, come illustrato di seguito:

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

Nella query precedente si noti quanto segue:

La query FOR XML interna recupera informazioni sulla sottocategoria del prodotto. Nella query FOR XML interna viene aggiunta la direttiva ELEMENTS per generare un valore XML incentrato sugli elementi, che verrà aggiunto al valore XML generato dalla query esterna. Per impostazione predefinita la query esterna genera un valore XML incentrato sugli attributi.
Nella query interna è specificata la direttiva TYPE, di modo che venga restituito un risultato di tipo xml. Se la direttiva TYPE non viene specificata, verrà restituito un risultato di tipo nvarchar(max) e i dati XML verranno restituiti come entità.
Poiché la direttiva TYPE è specificata anche nella query esterna, il risultato della query verrà restituito al client come tipo xml.

Risultato parziale:

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

La query seguente è semplicemente un'estensione della precedente. Visualizza la gerarchia dei prodotti completa nel database AdventureWorks, che include gli elementi seguenti:

Categorie di prodotti
Sottocategorie di prodotti in ogni categoria
Modelli di prodotti in ogni sottocategoria
Prodotti per ogni modello

Per comprendere l'organizzazione del database AdventureWorks, è possibile utilizzare la query seguente:

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

Risultato parziale:

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

Se si rimuove la direttiva ELEMENTS dalla query FOR XML nidificata che genera le sottocategorie di prodotti, il risultato sarà interamente incentrato sugli attributi. È pertanto possibile scrivere questa query senza nidificazione. L'aggiunta della direttiva ELEMENTS consente di ottenere un valore XML incentrato in parte sugli attributi e in parte sugli elementi. Questo tipo di risultato non può essere generato da una query FOR XML con un solo livello.

B. Generazione di elementi di pari livello tramite query nidificate in modalità AUTO

Nell'esempio seguente viene descritta la procedura per generare elementi di pari livello tramite una query nidificata in modalità AUTO. L'unico metodo alternativo per generare un valore XML di questo tipo consiste nell'utilizzare la modalità EXPLICIT, ma ciò può essere eccessivamente complesso.

Questa query costruisce un valore XML che contiene informazioni sugli ordini di vendita, incluse le seguenti:

Informazioni contenute nell'intestazione degli ordini di vendita, ovvero SalesOrderID, SalesPersonID e OrderDate. Nel database AdventureWorks tali informazioni sono archiviate nella tabella SalesOrderHeader.
Informazioni dettagliate sugli ordini di vendita, che includono i prodotti ordinati, il prezzo unitario e la quantità ordinata. Tali informazioni sono archiviate nella tabella SalesOrderDetail.
Informazioni sul venditore, ovvero la persona che ha ricevuto l'ordine. Il codice SalesPersonID è archiviato nella tabella SalesPerson. Per questa query è necessario unire in join tale tabella con la tabella Employee, per trovare il nome del venditore.

Le due query SELECT che seguono generano valori XML con struttura lievemente diversa.

La prima query genera un valore XML in cui <SalesPerson> e <SalesOrderHeader> sono elementi di pari livello figli di <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

Nella query precedente l'istruzione SELECT più esterna esegue le operazioni seguenti:

Esegue una query sul set di righe SalesOrder, specificato nella clausola FROM. Viene restituito un valore XML con uno o più elementi <SalesOrder>.
Specifica la modalità AUTO e la direttiva TYPE. La modalità AUTO converte in formato XML il risultato della query e la direttiva TYPE restituisce il risultato come tipo xml.
Include due istruzioni SELECT nidificate, separate da una virgola (,). La prima istruzione SELECT nidificata recupera le informazioni relative all'ordine di vendita, ovvero intestazione e dettagli, mentre la seconda recupera le informazioni relative al venditore.
- L'istruzione SELECT che recupera SalesOrderID, SalesPersonID e CustomerID include a sua volta un'altra istruzione SELECT ... FOR XML nidificata (con modalità AUTO e direttiva TYPE), che restituisce i dettagli dell'ordine di vendita..

L'istruzione SELECT che recupera le informazioni relative al venditore esegue una query sul set di righe SalesPerson, creato nella clausola FROM. Per consentire l'esecuzione delle query FOR XML, è necessario specificare un nome per il set di righe anonimo generato nella clausola FROM. In questo caso viene specificato il nome SalesPerson.

Risultato parziale:

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

La query seguente genera le stesse informazioni relative all'ordine di vendita, con la differenza che nel valore XML risultante <SalesPerson> è un elemento di pari livello di <SalesOrderDetail>:

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

La query è riportata di seguito:

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

Risultato:

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

Poiché la direttiva TYPE restituisce il risultato della query come tipo xml, è possibile eseguire una query sul valore XML risultante utilizzando vari metodi con tipo di dati xml. Per ulteriori informazioni, vedere l'argomento relativo ai metodi per i tipi di dati xml. In questa query di esempio, si noti quanto segue:

Alla query precedente viene aggiunta la clausola FROM. Il risultato della query viene restituito sotto forma di tabella. Si noti che viene aggiunto l'alias XmlCol.

La clausola SELECT specifica una query XQuery sul valore XmlCol restituito nella clausola FROM. Per specificare la query XQuery viene utilizzato il metodo query() con tipo di dati xml. Per ulteriori informazioni, vedere Metodo query() con tipo di dati XML.

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)

C. Creazione di un'applicazione ASPX per il recupero di informazioni sugli ordini di vendita tramite browser

Nell'esempio seguente un'applicazione aspx esegue una stored procedure e restituisce informazioni sugli ordini di vendita come valori XML. Il risultato viene visualizzato nel browser. L'istruzione SELECT nella stored procedure è simile a quella nell'esempio B, con la differenza che il valore XML risultante è incentrato sugli elementi.

CREATE PROC GetSalesOrderInfo AS
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, ELEMENTS)
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
GO

L'applicazione aspx è riportata di seguito. Esegue la stored procedure e restituisce il valore XML nel browser:

<%@LANGUAGE=C# Debug=true %>
<%@import Namespace="System.Xml"%>
<%@import namespace="System.Data.SqlClient" %><%
Response.Expires = -1;
Response.ContentType = "text/xml";
%>

<%
using(System.Data.SqlClient.SqlConnection c = new System.Data.SqlClient.SqlConnection("Data Source=server;Database=AdventureWorks;Integrated Security=SSPI;"))
using(System.Data.SqlClient.SqlCommand cmd = c.CreateCommand())
{
   cmd.CommandText = "GetSalesOrderInfo";
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Connection.Open();
   System.Xml.XmlReader r = cmd.ExecuteXmlReader();
   System.Xml.XmlTextWriter w = new System.Xml.XmlTextWriter(Response.Output);
   w.WriteStartElement("Root");
   r.MoveToContent();
   while(! r.EOF)
   {
      w.WriteNode(r, true);
   }
   w.WriteEndElement();
   w.Flush();
}
%>

Per testare l'applicazione

Creare la stored procedure nel database AdventureWorks.
Salvare l'applicazione aspx nella directory c:\inetpub\wwwroot (GetSalesOrderInfo.aspx).
Eseguire l'applicazione (https://server/GetSalesOrderInfo.aspx).

D. Costruzione di un valore XML che include i prezzi dei prodotti

Nell'esempio seguente viene eseguita una query sulla tabella Production.Product per recuperare i valori di ListPrice e StandardCost per un prodotto specifico. Per rendere la query più interessante, entrambi i prezzi vengono restituiti in un elemento <Price> e ogni elemento <Price> include un attributo PriceType. La struttura prevista del valore XML è la seguente:

<xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" elementFormDefault="qualified">
  <xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="https://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="Production.Product" type="xsd:anyType" />
</xsd:schema>
<Production.Product xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" ProductID="520">
  <Price  PriceType="ListPrice">133.34</Price>
  <Price  PriceType="StandardCost">98.77</Price>
</Production.Product>

La query FOR XML nidificata è la seguente:

SELECT Product.ProductID, 
          (SELECT 'ListPrice' as PriceType, 
                   CAST(CAST(ListPrice as NVARCHAR(40)) as XML) 
           FROM    Production.Product Price 
           WHERE   Price.ProductID=Product.ProductID 
           FOR XML AUTO, TYPE),
          (SELECT  'StandardCost' as PriceType, 
                   CAST(CAST(StandardCost as NVARCHAR(40)) as XML) 
           FROM    Production.Product Price 
           WHERE   Price.ProductID=Product.ProductID 
           FOR XML AUTO, TYPE)
FROM Production.Product
WHERE ProductID=520
for XML AUTO, TYPE, XMLSCHEMA

Dalla query precedente si noti quanto segue:

L'istruzione SELECT più esterna costruisce l'elemento <Product>, che ha un attributo ProductID e due elementi <Price> figlio.
Le due istruzioni SELECT interne costruiscono due elementi <Price>, ognuno con un attributo PriceType e un valore XML che restituisce il prezzo del prodotto.
La direttiva XMLSCHEMA nell'istruzione SELECT più esterna genera lo schema XSD inline che descrive la struttura del valore XML risultante.

Per rendere la query più interessante, è possibile creare la query FOR XML e quindi creare una query XQuery sul risultato in modo da modificare la struttura del valore XML, come illustrato nella query seguente:

SELECT ProductID, 
 ( SELECT p2.ListPrice, p2.StandardCost
   FROM Production.Product p2 
   WHERE Product.ProductID = p2.ProductID
   FOR XML AUTO, ELEMENTS XSINIL, type ).query('
                                   for $p in /p2/*
                                   return 
                                    <Price PriceType = "{local-name($p)}">
                                     { data($p) }
                                    </Price>
                                  ')
FROM Production.Product
WHERE ProductID = 520
FOR XML AUTO, TYPE

Nell'esempio precedente viene utilizzato il metodo query() con tipo di dati xml per eseguire una query sul valore XML restituito dalla query FOR XML interna e costruire il risultato previsto.

Risultato:

<Production.Product ProductID="520">
  <Price PriceType="ListPrice">133.3400</Price>
  <Price PriceType="StandardCost">98.7700</Price>
</Production.Product>

Condividi tramite

Query FOR XML nidificate

Esempi

A. Confronto di una query FOR XML con una query FOR XML nidificata

B. Generazione di elementi di pari livello tramite query nidificate in modalità AUTO

C. Creazione di un'applicazione ASPX per il recupero di informazioni sugli ordini di vendita tramite browser

Per testare l'applicazione

D. Costruzione di un valore XML che include i prezzi dei prodotti

Risorse aggiuntive