FOR XML (SQL Server)FOR XML (SQL Server)

SELECT 查詢會將結果以資料列集的形式傳回。A SELECT query returns results as a rowset. 在查詢中指定 FOR XML 子句,您就可選擇以 XML 格式擷取 SQL 查詢的正式結果。You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. FOR XML 子句可以使用在最上層的查詢與子查詢中。The FOR XML clause can be used in top-level queries and in sub queries. 最上層的 FOR XML 子句只能用在 SELECT 陳述式中。The top-level FOR XML clause can be used only in the SELECT statement. 在子查詢中,FOR XML 可以在 INSERT、UPDATE 與 DELETE 陳述式中使用。In sub queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements. 它也可用在指派陳述式中。It can also be used in assignment statements.

在 FOR XML 子句中,您可以指定下列其中一個模式:In a FOR XML clause, you specify one of these modes:





RAW 模式會對資料列集中 SELECT 陳述式傳回的每一個資料列,產生一個 <資料列> 項目。The RAW mode generates a single <row> element per row in the rowset that is returned by the SELECT statement. 您可以撰寫巢狀 FOR XML 查詢來產生 XML 階層。You can generate XML hierarchy by writing nested FOR XML queries.

AUTO 模式會使用啟發學習法 (根據 SELECT 陳述式的指定方式),在所產生的 XML 中產生巢狀結構。The AUTO mode generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified. 您對產生的 XML 之外觀只有最少的控制權。You have minimal control over the shape of the XML generated. 您可以撰寫巢狀的 FOR XML 查詢,以產生 AUTO 模式啟發學習法所產生之 XML 外觀以外的 XML 階層。The nested FOR XML queries can be written to generate XML hierarchy beyond the XML shape that is generated by AUTO mode heuristics.

EXPLICIT 模式可讓您對 XML 外觀有更多的控制權。The EXPLICIT mode allows more control over the shape of the XML. 您在決定 XML 的外觀時,可依照意願混合屬性和元素。You can mix attributes and elements at will in deciding the shape of the XML. 因為查詢執行的關係,產生的結果資料列集需要有特定格式。It requires a specific format for the resulting rowset that is generated because of query execution. 這個資料列集格式之後會對應到 XML 外觀。This rowset format is then mapped into XML shape. EXPLICIT 模式的功能是可依照意願混合屬性和元素、建立包裝函數和巢狀的複雜屬性,以及建立以空格分隔的值 (例如,OrderID 屬性可能有順序識別碼值的清單) 和混合的內容。The power of EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values (for example, OrderID attribute may have a list of order ID values), and mixed contents.

不過,撰寫 EXPLICIT 模式的查詢比較繁雜。However, writing EXPLICIT mode queries can be cumbersome. 您可以使用部份新的 FOR XML 功能,例如撰寫巢狀的 FOR XML RAW/AUTO/PATH 模式查詢和 TYPE 指示詞,而不要使用 EXPLICIT 模式來產生階層。You can use some of the new FOR XML capabilities, such as writing nested FOR XML RAW/AUTO/PATH mode queries and the TYPE directive, instead of using EXPLICIT mode to generate the hierarchies. 巢狀的 FOR XML 查詢可以產生您使用 EXPLICIT 模式可產生的任何 XML。The nested FOR XML queries can produce any XML that you can generate by using the EXPLICIT mode. 如需詳細資訊,請參閱 使用巢狀 FOR XML 查詢在 FOR XML 查詢中的 TYPE 指示詞For more information, see Use Nested FOR XML Queries and TYPE Directive in FOR XML Queries.

若搭配使用 PATH 模式和巢狀的 FOR XML 查詢功能,則能夠以較簡易的方式提供 EXPLICIT 模式的彈性。The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.

只有在執行設定為這些模式的查詢時,這些模式才有效。These modes are in effect only for the execution of the query for which they are set. 它們並不會影響任何後續查詢的結果。They do not affect the results of any subsequent queries.

對於任何搭配 FOR BROWSE 子句使用的選取項目而言,FOR XML 都是無效的。FOR XML is not valid for any selection that is used with a FOR BROWSE clause.


下列 SELECT 陳述式可從 Sales.Customer 資料庫的 Sales.SalesOrderHeaderAdventureWorks2012 資料表擷取資訊。The following SELECT statement retrieves information from the Sales.Customer and Sales.SalesOrderHeader tables in the AdventureWorks2012 database. 此查詢在 AUTO 子句中指定了 FOR XML 模式:This query specifies the AUTO mode in the FOR XML clause:

USE AdventureWorks2012  
SELECT Cust.CustomerID,   
FROM Sales.Customer Cust   
INNER JOIN Sales.SalesOrderHeader OrderHeader  
ON Cust.CustomerID = OrderHeader.CustomerID  

FOR XML 子句和伺服器名稱The FOR XML Clause and Server Names

當包含 FOR XML 子句的 SELECT 陳述式在查詢中指定四部分的名稱時,若在本機電腦上執行查詢,則伺服器名稱並不會在產生的 XML 文件中傳回。When a SELECT statement with a FOR XML clause specifies a four-part name in the query, the server name is not returned in the resulting XML document when the query is executed on the local computer. 但如果是在網路伺服器上執行查詢,則伺服器名稱會以四個部分的名稱形式傳回。However, the server name is returned as the four-part name when the query is executed on a network server.

例如,請考量以下的查詢:For example, consider this query:

SELECT TOP 1 LastName  
FROM ServerName.AdventureWorks2012.Person.Person  

ServerName 是本機伺服器,查詢將會傳回下列結果:When ServerName is a local server, the query returns the following:

<AdventureWorks2012.Person.Person LastName="Achong" />  

ServerName 是網路伺服器,查詢將會傳回下列結果:When ServerName is a network server, the query returns the following:

<ServerName.AdventureWorks2012.Person.Person LastName="Achong" />  

您可以指定下列別名來避免這種模稜兩可的情況:This potential ambiguity can be avoided by specifying this alias:

SELECT TOP 1 LastName  
FROM ServerName.AdventureWorks2012.Person.Person x  

此查詢傳回的內容如下:This query returns the following:

<x LastName="Achong"/>  

另請參閱See Also

FOR XML 子句的基本語法 Basic Syntax of the FOR XML Clause
使用 FOR XML 的 RAW 模式 Use RAW Mode with FOR XML
搭配 FOR XML 使用 AUTO 模式 Use AUTO Mode with FOR XML
搭配 FOR XML 使用 PATH 模式 Use PATH Mode with FOR XML
使用 WITH XMLNAMESPACES 將命名空間加入至查詢Add Namespaces to Queries with WITH XMLNAMESPACES