Two sql tips

1、WITH common_table_expression (Transact-SQL)

指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询，并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

主题链接图标 Transact-SQL 语法约定

语法


[ WITH <common_table_expression> [ ,...n ] ] <common_table_expression>::= expression_name [ ( column_name [ ,...n ] ) ] AS ( CTE_query_definition )

参数

expression_name: 公用表表达式的有效标识符。 expression_name 必须与在同一 WITH <common_table_expression> 子句中定义的任何其他公用表表达式的名称不同，但 expression_name 可以与基表或基视图的名称相同。在查询中对 expression_name 的任何引用都会使用公用表表达式，而不使用基对象。

column_name: 在公用表表达式中指定列名。在一个 CTE 定义中不允许出现重复的名称。指定的列名数必须与 CTE_query_definition 结果集中列数匹配。只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。

CTE_query_definition

指定一个其结果集填充公用表表达式的 SELECT 语句。除了 CTE 不能定义另一个 CTE 以外，CTE_query_definition 的 SELECT 语句必须满足与创建视图时相同的要求。有关详细信息，请参阅“备注”部分和 CREATE VIEW (Transact-SQL)。

如果定义了多个 CTE_query_definition，则这些查询定义必须用下列一个集合运算符联接起来：UNION ALL、UNION、EXCEPT 或 INTERSECT。有关如何使用递归 CTE 查询定义的详细信息，请参阅下面的“备注”部分和使用公用表表达式的递归查询。

备注

创建和使用 CTE 的指南

下列指南应用于非递归 CTE。有关适用于递归 CTE 的指南，请参阅后面的“定义和使用递归 CTE 的指南”。

CTE 之后必须跟随引用部分或全部 CTE 列的单条 SELECT、INSERT、UPDATE 或 DELETE 语句。也可以在 CREATE VIEW 语句中将 CTE 指定为视图中 SELECT 定义语句的一部分。
可以在非递归 CTE 中定义多个 CTE 查询定义。定义必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。
CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。
不允许在一个 CTE 中指定多个 WITH 子句。例如，如果 CTE_query_definition 包含一个子查询，则该子查询不能包括定义另一个 CTE 的嵌套的 WITH 子句。
不能在 CTE_query_definition 中使用以下子句：
- COMPUTE 或 COMPUTE BY
- ORDER BY（除非指定了 TOP 子句）
- INTO
- 带有查询提示的 OPTION 子句
- FOR XML
- FOR BROWSE
如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾。
可以使用引用 CTE 的查询来定义游标。
可以在 CTE 中引用远程服务器中的表。
在执行 CTE 时，任何引用 CTE 的提示都可能与该 CTE 访问其基础表时发现的其他提示相冲突，这种冲突与引用查询中的视图的提示所发生的冲突相同。发生这种情况时，查询将返回错误。有关详细信息，请参阅视图解析。

定义和使用递归 CTE 指南

下列指南适用于定义递归 CTE 的情况：

递归 CTE 定义至少必须包含两个 CTE 查询定义，一个定位点成员和一个递归成员。可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。所有 CTE 查询定义都是定位点成员，但它们引用 CTE 本身时除外。
定位点成员必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。在最后一个定位点成员和第一个递归成员之间，以及组合多个递归成员时，只能使用 UNION ALL 集合运算符。
定位点成员和递归成员中的列数必须一致。
递归成员中列的数据类型必须与定位点成员中相应列的数据类型一致。
递归成员的 FROM 子句只能引用一次 CTE expression_name。
在递归成员的 CTE_query_definition 中不允许出现下列项：
- SELECT DISTINCT
- GROUP BY
- HAVING
- 标量聚合
- TOP
- LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）
- 子查询
- 应用于对 CTE_query_definition 中的 CTE 的递归引用的提示。

下列指南适用于使用递归 CTE：

无论参与的 SELECT 语句返回的列的为空性如何，递归 CTE 返回的全部列都可以为空。
如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中的一个 0 到 32,767 之间的值，来限制特定语句所允许的递归级数，以防止出现无限循环。这样就能够在解决产生循环的代码问题之前控制语句的执行。服务器范围内的默认值是 100。如果指定 0，则没有限制。每一个语句只能指定一个 MAXRECURSION 值。有关详细信息，请参阅查询提示 (Transact-SQL)。
不能使用包含递归公用表表达式的视图来更新数据。
可以使用 CTE 在查询上定义游标。CTE 是定义游标结果集的 select_statement 参数。递归 CTE 只允许使用快速只进游标和静态（快照）游标。如果在递归 CTE 中指定了其他游标类型，则该类型将转换为静态游标类型。
可以在 CTE 中引用远程服务器中的表。如果在 CTE 的递归成员中引用了远程服务器，那么将为每个远程表创建一个假脱机，这样就可以在本地反复访问这些表。

