连接行时FOR XML PATH('')如何工作 [英] How FOR XML PATH('') works when concatenating rows

问题描述

FOR XML PATH ('')子句在连接SQL Server中的行时如何工作?

How does the FOR XML PATH ('') clause do its work when concatenating rows in SQL Server?

我只想解释FOR XML PATH ('')子句的工作原理...

I just want an explanation of how the FOR XML PATH ('') clause works...

推荐答案

FOR XML PATH('xxx')的作用是为结果集创建一个XML字符串，该字符串将每一行放入<xxx></xxx>元素中，并将每一列值放入该行中.具有该列名称的元素.

What FOR XML PATH('xxx') does is create an XML string for the resultset that puts each row in a <xxx></xxx> element and each column value inside the row, in an element with the name for that column.

如果PATH为空(即PATH(''))，它将忽略XML生成中的row元素.如果该列没有名称，则它会省略XML生成中的column元素.如果两个PATH均为空并且列没有名称，则它实际上成为所有行的字符串连接.

If the PATH is empty (i.e. PATH('')) it omits the row element in the XML generation. If the column has no name it omits the column element in the XML generation. When both PATH is empty and columns have no names it effectively becomes a string concatenation of all rows.

运行以下语句以更好地了解流程:

Run the following statements to get a better insight in the process:

-- Each row is in a <beta></beta> element
-- Each column in that row in a <alfa></alfa> element (the column name)
SELECT
    alfa=','+TABLE_SCHEMA + '.' + TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('beta');

-- Since the PATH is empty, the rows are not put inside an element
-- Each column in that row is in a <alfa></alfa> element (the column name)
SELECT
    alfa=','+TABLE_SCHEMA + '.' + TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

-- Since the PATH is empty, the rows are not put inside an element
-- Since the column has no name it is not put inside an element     
SELECT
    ','+TABLE_SCHEMA + '.' + TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

-- This uses the STUFF function to remove the leading comma to get a proper comma-seperated list    
SELECT STUFF((
    SELECT
        ','+TABLE_SCHEMA + '.' + TABLE_NAME
    FROM
        INFORMATION_SCHEMA.TABLES
    FOR
        XML PATH('')
    ),1,1,''
) AS comma_seperated_list;

---

现在，我听到您问:仅从表中选择列时，如何删除列名.按照我的喜好，有几种方法:

---

Now I hear you asking: How can I remove the column name when I simply select a column from a table. There are several ways, in order of my preference:

• XQuery属性:SELECT [text()]=column_name ...
• 使用子查询选择列值:SELECT (SELECT column_name) ...
• 将该列投射到其类型:SELECT CAST(column_value AS <TYPE of the column>) ...

• XQuery properties: SELECT [text()]=column_name ...
• Use a subquery to select the column value: SELECT (SELECT column_name) ...
• CAST the column to its type: SELECT CAST(column_value AS <TYPE of the column>) ...

示例:

SELECT
    [text()]=TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

SELECT
    (SELECT TABLE_NAME)
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

SELECT
    CAST(TABLE_NAME AS SYSNAME)
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

这篇关于连接行时FOR XML PATH('')如何工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！