SQL Server的FOR XML路径化妆重复的节点SQL Server的FOR XML路径化妆重

我想使用SQL Server 2012中生成以下的输出：

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

从同一个表三种不同的列（我们会打电话给他们COL1，COL2和COL3）。

我试图用这个查询：

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

但是我所得到的是这样的：

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

我在做什么错在这里？

Answer 1:

用NULL添加列值以生成用于每个列的一个单独项目的节点。

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;

结果：

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

SQL小提琴

为什么这项工作？

没有名字列被插入的文本节点。 在这种情况下，空值被插入作为之间的文本节点item节点。

如果添加的实际值，而不是空的，你会看到发生了什么。

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;

结果：

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

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

指定为通配符，名称列

这是没有必要使用通配符在这种情况下，因为与NULL值的列没有列名，但是当你想从实际的列值，但你不希望列名是一个节点名是有用的。

Answer 2:

好吧，你不能使用的路径。 使用明确的，相反，

SELECT 1 AS tag,NULL AS parent, t.col1 AS [Parent!1!Item!element],
               t.col2 AS [Parent!1!Item!element],
               t.col3 AS [Parent!1!Item!element]
FROM tbl t
FOR XML EXPLICIT

Answer 3:

实际上有与XML语法的路径来解决这几个方面。

首先是第一UNPIVOT你的结果，例如：

SELECT item as [text()]
FROM 
   (select col1, col2, col3 from tbl) p
UNPIVOT
   (item FOR colHeading IN (col1, col2, col3)) AS unpvt
FOR XML PATH ('item'), ROOT ('parent')

第二届不需要UNPIVOT，但更多的重复查询的：

select (select col1 as [text()] from tbl for xml path('item'), type)
    ,  (select col2 as [text()] from tbl for xml path('item'), type)
    ,  (select col3 as [text()] from tbl for xml path('item'), type)
for xml path ('parent')

这两项将结合多行数据全部相同的父节点下。 例如，如果你有2行，先用1,2,3和4,5,6第二，你会得到：

<parent>
    <item>1</item>
    <item>2</item>
    <item>3</item>
    <item>4</item>
    <item>5</item>
    <item>6</item>
</parent>

相反，如果你想每一行，你UNPIVOT有每行的唯一父元素，那么，假设你在每一行的一些行标识符通过调整这些方法通过该行（我称之为parentId的），您可以将这些：

SELECT
  (
      SELECT item as [text()]
      FROM 
        (select parentId, col1, col2, col3 from tbl tt where tt.parentid =   t.parentid) p
      UNPIVOT
        (item FOR colHeading IN (col1, col2, col3)) AS unpvt
      FOR XML PATH ('item'), TYPE
  )
FROM tbl t
FOR XML PATH ('parent')

要么

select (select col1 as [text()] from tbl tt where t.parentid = tt.parentid for xml path('item'), type)
    ,  (select col2 as [text()] from tbl tt where t.parentid = tt.parentid for xml path('item'), type)
    ,  (select col3 as [text()] from tbl tt where t.parentid = tt.parentid for xml path('item'), type)
from tbl t
for xml path ('parent')

这将导致：

<parent>
    <item>1</item>
    <item>2</item>
    <item>3</item>
</parent>
<parent>
    <item>4</item>
    <item>5</item>
    <item>6</item>
</parent>

SQL拨弄演示

Answer 4:

一对夫妇在这里指出：如果您使用FOR XML EXPLICIT，您不能使用XMLNAMESPACES（我没有提到的要求，所以我还是留下接受的答案）。 虽然Iheria的回答也非常有帮助，还有另一种更简单的可能性，因为意识到我有：

SELECT CONVERT(XML, '<item>' + t.col1 
           + '</item><item>' + t.col2 
           + '</item><item>' + t.col3 + '</item>')
FROM tbl t
FOR XML PATH('parent'), TYPE

我想这大概是最简单，最高效的方式（我没有基准，但我无法想象使用UNPIVOT会更快，如果有的话，多SELECT的选项可能被发动机无论如何重构这个）。

Answer 5:

我认为，如果你改变这样的列它应该工作的别名。 这是因为别名是相同的，并且可以是数据的数据类型是相同为好。 如果您有COL1，COL2和COL3不同的数据的情况下，不应该出这种行为。

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

文章来源: SQL Server FOR XML Path make repeating nodes