示例

A. 创建一个简单公用表表达式

以下示例显示直接向 Adventure Works Cycles 的每个经理报告的雇员的数目。

	复制代码
USE AdventureWorks; GO WITH DirReps(ManagerID, DirectReports) AS ( SELECT ManagerID, COUNT(*) FROM HumanResources.Employee AS e WHERE ManagerID IS NOT NULL GROUP BY ManagerID ) SELECT ManagerID, DirectReports FROM DirReps ORDER BY ManagerID; GO

复制代码

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) 
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports 
FROM DirReps 
ORDER BY ManagerID;
GO

B. 使用公用表表达式来限制次数和报告平均数

以下示例显示向经理报告的雇员的平均数。

	复制代码
WITH DirReps (Manager, DirectReports) AS ( SELECT ManagerID, COUNT(*) AS DirectReports FROM HumanResources.Employee GROUP BY ManagerID ) SELECT AVG(DirectReports) AS [Average Number of Direct Reports] FROM DirReps WHERE DirectReports>= 2 ; GO

复制代码

WITH DirReps (Manager, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
) 
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps 
WHERE DirectReports>= 2 ;
GO

C. 多次引用同一个公用表表达式

以下示例显示 SalesOrderHeader 表中每个销售人员的销售订单的总数和最近的销售订单的日期。CTE 在运行的语句中被引用两次：一次返回为销售人员所选的列，另一次检索销售经理的类似详细信息。销售人员和销售经理的数据都返回在一行中。

	复制代码
USE AdventureWorks; GO WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate) AS ( SELECT SalesPersonID, COUNT(*), MAX(OrderDate) FROM Sales.SalesOrderHeader GROUP BY SalesPersonID ) SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate, E.ManagerID, OM.NumberOfOrders, OM.MaxDate FROM HumanResources.Employee AS E JOIN Sales_CTE AS OS ON E.EmployeeID = OS.SalesPersonID LEFT OUTER JOIN Sales_CTE AS OM ON E.ManagerID = OM.SalesPersonID ORDER BY E.EmployeeID; GO

复制代码

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

使用递归公用表表达式显示递归的多个级别。

以下示例显示经理以及向经理报告的雇员的层次列表。

	复制代码
USE AdventureWorks; GO WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel FROM HumanResources.Employee WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1 FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, EmployeeLevel FROM DirectReports ; GO

复制代码

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;
GO

E. 使用递归公用表表达式显示递归的两个级别。

以下示例显示经理以及向经理报告的雇员。将返回的级别数目被限制为两个。

	复制代码
USE AdventureWorks; GO WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel FROM HumanResources.Employee WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1 FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, EmployeeLevel FROM DirectReports WHERE EmployeeLevel <= 2 ; GO

复制代码

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 ;
GO

F. 使用递归公用表表达式显示层次列表

以下示例在示例 C 的基础上添加经理和雇员的名称，以及他们各自的头衔。通过缩进各个级别，突出显示经理和雇员的层次结构。

	复制代码
USE AdventureWorks; GO WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName), e.Title, e.EmployeeID, 1, CONVERT(varchar(255), c.FirstName + ' ' + c.LastName) FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.ManagerID IS NULL UNION ALL SELECT CONVERT(varchar(255), REPLICATE ('\| ' , EmployeeLevel) + c.FirstName + ' ' + c.LastName), e.Title, e.EmployeeID, EmployeeLevel + 1, CONVERT (varchar(255), RTRIM(Sort) + '\| ' + FirstName + ' ' + LastName) FROM HumanResources.Employee as e JOIN Person.Contact AS c ON e.ContactID = c.ContactID JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) SELECT EmployeeID, Name, Title, EmployeeLevel FROM DirectReports ORDER BY Sort; GO

复制代码

USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

G. 使用 MAXRECURSION 取消一条语句

可以使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环。以下示例特意创建了一个无限循环，然后使用 MAXRECURSION 提示将递归级别限制为两级。

	复制代码
USE AdventureWorks; GO --Creates an infinite loop WITH cte (EmployeeID, ManagerID, Title) as ( SELECT EmployeeID, ManagerID, Title FROM HumanResources.Employee WHERE ManagerID IS NOT NULL UNION ALL SELECT cte.EmployeeID, cte.ManagerID, cte.Title FROM cte JOIN HumanResources.Employee AS e ON cte.ManagerID = e.EmployeeID ) --Uses MAXRECURSION to limit the recursive levels to 2 SELECT EmployeeID, ManagerID, Title FROM cte OPTION (MAXRECURSION 2); GO

复制代码

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

在更正代码错误之后，就不再需要 MAXRECURSION。以下示例显示了更正后的代码。

	复制代码
