添加一个值为 NULL 的列，为每一列生成一个单独的项目节点.

SELECTt.col1 作为项目"，空值,t.col2 作为项目"，空值,t.col3 作为项目"从 dbo.tbl 作为 tFOR XML PATH('parent'), TYPE;

结果:

<item>1</item><item>2</item><item>3</item></父母>

SQL 小提琴

为什么会这样?

没有名称的列作为文本节点插入.在这种情况下，NULL 值作为文本节点插入 item 节点之间.

如果您添加实际值而不是 NULL，您将看到正在发生的事情.

SELECTt.col1 作为项目",'1',t.col2 作为项目",'2',t.col3 作为项目"从 dbo.tbl 作为 tFOR XML PATH('parent'), TYPE;

结果:

<item>1</item>1<item>2</item>2<item>3</item></parent>

另一种指定没有名称的列的方法是使用通配符 * 作为列别名.

名称指定为通配符的列>

在这种情况下没有必要使用通配符，因为具有 NULL 值的列没有列名，但是当您想要来自实际列的值但不希望列名是一个列名时，它很有用节点名称.

I'd like to generate the following output using SQL Server 2012:

<parent>
  <item>1</item>
  <item>2</item>
  <item>3</item>
</parent>

From three different columns in the same table (we'll call them col1, col2, and col3).

I'm trying to use this query:

SELECT 
  t.col1 as 'item'
 ,t.col2 as 'item'
 ,t.col3 as 'item' 
FROM tbl t 
FOR XML PATH('parent'), TYPE

But what I get is this:

<parent>
  <item>123</item>
</parent>

What am I doing wrong here?

Add a column with NULL as value to generate a separate item node for each column.

SELECT 
  t.col1 as 'item'
 ,NULL
 ,t.col2 as 'item'
 ,NULL
 ,t.col3 as 'item' 
FROM dbo.tbl as t 
FOR XML PATH('parent'), TYPE;

Result:

<parent>
  <item>1</item>
  <item>2</item>
  <item>3</item>
</parent>

SQL Fiddle

Why does this work?

Columns without a name are inserted as text nodes. In this case the NULL value is inserted as a text node between the item nodes.

If you add actual values instead of NULL you will see what is happening.

SELECT 
  t.col1 as 'item'
 ,'1'
 ,t.col2 as 'item'
 ,'2'
 ,t.col3 as 'item' 
FROM dbo.tbl as t 
FOR XML PATH('parent'), TYPE;

Result:

<parent>
  <item>1</item>1<item>2</item>2<item>3</item></parent>

Another way to specify a column without a name is to use the wildcard character * as a column alias.

Columns with a Name Specified as a Wildcard Character

It is not necessary to use the wildcard in this case because the columns with NULL values don't have a column name but it is useful when you want values from actual columns but you don't want the column name to be a node name.