Utilizzo della modalità AUTO
Come illustrato nell'argomento relativo al recupero di documenti XML tramite FOR XML, la modalità AUTO restituisce i risultati di una query come elementi XML nidificati, ma non consente di controllare in modo preciso la struttura del valore XML generato. È consigliabile utilizzare query in modalità AUTO solo se si desidera generare gerarchie semplici. Le funzionalità illustrate negli argomenti relativi all'utilizzo della modalità EXPLICIT e all'utilizzo della modalità PATH offrono invece livelli superiori di flessibilità e controllo sulla struttura del valore XML restituito come risultato della 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, le colonne elencate nella clausola SELECT verranno mappate 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>, il valore CustomerID di ogni riga viene confrontato con quello 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, image o 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. Si noti che le colonne di tipo (n)varchar(max) e varbinary(max) vengono confrontate.
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.
Se nella query è specificata l'opzione BINARY BASE64, i dati binari verranno restituiti con la codifica Base64. Per impostazione predefinita, se l'opzione BINARY BASE64 non viene specificata la modalità AUTO supporta la codifica URL dei dati binari, ovvero, al posto dei dati binari viene restituito un riferimento a un URL relativo alla directory principale virtuale del database in cui è stata eseguita la query. Tale riferimento può essere utilizzato per accedere ai dati binari effettivi nelle operazioni successive, tramite la query SQLXML ISAPI dbobject. La query deve restituire una quantità di informazioni sufficiente per l'identificazione dell'immagine, ad esempio le colonne chiave primaria.
Quando si specifica una query, se per la colonna di dati binari della vista viene utilizzato un alias, tale alias verrà restituito nella codifica URL dei dati binari. Nelle operazioni successive l'alias non è significativo e non sarà possibile utilizzare la codifica URL per recuperare l'immagine. Non utilizzare pertanto gli alias per l'esecuzione di query su una vista utilizzando la modalità AUTO della clausola FOR XML.
Informazioni sull'approccio euristico della modalità AUTO per la determinazione della struttura dei valori XML restituiti
La modalità AUTO determina la struttura del valore XML restituito in base alla query. Per determinare come devono essere nidificati gli elementi, la modalità AUTO, che utilizza un approccio euristico, confronta i valori delle colonne nelle righe adiacenti. Possono essere confrontate colonne di tutti i tipi, ad eccezione di ntext, text, image e xml. Le colonne di tipo (n)varchar(max) e varbinary(max) vengono confrontate.
Nell'esempio seguente viene illustrato l'approccio euristico utilizzato dalla modalità AUTO per determinare la struttura del valore XML risultante:
SELECT T1.Id, T2.Id, T1.Name
FROM T1, T2
WHERE ...
FOR XML AUTO
ORDER BY T1.Id
Per determinare la posizione iniziale dell'elemento <T1>, se non è specificata la chiave della tabella T1 vengono confrontati tutti i valori di colonna di T1, ad eccezione di quelli di tipo ntext, text, image e xml. Si supponga quindi che la colonna Name sia di tipo nvarchar(40) e che l'istruzione SELECT restituisca il set di righe seguente:
T1.Id T1.Name T2.Id
-----------------------
1 Andrew 2
1 Andrew 3
1 Nancy 4
Utilizzando un approccio euristico, la modalità AUTO confronta tutti i valori della tabella T1, ovvero le colonne Id e Name. Poiché le prime due righe contengono gli stessi valori nelle colonne Id e Name, al risultato viene aggiunto un elemento <T1> con due elementi figlio <T2>.
Il valore XML restituito è il seguente:
<T1 Id="1" Name="Andrew">
<T2 Id="2" />
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T>
Si supponga ora che la colonna Name sia di tipo text. A causa dell'approccio euristico, la modalità AUTO non confronta valori di questo tipo, ma presuppone che siano diversi. Viene pertanto generato il valore XML illustrato di seguito:
<T1 Id="1" Name="Andrew" >
<T2 Id="2" />
</T1>
<T1 Id="1" Name="Andrew" >
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T1>
Esempi
Negli esempi seguenti viene illustrato l'utilizzo della modalità AUTO. La maggior parte delle query viene applicata a documenti XML con istruzioni per la produzione di biciclette, archiviati nella colonna Instructions della tabella ProductModel. Per ulteriori informazioni sulle istruzioni XML, vedere Rappresentazione del tipo di dati XML nel database AdventureWorks.
A. Recupero di informazioni su un cliente, su un ordine e sui dettagli di un ordine
Questa query recupera informazioni sul cliente, sull'ordine e sui dettagli dell'ordine per un cliente specifico.
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
Detail.SalesOrderID, Detail.LineTotal,Detail.ProductID,
Product.Name,
Detail.OrderQty
FROM Sales.Customer Cust,
Sales.SalesOrderHeader OrderHeader,
Sales.SalesOrderDetail Detail,
Production.Product Product
WHERE Cust.CustomerID = OrderHeader.CustomerID
AND OrderHeader.SalesOrderID = Detail.SalesOrderID
AND Detail.ProductID = Product.ProductID
AND (Cust.CustomerID=117 or Cust.CustomerID=442)
ORDER BY OrderHeader.CustomerID,
OrderHeader.SalesOrderID
FOR XML AUTO
Poiché la query identifica gli alias di tabella Cust, OrderHeader, Detail e Product, la modalità AUTO genera gli elementi corrispondenti. Anche in questo caso, la gerarchia di tali elementi è determinata dall'ordine in cui le tabelle vengono identificate dalle colonne specificate nella clausola SELECT.
Di seguito è riportato il risultato parziale.
<Cust CustomerID="117">
<OrderHeader CustomerID="117" SalesOrderID="43660">
<Detail SalesOrderID="43660" LineTotal="874.794000" ProductID="758" OrderQty="1">
<Product Name="Road-450 Red, 52" />
</Detail>
<Detail SalesOrderID="43660" LineTotal="419.458900" ProductID="762" OrderQty="1">
<Product Name="Road-650 Red, 44" />
</Detail>
</OrderHeader>
<OrderHeader CustomerID="117" SalesOrderID="47660">
<Detail SalesOrderID="47660" LineTotal="469.794000" ProductID="765" OrderQty="1">
<Product Name="Road-650 Black, 58" />
</Detail>
</OrderHeader>
<OrderHeader CustomerID="117" SalesOrderID="49857">
<Detail SalesOrderID="49857" LineTotal="44.994000" ProductID="852" OrderQty="1">
<Product Name="Women's Tights, S" />
</Detail>
</OrderHeader>
...
</Cust>
B. Impostazione della clausola GROUP BY e di funzioni di aggregazione
La query seguente restituisce gli ID di singoli clienti e il numero degli ordini effettuati da tali clienti.
SELECT I.CustomerID, count(*) as NoOfOrders
from Sales.Individual I,Sales.SalesOrderHeader SOH
WHERE I.CustomerID = SOH.CustomerID
GROUP BY I.CustomerID
FOR XML AUTO
Risultato parziale:
<I CustomerID="11000" NoOfOrders="3" />
<I CustomerID="11001" NoOfOrders="3" />
...
C. Impostazione di colonne calcolate in modalità AUTO
Questa query restituisce i nomi concatenati dei singoli clienti e le informazioni sugli ordini. Poiché la colonna calcolata viene assegnata al livello più interno rilevato fino a quel punto, che in questo esempio è l'elemento <SOH>, nel risultato i nomi concatenati dei clienti vengono aggiunti come attributi dell'elemento <SOH>.
select C.FirstName + ' ' + C.LastName as Name,
SOH.SalesOrderID
from Sales.Individual I, Person.Contact C,
Sales.SalesOrderHeader SOH
where I.ContactID = C.ContactID
AND I.CustomerID = SOH.CustomerID
FOR XML AUTO
Risultato parziale:
<SOH Name="David Robinett" SalesOrderID="53647" />
<SOH Name="Rebecca Robinson" SalesOrderID="72188" />
Per recuperare gli elementi <IndividualCustomer> che hanno come sottoelemento un attributo Name contenente le informazioni di intestazione di ogni ordine di vendita, la query viene riscritta utilizzando una sub-SELECT. L'istruzione SELECT interna crea una tabella IndividualCustomer temporanea con la colonna calcolata contenente i nomi dei singoli clienti. Tale tabella viene quindi unita in join alla tabella SalesOrderHeader per ottenere il risultato.
Nella tabella Sales.Individual sono archiviate informazioni sui singoli clienti, incluso il valore ContactID di ogni cliente. Tale valore viene quindi utilizzato per recuperare il nome del contatto dalla tabella Person.Contact.
SELECT IndividualCustomer.Name, SOH.SalesOrderID
FROM (SELECT FirstName+ ' '+LastName as Name, I.CustomerID
FROM Sales.Individual I, Person.Contact C
WHERE I.ContactID = C.ContactID) IndividualCustomer
left outer join Sales.SalesOrderHeader SOH
ON IndividualCustomer.CustomerID = SOH.CustomerID
ORDER BY IndividualCustomer.CustomerID, SOH.CustomerID
FOR XML AUTO
Risultato parziale:
<IndividualCustomer Name="Jon Yang">
<SOH SalesOrderID="43793" />
<SOH SalesOrderID="51522" />
<SOH SalesOrderID="57418" />
</IndividualCustomer>
...
...
D. Restituzione di dati binari
La query seguente restituisce la fotografia di un dipendente incluso nella tabella Employees. Photo è una colonna di tipo image nella tabella Employees. Per impostazione predefinita, la modalità AUTO restituisce ai dati binari un riferimento costituito da un URL relativo alla directory principale virtuale del database in cui viene eseguita la query. Per identificare l'immagine, è necessario specificare l'attributo chiave EmployeeID. Per recuperare un riferimento a un'immagine come illustrato nell'esempio seguente, è inoltre necessario specificare la chiave primaria della tabella nella clausola SELECT, per identificare una riga in modo univoco.
SELECT ProductPhotoID, ThumbNailPhoto
FROM Production.ProductPhoto
WHERE ProductPhotoID=70
FOR XML AUTO
Risultato:
-- result
<Production.ProductPhoto
ProductPhotoID="70"
ThumbNailPhoto= "dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@ThumbNailPhoto" />
Se si esegue la stessa query specificando l'opzione BINARY BASE64, i dati binari verranno restituiti in formato Base64.
SELECT ProductPhotoID, ThumbNailPhoto
FROM Production.ProductPhoto
WHERE ProductPhotoID=70
FOR XML AUTO, BINARY BASE64
Risultato:
-- result
<Production.ProductPhoto ProductPhotoID="70" ThumbNailPhoto="Base64 encoded photo" />
Per impostazione predefinita, quando si utilizza la modalità AUTO per recuperare dati binari, al posto dei dati binari viene restituito un riferimento a un URL relativo alla directory principale virtuale del database in cui è stata eseguita la query. Questa situazione si presenta se non è specificata l'opzione BINARY BASE64.
Quando la modalità AUTO restituisce un riferimento URL a dati binari in database senza distinzione tra maiuscole e minuscole, in cui un nome di tabella o colonna specificato nella query non corrisponde al nome della tabella o della colonna nel database, la query viene eseguita, ma la combinazione di maiuscole e minuscole nel riferimento non sarà consistente. Ad esempio:
SELECT PRODUCTPHOTOID, THUMBNAILPHOTO
FROM Production.PRODUCTPHOTO
WHERE PRODUCTPHOTOID=70
FOR XML AUTO
Risultato:
<Production.PRODUCTPHOTO
PRODUCTPHOTOID="70"
THUMBNAILPHOTO= "dbobject/Production.PRODUCTPHOTO[@ProductPhotoID='70']/@ThumbNailPhoto" />
Questo costituisce un problema soprattutto quando si eseguono query dbobject su un database con distinzione tra maiuscole e minuscole. Per evitarlo, è necessario che la combinazione di maiuscole e minuscole nel nome di tabella o colonna specificato nelle query corrisponda a quella del nome della tabella o colonna nel database.
E. Informazioni sulla codifica
In questo esempio vengono illustrate le varie operazioni di codifica eseguite sul risultato.
Creare la tabella seguente:
CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50))
Aggiungere i dati seguenti alla tabella:
INSERT INTO [Special Chars] values ('&', 0x20)
INSERT INTO [Special Chars] values ('#', 0x20)
La query restituisce i dati dalla tabella. È specificata la modalità AUTO della clausola FOR XML. I dati binari vengono restituiti come riferimento.
SELECT * FROM [Special Chars] FOR XML AUTO
Risultato:
<Special_x0020_Chars
Col1="#"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='#']/@Col_x0023__x0026_2"
/>
<Special_x0020_Chars
Col1="&"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&']/@Col_x0023__x0026_2"
/>
Di seguito viene descritto il processo di codifica dei caratteri speciali nel risultato:
- I caratteri speciali XML e URL inclusi nei nomi degli elementi e degli attributi restituiti nel risultato della query vengono codificati utilizzando il valore esadecimale del carattere Unicode corrispondente. Nell'esempio precedente il nome di elemento <
Special Chars> viene restituito come <Special_x0020_Chars>. Il nome di attributo <Col#&2> viene restituito come <Col_x0023__x0026_2>. Vengono codificati sia i caratteri XML speciali che i caratteri URL speciali. - Se i valori degli elementi o degli attributi includono una delle cinque entità carattere XML standard (', "", <, > e &), tali caratteri XML speciali verranno sempre codificati tramite la codifica dei caratteri XML. Nel risultato precedente il valore & nel valore dell'attributo <
Col1> viene codificato come &. Il simbolo di cancelletto (#), tuttavia, rimane invariato perché è un carattere XML valido e non un carattere XML speciale. - Se i valori degli elementi o degli attributi contengono eventuali caratteri URL speciali che hanno un significato particolare negli URL, tali caratteri verranno codificati solo nel valore URL DBOBJECT e unicamente se il carattere speciale fa parte del nome di una tabella o di una colonna. Nel risultato il simbolo di cancelletto (#), che fa parte del nome di tabella Col#&2, viene codificato come _x0023_ nel valore URL DBOBJECT.
Vedere anche
Riferimento
Costruzione di codice XML tramite la clausola FOR XML