USE AdventureWorks; GO WITH cte (EmployeeID, ManagerID, Title) AS ( SELECT EmployeeID, ManagerID, Title FROM HumanResources.Employee WHERE ManagerID IS NOT NULL UNION ALL SELECT e.EmployeeID, e.ManagerID, e.Title FROM HumanResources.Employee AS e JOIN cte ON e.ManagerID = cte.EmployeeID ) SELECT EmployeeID, ManagerID, Title FROM cte; GO

复制代码

USE AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

H. 使用公用表表达式来有选择地执行 SELECT 语句中的递归操作

以下示例显示了为 ProductAssemblyID = 800 生产自行车所需的产品装配和部件层次结构。

	复制代码
USE AdventureWorks; GO WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS ( SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty, b.EndDate, 0 AS ComponentLevel FROM Production.BillOfMaterials AS b WHERE b.ProductAssemblyID = 800 AND b.EndDate IS NULL UNION ALL SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty, bom.EndDate, ComponentLevel + 1 FROM Production.BillOfMaterials AS bom INNER JOIN Parts AS p ON bom.ProductAssemblyID = p.ComponentID AND bom.EndDate IS NULL ) SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate, ComponentLevel FROM Parts AS p INNER JOIN Production.Product AS pr ON p.ComponentID = pr.ProductID ORDER BY ComponentLevel, AssemblyID, ComponentID; GO

复制代码

USE AdventureWorks;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel 
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

I. 在 UPDATE 语句中使用递归 CTE

以下示例将直接或间接向 ManagerID 12 报告的所有雇员的 VacationHours 值增加 25%。公用表表达式将返回直接向 ManagerID 12 报告的雇员以及直接向这些雇员报告的雇员等的层次列表。只修改公用表表达式所返回的行。

	复制代码
USE AdventureWorks; GO WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel) AS (SELECT e.EmployeeID, e.VacationHours, 1 FROM HumanResources.Employee AS e WHERE e.ManagerID = 12 UNION ALL SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1 FROM HumanResources.Employee as e JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) UPDATE HumanResources.Employee SET VacationHours = VacationHours * 1.25 FROM HumanResources.Employee AS e JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID; GO

复制代码

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

使用多个定位点和递归成员

以下示例使用多个定位点和递归成员来返回指定的人的所有祖先。创建了一个表，并在表中插入值，以建立由递归 CTE 返回的宗谱。

	复制代码
-- Genealogy table IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person; GO CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int); GO INSERT Person VALUES(1, 'Sue', NULL, NULL); INSERT Person VALUES(2, 'Ed', NULL, NULL); INSERT Person VALUES(3, 'Emma', 1, 2); INSERT Person VALUES(4, 'Jack', 1, 2); INSERT Person VALUES(5, 'Jane', NULL, NULL); INSERT Person VALUES(6, 'Bonnie', 5, 4); INSERT Person VALUES(7, 'Bill', 5, 4); GO -- Create the recursive CTE to find all of Bonnie's ancestors. WITH Generation (ID) AS ( -- First anchor member returns Bonnie's mother. SELECT Mother FROM Person WHERE Name = 'Bonnie' UNION -- Second anchor member returns Bonnie's father. SELECT Father FROM Person WHERE Name = 'Bonnie' UNION ALL -- First recursive member returns male ancestors of the previous generation. SELECT Person.Father FROM Generation, Person WHERE Generation.ID=Person.ID UNION ALL -- Second recursive member returns female ancestors of the previous generation. SELECT Person.Mother FROM Generation, Person WHERE Generation.ID=Person.ID ) SELECT Person.ID, Person.Name, Person.Mother, Person.Father FROM Generation, Person WHERE Generation.ID = Person.ID; GO

复制代码

-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother 
    FROM Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO

2、FOR Clause (Transact-SQL)

FOR clause is used to specify either the BROWSE or the XML option. BROWSE and XML are unrelated options.

Topic link icon Transact-SQL Syntax Conventions

Syntax


[ FOR { BROWSE \| <XML> } ] <XML> ::= XML { { RAW [ ( 'ElementName' ) ] \| AUTO } [ <CommonDirectives> [ , { XMLDATA \| XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] } ] [ , ELEMENTS [ XSINIL \| ABSENT ] ] \| EXPLICIT [ <CommonDirectives> [ , XMLDATA ] ] \| PATH [ ( 'ElementName' ) ] [ <CommonDirectives> [ , ELEMENTS [ XSINIL \| ABSENT ] ] ] } <CommonDirectives> ::= [ , BINARY BASE64 ] [ , TYPE ] [ , ROOT [ ( 'RootName' ) ] ]

