使用 AUTO 模式

發行項
12/15/2008

如＜使用 FOR XML 擷取 XML 文件＞主題中所述，AUTO 模式會將查詢結果傳回為巢狀 XML 元素。這對於從查詢結果產生出來的 XML 外觀，並未提供很大的控制權。如果您想要產生簡單的階層，AUTO 模式查詢會很有用。不過，在決定從查詢結果產生出來的 XML 外觀方面，使用 EXPLICIT 模式及使用 PATH 模式可以提供更大的控制權及彈性。

FROM 子句中的每個資料表都至少有一資料行是列在 SELECT 子句中，這些資料表是以 XML 元素表示。若在 FOR XML 子句中指定選用性的 ELEMENTS 選項，SELECT 子句中所列的資料行就會對應至屬性或子元素。

所產生之 XML 中的 XML 階層 (巢狀元素)，是依據 SELECT 子句中指定資料行所識別的資料表順序。因此，在 SELECT 子句中指定的資料行名稱順序是很重要的。所識別的左邊第一個資料表，會形成所產生之 XML 文件的最上層元素。由 SELECT 陳述式之資料行所識別的左邊第二個資料表，則形成最上層元素中的子元素，以此類推。

如果 SELECT 子句中列出的資料行名稱是來自 SELECT 子句先前指定之資料行所識別的資料表，這時會加入該資料行，做為已建立之元素的屬性，而不是開啟新的階層架構層級。而如果指定了 ELEMENTS 選項，便會加入資料行做為屬性。

例如，執行以下的查詢：

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

以下是部份結果：

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

請注意 SELECT 子句中的下列各項：

CustomerID 會參考 Cust 資料表。因此，會建立 <Cust> 元素，並加入 CustomerID 做為其屬性。
接著，OrderHeader.CustomerID、OrderHeader.SaleOrderID 及 OrderHeader.Status 這三個資料行會參考 OrderHeader 資料表。因此，會加入 <OrderHeader> 元素做為 <Cust> 元素的子元素，並加入這三個資料行，做為 <OrderHeader> 的屬性。
接下來，Cust.CustomerType 資料行又會參考已由 Cust.CustomerID 資料行所識別的 Cust 資料表。因此並未建立任何新元素。反之，會將 CustomerType 屬性加入先前所建立的 <Cust> 元素。
查詢會為資料表名稱指定別名。這些別名會以對應的元素名稱來顯示。
若要將所有子系集合在一個父系之下，則需要 ORDER BY。

此查詢和上一個查詢類似，不同的是 SELECT 子句會將 OrderHeader 資料表中的資料行，指定在 Cust 資料表中的資料行之前。因此，會先建立 <OrderHeader> 元素，然後再加入 <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

以下是部份結果：

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

若將 ELEMENTS 選項加入 FOR XML 子句，則會傳回元素中心的 XML。

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

以下是部份結果：

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

在此查詢中，建立 <Cust> 元素時，會一一比較資料列中的 CustomerID 值，因為 CustomerID 是資料表的主索引鍵。若未將 CustomerID 識別成資料表的主索引鍵，則會一一比較資料列中的所有資料行值 (在此查詢中是 CustomerID、CustomerType)。如果值有所差異，就會加入新的 <Cust> 元素至 XML。

在比較這些資料行的值時，如果所要比較的任何資料行中具有 text、ntext、image 或 xml 類型，FOR XML 就會認定該值是不同的 (即使該值可能是相同的)，而不加以比較。這是因為不支援比較大型物件。針對所選取的每個資料列，都會將元素加入結果中。請注意，(n)varchar(max) 及 varbinary(max) 的資料行會進行比較。

當 SELECT 子句中的資料行無法與 FROM 子句中所識別的任何資料表建立關聯 (例如：彙總資料行或計算資料行)，在清單中發現該資料行時，就會將其加入至最深之巢狀層級中的 XML 文件。若該資料行是出現在 SELECT 子句中的第一個資料行，則會將該資料行新增至最上層元素。

若在 SELECT 子句中指定星號 (*) 萬用字元，就會根據查詢引擎所傳回的資料列順序，以上述同樣的方式來決定巢狀作業。

若查詢中指定 BINARY BASE64 選項，就會以 Base64 編碼格式傳回二進位資料。依預設，若沒有指定 BINARY BASE64 選項，則 AUTO 模式支援 URL 編碼的二進位資料。意即，不是傳回二進位資料，而是傳回一個參考，此為執行查詢所在之資料庫虛擬根目錄的相對 URL。此參考可用來存取後續作業中的實際二進位資料 (使用 SQLXML ISAPI dbobject 查詢)。查詢必須提供足夠的資訊 (例如：主索引鍵資料行)，以便識別影像。

