连接行时 FOR XML PATH('') 如何工作

2021-12-10 00:00:00 tsql sql-server for-xml-path

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

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 生成中省略行元素.如果列没有名称，它会在 XML 生成中省略列元素.当 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>) ...

示例:

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('');

相关文章