[ FOR { BROWSE | <XML> } ]
<XML> ::=
XML 
{ 
    { RAW [ ( 'ElementName' ) ] | AUTO } 
    [ 
        <CommonDirectives> 
        [ , { XMLDATA | XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] } ] 
        [ , ELEMENTS [ XSINIL | ABSENT ] 
    ]
  | EXPLICIT 
    [ 
        <CommonDirectives> 
        [ , XMLDATA ] 
    ]
  | PATH [ ( 'ElementName' ) ] 
    [
        <CommonDirectives> 
        [ , ELEMENTS [ XSINIL | ABSENT ] ]
    ]
} 

<CommonDirectives> ::= 
[ , BINARY BASE64 ]
[ , TYPE ]
[ , ROOT [ ( 'RootName' ) ] ]

Arguments

BROWSE

Specifies that updates be allowed while viewing the data in a DB-Library browse mode cursor. A table can be browsed in an application if the table includes a timestamp column, the table has a unique index, and the FOR BROWSE option is at the end of the SELECT statements sent to an instance of SQL Server.

Note:
You cannot use the <lock_hint> HOLDLOCK in a SELECT statement that includes the FOR BROWSE option.

FOR BROWSE cannot appear in SELECT statements that are joined by the UNION operator.

Note:
When the unique index key columns of a table are nullable, and the table is on the inner side of an outer join, the index is not supported by browse mode.

XML: Specifies that the results of a query are to be returned as an XML document. One of the following XML modes must be specified: RAW, AUTO, EXPLICIT. For more information about XML data and SQL Server, see Constructing XML Using FOR XML.

RAW [ ('ElementName') ]: Takes the query result and transforms each row in the result set into an XML element with a generic identifier <row /> as the element tag. You can optionally specify a name for the row element. The resulting XML output uses the specified ElementName as the row element generated for each row. For more information, see Using RAW Mode and RAW Mode Enhancements.

AUTO: Returns query results in a simple, nested XML tree. Each table in the FROM clause, for which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to the appropriate element attributes. For more information, see Using AUTO Mode and AUTO Mode Enhancements.

EXPLICIT: Specifies that the shape of the resulting XML tree is defined explicitly. Using this mode, queries must be written in a particular way so that additional information about the desired nesting is specified explicitly. For more information, see Using EXPLICIT Mode and EXPLICIT Mode Enhancements.

XMLDATA: Returns inline XDR schema, but does not add the root element to the result. If XMLDATA is specified, XDR schema is appended to the document.

XMLSCHEMA [ ('TargetNameSpaceURI') ]: Returns inline XSD schema. You can optionally specify a target namespace URI when you specify this directive, which returns the specified namespace in the schema. For more information, see Inline XSD Schema Generation.

ELEMENTS: Specifies that the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in RAW, AUTO and PATH modes only. For more information, see RAW Mode Enhancements.

XSINIL: Specifies that an element with xsi:nil attribute set to True be created for NULL column values. This option can only be specified with ELEMENTS directive. For more information, see Generating Elements for NULL Values Using the XSINIL Parameter.

ABSENT: Indicates that for null column values, corresponding XML elements will not be added in the XML result. Specify this option only with ELEMENTS.

PATH [ ('ElementName') ]: Generates a <row> element wrapper for each row in the result set. You can optionally specify an element name for the <row> element wrapper. If an empty string is provided, such as FOR XML PATH ('') ), a wrapper element is not generated. Using PATH may provide a simpler alternative to queries written using the EXPLICIT directive. For more information, see Using PATH Mode.

BINARY BASE64: Specifies that the query returns the binary data in binary base64-encoded format. When you retrieve binary data by using RAW and EXPLICIT mode, this option must be specified. This is the default in AUTO mode.

TYPE: Specifies that the query returns results as xml type. For more information, see TYPE Directive in FOR XML Queries.

ROOT [ ('RootName') ]: Specifies that a single top-level element be added to the resulting XML. You can optionally specify the root element name to generate. If the optional root name is not specified, the default <root> element is added.

Examples

The following example specifies FOR XML AUTO with the TYPE and XMLSCHEMA options. Because of the TYPE option, the result set is returned to the client as an xml type. The XMLSCHEMA option specifies that the inline XSD schema is included in the XML data returned, and the ELEMENTS option specifies that the XML result is element-centric.

	Copy Code
USE AdventureWorks; GO SELECT ContactID, FirstName, LastName, Phone FROM Person.Contact WHERE LastName LIKE 'G%' ORDER BY LastName, FirstName FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS XSINIL;

3、使用 FOR 子句

FOR 子句用来指定 BROWSE 或 XML 选项。BROWSE 和 XML 是无关的选项。

主题链接图标 Transact-SQL 语法约定

语法