在指定查詢時，若將別名用於檢視的二進位資料行，就會在 URL 編碼的二進位資料中傳回該別名。在後續動作中該別名不具任何意義，且無法使用 URL 編碼來擷取影像。所以在利用 FOR XML AUTO 模式查詢檢視時，請勿使用別名。

瞭解用來形成傳回之 XML 的 AUTO 模式啟發式方法

AUTO 模式可依據查詢來決定所傳回之 XML 的外觀。在決定如何將元素進行巢狀化時，AUTO 模式啟發式方法會比較相鄰資料列中的資料行值。除了 ntext、text、image 及 xml 之外，所有類型的資料行都會加以比較。(n)varchar(max) 及 varbinary(max) 類型的資料行也會被比較。

下列範例說明用來決定所產生之 XML 外觀的 AUTO 模式啟發式方法：

SELECT T1.Id, T2.Id, T1.Name
FROM   T1, T2
WHERE ...
FOR XML AUTO
ORDER BY T1.Id

在決定新的 <T1> 元素要從哪裡開始時，若沒有指定 T1 資料表上的索引鍵，就會比較 T1 的所有資料行值 (ntext、text、image 及 xml 除外)。接著，假設 Name 資料行是 nvarchar(40)，且 SELECT 陳述式傳回下列資料列集：

T1.Id  T1.Name  T2.Id
-----------------------
1       Andrew    2
1       Andrew    3
1       Nancy     4

AUTO 模式啟發式方法會比較 T1 資料表中，Id 及 Name 資料行的所有值。因為前兩個資料列的 Id 及 Name 資料行具有相同值，因此結果中會加入一個具有兩個 <T2> 子元素的 <T1> 元素。

以下是所傳回的 XML：

<T1 Id="1" Name="Andrew">
    <T2 Id="2" />
    <T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
      <T2 Id="4" />
</T>

現在，假設 Name 資料行的類型為 text。AUTO 模式啟發式方法不會比較此類型的值，反之，它會假設這些值都不一樣。所產生的 XML 結果如下所示：

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

範例

下列範例說明 AUTO 模式的用法。這些查詢中有許多是針對自行車製造指示的 XML 文件來指定的，而這些文件儲存在 ProductModel 資料表的 Instructions 資料行中。如需 XML 指示的詳細資訊，請參閱＜在 AdventureWorks 資料庫中的 xml 資料類型表示法＞。

A. 擷取客戶、訂單及訂單詳細資訊

此查詢會擷取特定客戶的客戶、訂單及訂單詳細資訊。

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

因為此查詢會識別 Cust、OrderHeader、Detail 及 Product 資料表別名，所以 AUTO 模式會產生對應的元素。而 SELECT 子句中指定資料行所識別的資料表順序，會決定這些元素的階層。

以下是部份結果。

<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. 指定 GROUP BY 及彙總函數

下列查詢會傳回個別的客戶識別碼，以及客戶所要求的訂單數量。

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

以下是部份結果：

<I CustomerID="11000" NoOfOrders="3" />
<I CustomerID="11001" NoOfOrders="3" />
...

C. 在 AUTO 模式中指定計算資料行

此查詢會傳回串連的個別客戶名稱及訂單資訊。因為計算資料行被指派在此時所發現的最內層 (在此範例中為 <SOH> 元素)，因此在結果中，串連的客戶名稱會被當成 <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

以下是部份結果：

<SOH Name="David Robinett" SalesOrderID="53647" />
<SOH Name="Rebecca Robinson" SalesOrderID="72188" />

為了擷取 <IndividualCustomer> 元素 (而其 Name 屬性中，包含每個銷售訂單的標頭資訊做為子元素)，使用子 Select 陳述式來重寫查詢。內部的 Select 會建立暫存的 IndividualCustomer 資料表，且其中的計算資料行含有個別客戶的名稱。接著此資料表會與 SalesOrderHeader 資料表聯結，以取得結果。

請注意，Sales.Individual 資料表會儲存個別客戶的資訊，包括該客戶的 ContactID 值。然後再使用此 ContactID，在 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

以下是部份結果：

<IndividualCustomer Name="Jon Yang">
  <SOH SalesOrderID="43793" />
  <SOH SalesOrderID="51522" />
  <SOH SalesOrderID="57418" />
</IndividualCustomer>
...
...

D. 傳回二進位資料

此查詢從 Employees 資料表傳回員工照片。Photo 在 Employees 資料表中是屬於 image 資料行。依預設，AUTO 模式會傳回二進位資料的參考，此為執行查詢所在之資料庫虛擬根目錄的相對 URL。必須指定 EmployeeID 主要屬性來識別影像。如同此範例所說明的，在擷取影像參考時，也必須在 SELECT 子句中指定資料表的主索引鍵，以識別具唯一性的資料列。

