Usare la modalità AUTO con FOR XML

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

Come descritto in FOR XML (SQL Server), la modalità AUTO restituisce i risultati della query come elementi XML annidati. Tuttavia, ciò non consente di controllare in modo preciso la struttura dell'XML generato dal risultato di una query. È consigliabile utilizzare query in modalità AUTO solo se si desidera generare gerarchie semplici. Tuttavia, l' uso della modalità EXPLICIT con FOR XML e l' uso della modalità PATH con FOR XML assicurano maggiore controllo e flessibilità nella definizione della forma di elementi XML dal risultato di una query.

Ogni tabella nella clausola FROM, della quale almeno una colonna viene elencata nella clausola SELECT, viene rappresentata come un elemento XML. Se per la clausola FOR XML è specificata l'opzione facoltativa ELEMENTS, verrà eseguito il mapping delle colonne elencate nella clausola SELECT ad attributi o sottoelementi.

La gerarchia XML (nidificazione degli elementi) del valore XML risultante è basata sull'ordine delle tabelle identificate dalle colonne specificate nella clausola SELECT. L'ordine in cui sono specificati i nomi delle colonne nella clausola SELECT è pertanto significativo. La prima tabella da sinistra identificata costituisce l'elemento di livello principale nel documento XML risultante. La seconda tabella da sinistra, identificata dalle colonne nell'istruzione SELECT, costituisce un sottoelemento all'interno dell'elemento di livello principale e così via.

Se un nome di colonna elencato nella clausola SELECT appartiene a una tabella già identificata da una colonna specificata in precedenza nella clausola SELECT, tale colonna verrà aggiunta come attributo dell'elemento già creato, anziché aggiungere un nuovo livello alla gerarchia. Se si specifica l'opzione ELEMENTS, la colonna verrà aggiunta come attributo.

Si esegua ad esempio la query seguente:

SELECT Cust.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO;

Risultato parziale:

<Cust CustomerID="1" CustomerType="S">
  <OrderHeader CustomerID="1" SalesOrderID="43860" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="44501" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="45283" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="46042" Status="5" />
</Cust>
...

Nella clausola SELECT si noti quanto segue:

  • CustomerID fa riferimento alla tabella Cust. Verrà pertanto creato un elemento <Cust> al quale verrà aggiunto CustomerID come attributo.

  • Poiché inoltre le tre colonne OrderHeader.CustomerID, OrderHeader.SaleOrderID e OrderHeader.Status fanno riferimento alla tabella OrderHeader, viene aggiunto un elemento <OrderHeader> come sottoelemento dell'elemento <Cust> e le tre colonne vengono aggiunte come attributi di <OrderHeader>.

  • Anche la colonna Cust.CustomerType fa riferimento alla tabella Cust, che era già stata identificata dalla colonna Cust.CustomerID. Non verranno pertanto creati nuovi elementi, ma all'elemento <Cust> creato in precedenza verrà aggiunto l'attributo CustomerType.

  • Nella query sono specificati alias per i nomi delle tabelle. Tali alias sono i nomi degli elementi corrispondenti.

  • La clausola ORDER BY è necessaria per raggruppare tutti gli elementi figlio sotto un unico elemento padre.

Questa query è simile alla precedente, con la differenza che nella clausola SELECT le colonne della tabella OrderHeader sono specificate prima di quelle della tabella Cust. Viene pertanto creato prima l'elemento <OrderHeader>, a cui viene aggiunto l'elemento figlio <Cust>.

select OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status,
       Cust.CustomerID,
       Cust.CustomerType
from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
where Cust.CustomerID = OrderHeader.CustomerID
for xml auto;

Risultato parziale:

<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5">
  <Cust CustomerID="1" CustomerType="S" />
</OrderHeader>
...

Se alla clausola FOR XML viene aggiunta l'opzione ELEMENTS, verrà restituito un valore XML incentrato sugli elementi.

SELECT Cust.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO, ELEMENTS

Risultato parziale:

<Cust>
  <CustomerID>1</CustomerID>
  <CustomerType>S</CustomerType>
  <OrderHeader>
    <CustomerID>1</CustomerID>
    <SalesOrderID>43860</SalesOrderID>
    <Status>5</Status>
  </OrderHeader>
   ...
</Cust>
...

In questa query, durante la creazione degli elementi <Cust>, i valori CustomerID di ogni riga vengono confrontati con quelli della riga successiva perché CustomerID è la chiave primaria della tabella. Se CustomerID non viene identificata come chiave primaria della tabella, tutti i valori delle colonne (in questa query, CustomerID e CustomerType) di ogni riga verranno confrontati con quelli della riga successiva. Se i valori sono diversi, al valore XML verrà aggiunto un nuovo elemento <Cust>.

Durante il confronto di questi valori di colonna, se una qualsiasi delle colonne da confrontare è di tipo text, ntext, imageo xml, FOR XML presupporrà che i valori siano diversi e non eseguirà il confronto, anche se potrebbero essere uguali. Questo avviene perché il confronto di oggetti di grandi dimensioni non è supportato. Vengono aggiunti elementi al risultato per ogni riga selezionata. Vengono confrontate le colonne di tipo (n)varchar(max) e varbinary(max).

Quando una colonna nella clausola SELECT non può essere associata a una qualsiasi delle tabelle identificate nella clausola FROM, ad esempio nel caso di una colonna aggregata o calcolata, la colonna viene aggiunta al documento XML al livello di nidificazione più basso quando viene rilevata nell'elenco. Se tale colonna viene elencata per prima nella clausola SELECT, verrà aggiunta come elemento di livello principale.

Se nella clausola SELECT è specificato il carattere jolly asterisco (*), il livello di nidificazione verrà determinato come descritto in precedenza, ovvero in base all'ordine in cui le righe vengono restituite dal motore query.

Passaggi successivi

Per ulteriori informazioni sulla modalità AUTO, vedere gli articoli seguenti:

Vedi anche