[ FOR { BROWSE \| <XML> } ] <XML> ::= XML { { RAW [ ( 'ElementName' ) ] \| AUTO } [ <CommonDirectives> [ , { XMLDATA \| XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] } ] [ , ELEMENTS [ XSINIL \| ABSENT ] ] \| EXPLICIT [ <CommonDirectives> [ , XMLDATA ] ] \| PATH [ ( 'ElementName' ) ] [ <CommonDirectives> [ , ELEMENTS [ XSINIL \| ABSENT ] ] ] } <CommonDirectives> ::= [ , BINARY BASE64 ] [ , TYPE ] [ , ROOT [ ( 'RootName' ) ] ]

[ FOR { BROWSE | <XML> } ]
<XML> ::=
XML 
{ 
    { RAW [ ( 'ElementName' ) ] | AUTO } 
    [ 
        <CommonDirectives> 
        [ , { XMLDATA | XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] } ] 
        [ , ELEMENTS [ XSINIL | ABSENT ] 
    ]
  | EXPLICIT 
    [ 
        <CommonDirectives> 
        [ , XMLDATA ] 
    ]
  | PATH [ ( 'ElementName' ) ] 
    [
        <CommonDirectives> 
        [ , ELEMENTS [ XSINIL | ABSENT ] ]
    ]
} 

<CommonDirectives> ::= 
[ , BINARY BASE64 ]
[ , TYPE ]
[ , ROOT [ ( 'RootName' ) ] ]

参数

BROWSE

指定可以在查看 DB-Library 浏览模式游标中的数据时进行更新。如果表包括 timestamp 列，并且表有唯一索引，而且在发送到 SQL Server 实例的 SELECT 语句中 FOR BROWSE 选项位于末尾，则可以在应用程序中浏览该表。

注意：
不能在包括 FOR BROWSE 选项的 SELECT 语句中使用 <lock_hint> HOLDLOCK。

FOR BROWSE 不能出现在由 UNION 运算符所联接的 SELECT 语句中。

注意：
如果表的唯一索引键列可为空，并且表在外部联接的内侧，则浏览模式不支持索引。

XML: 指定以 XML 文档返回查询的结果。必须指定下列 XML 模式之一：RAW、AUTO、EXPLICIT。有关 XML 数据和 SQL Server 的详细信息，请参阅使用 FOR XML 构造 XML。

RAW [ ('ElementName') ]: 获得查询结果并将结果集内的每一行转换为以一般标识符 <row /> 作为元素标记的 XML 元素。（可选）可以为该行元素指定名称。所得到的 XML 输出将使用指定的 ElementName 作为为每一行生成的行元素。有关详细信息，请参阅使用 RAW 模式和 RAW 模式的增强功能。

AUTO: 以简单的嵌套 XML 树返回查询结果。FROM 子句中每个在 SELECT 子句中至少列出一次的表都被表示为一个 XML 元素。SELECT 子句中列出的列映射到适当的元素属性。有关详细信息，请参阅使用 AUTO 模式和 AUTO 模式的增强功能。

EXPLICIT: 指定显式定义结果 XML 树的形状。使用该模式要求必须以一种特定的方式编写查询，即显式指定与想要的嵌套有关的其他信息。有关详细信息，请参阅使用 EXPLICIT 模式和 EXPLICIT 模式的增强功能。

XMLDATA: 返回内联 XDR 架构，但不将根元素添加到结果中。如果指定了 XMLDATA，则 XDR 架构将被追加到文档末尾。

XMLSCHEMA [ ('TargetNameSpaceURI') ]: 返回内联 XSD 架构。如果指定该指令（用于返回架构中指定的命名空间），则可以选择指定目标命名空间 URI。有关详细信息，请参阅内联 XSD 架构生成。

ELEMENTS: 指定列作为子元素返回。否则，列将映射到 XML 属性。只在 RAW、AUTO 和 PATH 模式中支持该选项。有关详细信息，请参阅 RAW 模式的增强功能。

XSINIL: 指定为 NULL 列值创建其 xsi:nil 属性设置为 True 的元素。该选项只能与 ELEMENTS 指令一起指定。有关详细信息，请参阅使用 XSINIL 参数生成 NULL 值对应的元素。

ABSENT: 指示对于空列值，将不在 XML 结果中添加对应的 XML 元素。该选项只能与 ELEMENTS 一起指定。

PATH [ ('ElementName') ]: 为结果集中的每一行生成 <row> 元素包装。（可选）可以为 <row> 元素包装指定元素名称。如果提供了空字符串（例如 FOR XML PATH ('')），则不生成包装元素。使用 PATH 可能为使用 EXPLICIT 指令所编写的查询提供更简单的代替方案。有关详细信息，请参阅使用 PATH 模式。

BINARY BASE64: 指定查询返回二进制 base64 编码格式的二进制数据。使用 RAW 和 EXPLICIT 模式检索二进制数据时，必须指定该选项。这是 AUTO 模式中的默认值。

TYPE: 指定查询以 xml 类型返回结果。有关详细信息，请参阅 FOR XML 查询中的 TYPE 指令。