SELECT ProductPhotoID, ThumbNailPhoto
FROM   Production.ProductPhoto 
WHERE ProductPhotoID=70
FOR XML AUTO

以下是結果：

-- result
<Production.ProductPhoto 
    ProductPhotoID="70" 
    ThumbNailPhoto= "dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@ThumbNailPhoto" />

同樣的查詢，可以用 BINARY BASE64 選項來執行，該查詢會以 Base64 編碼格式傳回二進位資料。

SELECT ProductPhotoID, ThumbNailPhoto
FROM   Production.ProductPhoto 
WHERE ProductPhotoID=70
FOR XML AUTO, BINARY BASE64

以下是結果：

-- result
<Production.ProductPhoto ProductPhotoID="70" ThumbNailPhoto="Base64 encoded photo" />

依預設，當您使用 AUTO 模式來擷取二進位資料時，將會傳回一個參考 (此為執行查詢所在之資料庫虛擬根目錄的相對 URL)，而不會傳回二進位資料。若沒有指定 BINARY BASE64 選項，就會發生這種情形。

當 AUTO 模式針對不區分大小寫之資料庫中的二進位資料，傳回其 URL 參考，而查詢中所指定的資料表或資料行名稱並不符合資料庫中的資料表或資料行名稱時，查詢仍可執行。不過，參考中所傳回的大小寫會不一致。例如：

SELECT PRODUCTPHOTOID, THUMBNAILPHOTO
FROM   Production.PRODUCTPHOTO 
WHERE PRODUCTPHOTOID=70
FOR XML AUTO

以下是結果：

<Production.PRODUCTPHOTO 
        PRODUCTPHOTOID="70" 
        THUMBNAILPHOTO= "dbobject/Production.PRODUCTPHOTO[@ProductPhotoID='70']/@ThumbNailPhoto" />

這可能會是一個問題，尤其是在針對區分大小寫的資料庫執行 dbobject 查詢時。為避免發生這個問題，查詢中指定之資料表或資料行名稱的大小寫，應該要與資料庫中資料表或資料行名稱的大小寫相符。

E. 瞭解編碼方式

此範例顯示結果中所出現的各種編碼方式。

建立下述資料表：

CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50))

將下列資料加入資料表：

INSERT INTO [Special Chars] values ('&', 0x20)
INSERT INTO [Special Chars] values ('#', 0x20)

此查詢傳回來自於該資料表的資料。並已指定 FOR XML AUTO 模式。二進位資料是以參考來傳回。

SELECT * FROM [Special Chars] FOR XML AUTO

結果如下：

<Special_x0020_Chars 
Col1="#"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='#']/@Col_x0023__x0026_2"
/>
<Special_x0020_Chars 
Col1="&amp;" 
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&amp;']/@Col_x0023__x0026_2"
/>

下述處理序是用來對結果中的特殊字元進行編碼：

在查詢結果中，所傳回之元素及屬性名稱中的 XML 與 URL 特殊字元，是使用對應之 Unicode 字元的十六進位值來進行編碼。在上述結果中，元素名稱 <Special Chars> 在傳回時會變成 <Special_x0020_Chars>。屬性名稱 <Col#&2> 在傳回時會變成 <Col_x0023__x0026_2>。XML 和 URL 特殊字元都會加以編碼。
如果元素或屬性的值包含五種標準 XML 字元實體 ('、""、<、> 及 &) 的其中任何一種，則永遠都會使用 XML 字元編碼方式來將這些 XML 特殊字元編碼。在上述結果中，<Col1> 屬性值中的 & 值會被編碼成 &。而 # 字元仍保留為 #，因為它是有效的 XML 字元，並非 XML 特殊字元。
如果元素或屬性的值包含任何於 URL 中是具有特殊意義的 URL 特殊字元，則只有位於 DBOBJECT URL 值內且當特殊字元為資料表或資料行名稱的一部份時，才會對這些字元進行編碼。在結果中，屬於資料表名稱 Col#&2 一部份的 # 字元，在 DBOJBECT URL 中被編碼成 _x0023_。

請參閱

Share via

使用 AUTO 模式

瞭解用來形成傳回之 XML 的 AUTO 模式啟發式方法

範例

A. 擷取客戶、訂單及訂單詳細資訊

B. 指定 GROUP BY 及彙總函數

C. 在 AUTO 模式中指定計算資料行

D. 傳回二進位資料

E. 瞭解編碼方式

請參閱

參考

其他資源

說明及資訊

其他資源