ROOT [ ('RootName') ]: 指定将一个顶级元素添加到结果 XML 中。（可选）可以指定要生成的根元素名称。如果不指定可选的根名称，则添加默认的 <root> 元素。

示例

以下示例指定具有 TYPE 和 XMLSCHEMA 选项的 FOR XML AUTO。由于有 TYPE 选项，因此，结果集将作为 xml 类型返回到客户端。XMLSCHEMA 选项指定在所返回的 XML 数据中包括内联 XSD 架构，而 ELEMENTS 选项指定 XML 结果是以元素为中心的。

	复制代码
USE AdventureWorks; GO SELECT ContactID, FirstName, LastName, Phone FROM Person.Contact WHERE LastName LIKE 'G%' ORDER BY LastName, FirstName FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS XSINIL;

4、嵌套 FOR XML 查询

在 SQL Server 2000 中，只能在 SELECT 查询的顶级指定 FOR XML 子句。生成的 XML 主要返回到要进行额外处理的客户端。在 SQL Server 2005 中，由于引入了 xml 数据类型和 FOR XML 查询中的 TYPE 指令，因此可以在服务器上额外处理由 FOR XML 查询返回的 XML。

您可以将 FOR XML 查询结果分配给 xml 类型变量，或使用 XQuery 查询结果，将该结果分配给 xml 类型变量以进行进一步处理。

	复制代码
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" />

复制代码

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

还可以使用 xml 数据类型方法之一，处理在变量 @x 中返回的 XML。例如，可以使用 value() 方法（xml 数据类型）检索 ProductModelID 属性值。

	复制代码
DECLARE @i int SET @i = (SELECT @x.value('/row[1]/@ProductModelID[1]', 'int')) SELECT @i

在以下示例中，FOR XML 查询结果将作为 xml 类型返回，因为在 FOR XML 子句中已指定了 TYPE 指令。

	复制代码
SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=119 or ProductModelID=122 FOR XML RAW, TYPE,ROOT('myRoot')

结果如下：

	复制代码
<myRoot> <row ProductModelID="122" Name="All-Purpose Bike Stand" /> <row ProductModelID="119" Name="Bike Wash" /> </myRoot>

由于结果为 xml 类型，因此可以对此 XML 直接指定 xml 数据类型方法之一，如以下查询所示。在此查询中，query() 方法（xml 数据类型）用于检索 <myRoot> 元素的第一个 <row> 子元素。

	复制代码
SELECT (SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=119 or ProductModelID=122 FOR XML RAW, TYPE,ROOT('myRoot')).query('/myRoot[1]/row[1]')

结果如下：

	复制代码
<row ProductModelID="122" Name="All-Purpose Bike Stand" />

此外，还可以编写嵌套 FOR XML 查询，其中内部查询的结果作为 xml 类型返回到外部查询。例如：
复制代码

SELECT Col1, Col2, ( SELECT Col3, Col4 FROM T2 WHERE T2.Col = T1.Col ... FOR XML AUTO, TYPE ) FROM T1 WHERE ... FOR XML AUTO, TYPE
注意上述查询的以下方面：
- 将由内部 FOR XML 查询生成的 XML 添加到由外部 FOR XML 生成的 XML。
- 内部查询指定 TYPE 指令。因此，由内部查询返回的 XML 数据为 xml 类型。如果未指定 TYPE 指令，则将内部 FOR XML 查询的结果作为 nvarchar(max) 返回并对 XML 数据进行实体化。
通过嵌套 FOR XML 查询，您可以更好地控制定义生成的 XML 数据的外形。
- 在 SQL Server 2000 中，默认情况下 RAW 和 AUTO 模式查询将生成以属性为中心的 XML。例如：
  复制代码
  
  SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 or ProductModelID=119 FOR XML RAW
  以下是以属性为中心的结果：
  复制代码
  
  <row ProductModelID="122" Name="All-Purpose Bike Stand" /> <row ProductModelID="119" Name="Bike Wash" />
  通过指定 ELEMENTS 指令，可以将所有 XML 作为以元素为中心的 XML 进行检索。例如：
  复制代码
  
  SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 or ProductModelID=119 FOR XML RAW, ELEMENTS
  以下是以元素为中心的结果：
  复制代码
  
  <row> <ProductModelID>122</ProductModelID> <Name>All-Purpose Bike Stand</Name> </row> <row> <ProductModelID>119</ProductModelID> <Name>Bike Wash</Name> </row>
  对于 SQL Server 2005 中的嵌套 FOR XML 查询，可以将 XML 构造为部分以属性为中心、部分以元素为中心。
- 在 SQL Server 2000 中，只能通过使用 EXPLICIT 模式编写查询来构造同级。但是，这样做可能会很麻烦。在 SQL Server 2005 中，可以通过指定嵌套 AUTO 模式 FOR XML 查询生成包括同级的 XML 层次结构。
不管使用哪种模式，嵌套 FOR XML 查询均可对说明生成的 XML 的外形进行更好的控制。可以使用这些查询替代 EXPLICIT 模式查询。

	复制代码
SELECT Col1, Col2, ( SELECT Col3, Col4 FROM T2 WHERE T2.Col = T1.Col ... FOR XML AUTO, TYPE ) FROM T1 WHERE ... FOR XML AUTO, TYPE

	复制代码
SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 or ProductModelID=119 FOR XML RAW

	复制代码
<row ProductModelID="122" Name="All-Purpose Bike Stand" /> <row ProductModelID="119" Name="Bike Wash" />

	复制代码
SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 or ProductModelID=119 FOR XML RAW, ELEMENTS

	复制代码
<row> <ProductModelID>122</ProductModelID> <Name>All-Purpose Bike Stand</Name> </row> <row> <ProductModelID>119</ProductModelID> <Name>Bike Wash</Name> </row>

示例

A. 将 FOR XML 查询与嵌套的 FOR XML 查询进行比较

以下 SELECT 查询检索 AdventureWorks 数据库中的产品类别和子类别信息。该查询中没有嵌套 FOR XML。

	复制代码
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

复制代码

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

下面是部分结果：

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

复制代码

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

如果在查询中指定 ELEMENTS 指令，则会收到以元素为中心的结果，如以下结果片段中所示：

	复制代码
<ProductCategory> <ProductCategoryID>1</ProductCategoryID> <CategoryName>Bike</CategoryName> <ProductSubCategory> <ProductSubCategoryID>1</ProductSubCategoryID> <Name>Mountain Bike</Name> </ProductSubCategory> <ProductSubCategory> ... </ProductSubCategory> </ProductCategory>

复制代码

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

然后，假设您希望生成一个 XML 层次结构，并且它是以属性为中心和以元素为中心的 XML 组合，则如以下片段中所示：

	复制代码
<ProductCategory ProductCategoryID="1" CategoryName="Bike"> <ProductSubCategory> <ProductSubCategoryID>1</ProductSubCategoryID> <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory> <ProductSubCategory> ... <ProductSubCategory> ... </ProductCategory>

复制代码

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

在先前的片段中，产品类别信息（如类别 ID 和类别名称）为属性。但是，子类别信息是以元素为中心的。若要构造 <ProductCategory> 元素，则可以编写 FOR XML 查询，如下所示：

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

结果如下：

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

复制代码

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

若要在所需的 XML 中构造嵌套 <ProductSubCategory> 元素，则可以添加嵌套 FOR XML 查询，如下所示：

	复制代码
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

复制代码

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

注意上述查询的以下方面：

内部 FOR XML 查询检索产品子类别信息。将 ELEMENTS 指令添加到内部 FOR XML，以生成以元素为中心的 XML（它将添加到由外部查询生成的 XML）。默认情况下，外部查询生成的是以属性为中心的 XML。
在内部查询中，指定 TYPE 指令以使结果为 xml 类型。如果未指定 TYPE，则结果作为 nvarchar(max) 类型返回，XML 数据作为实体返回。
外部查询也指定 TYPE 指令。因此，此查询的结果将作为 xml 类型返回到客户端。

下面是部分结果：

	复制代码
<ProductCategory ProductCategoryID="1" CategoryName="Bike"> <ProductSubCategory> <ProductSubCategoryID>1</ProductSubCategoryID> <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory> <ProductSubCategory> ... <ProductSubCategory> ... </ProductCategory>

复制代码

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

以下查询只是先前查询的扩展。它显示 AdventureWorks 数据库中完整的产品层次结构。包括：

产品类别
每种类别中的产品子类别
每种子类别的产品样式
每种样式的产品

您可能会发现以下查询对了解 AdventureWorks 数据库非常有用：

	复制代码
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

复制代码

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

下面是部分结果：

	复制代码
<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>

复制代码

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

如果从生成产品子类别的嵌套 FOR XML 查询中删除 ELEMENTS 指令，则整个结果均以属性为中心。然后便可以编写没有嵌套的查询。添加 ELEMENTS 会使 XML 部分以属性为中心、部分以元素为中心。此结果无法通过单一级别的 FOR XML 查询生成。

B. 使用嵌套 AUTO 模式查询生成同级

以下示例显示了如何使用嵌套 AUTO 模式查询生成同级。生成此类 XML 的其他方式只有这一种，即使用 EXPLICIT 模式。但是，这样做可能会很麻烦。

此查询可构造提供销售订单信息的 XML。包括：

销售订单标题信息、SalesOrderID、SalesPersonID 和 OrderDate。 AdventureWorks 将此信息存储在 SalesOrderHeader 表中。
销售订单详细信息。这包括所订购的产品、单价和订购数量。此信息存储在 SalesOrderDetail 表中。
销售人员信息。这是获得订单的销售人员。SalesPerson 表提供 SalesPersonID。对于此查询，必须将此表联接到 Employee 表以查找销售人员的姓名。

后面两个不同的 SELECT 查询生成外形略有不同的 XML。

第一个查询生成的 XML 中的 <SalesPerson> 和 <SalesOrderHeader> 显示为 <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

复制代码

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

在先前的查询中，最外面的 SELECT 语句执行下列操作：

查询在 FROM 子句中指定的行集 SalesOrder。结果是包含一个或多个 <SalesOrder> 元素的 XML。
指定 AUTO 模式和 TYPE 指令。AUTO 模式将查询结果转换为 XML，TYPE 指令将结果作为 xml 类型返回。
包括两个以逗号分隔的嵌套 SELECT 语句。第一个嵌套 SELECT 语句检索销售订单信息、标题和详细信息，第二个嵌套 SELECT 语句检索销售人员信息。
- 检索 SalesOrderID、SalesPersonID 和 CustomerID 的 SELECT 语句本身包括另一个返回销售订单详细信息的嵌套 SELECT ... FOR XML 语句（使用 AUTO 模式和 TYPE 指令）。

检索销售人员信息的 SELECT 语句查询在 FROM 子句中创建的行集 SalesPerson。若要使用 FOR XML 查询，必须提供在 FROM 子句中生成的匿名行集的名称。在本例中，提供的名称为 SalesPerson。

下面是部分结果：

	复制代码
<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> ...

复制代码

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

以下查询生成的销售订单信息基本相同，只是在结果 XML 中，<SalesPerson> 显示为 <SalesOrderDetail> 的同级。

	复制代码
<SalesOrder> <SalesOrderHeader ...> <SalesOrderDetail .../> <SalesOrderDetail .../> ... <SalesPerson .../> </SalesOrderHeader> </SalesOrder> <SalesOrder> ... </SalesOrder>

以下是查询语句：

	复制代码
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

复制代码

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

结果如下：

	复制代码
<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>

复制代码

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

由于 TYPE 指令将查询结果作为 xml 类型返回，因此，可以使用各种 xml 数据类型方法查询生成的 XML。有关详细信息，请参阅 xml Data Type Methods。在以下查询中，注意：

将先前的查询添加到 FROM 子句。查询结果返回为表。注意添加的 XmlCol 别名。

SELECT 子句对 FROM 子句中返回的 XmlCol 指定 XQuery。xml 数据类型的 query() 方法用于指定 XQuery。有关详细信息，请参阅 query() 方法（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)

复制代码

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. 创建 ASPX 应用程序以便在浏览器中检索销售订单信息

在以下示例中，Aspx 应用程序执行存储过程并将销售订单信息作为 XML 返回。结果将显示在浏览器中。存储过程中的 SELECT 语句与示例 B 中的语句类似，但生成的 XML 以元素为中心。

	复制代码
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

复制代码

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

以下是 .aspx 应用程序。它执行存储过程并在浏览器中返回 XML：

	复制代码
<%@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(); } %>

复制代码

<%@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();
}
%>

测试应用程序

在 AdventureWorks 数据库中创建存储过程。
将 .aspx 应用程序 (GetSalesOrderInfo.aspx) 保存至 c:\inetpub\www 根目录中。
执行应用程序 (http://server/GetSalesOrderInfo.aspx)。

D. 构造包括产品价格的 XML

以下示例查询 Production.Product 表以检索特定产品的 ListPrice 值和 StandardCost 值。若要使查询变得有趣，请在 <Price> 元素中返回两个价格，并使每个 <Price> 元素都有 PriceType 属性。以下是所需 XML 外形：

复制代码

<xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" elementFormDefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://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 xmlns="" PriceType="ListPrice">133.34</Price>
  <Price xmlns="" PriceType="StandardCost">98.77</Price>
</Production.Product>

以下是嵌套的 FOR XML 查询：

复制代码

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

注意上述查询的以下方面：

外部 SELECT 语句构造具有 ProductID 属性和两个 <Price> 子元素的 <Product> 元素。
两个内部 SELECT 语句构造两个 <Price> 元素，每一个都具有一个 PriceType 属性和可以返回产品价格的 XML。
外部 SELECT 语句中的 XMLSCHEMA 指令生成用于描述结果 XML 外形的内联 XSD 架构。

为使查询能够更加有趣，可以编写 FOR XML 查询，然后针对结果编写 XQuery 以重新定形 XML，如以下查询所示：

复制代码

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

先前的示例使用 xml 数据类型的 query() 方法查询由内部 FOR XML 查询返回的 XML，并构造预期的结果。

结果如下：

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