使用SQL FOR XML生成多个同名节点

Question

我正在尝试使用 for xml 创建相同名称的子节点，但这些子节点包含来自表中不同列的数据。然而，我构建的查询没有得到预期的输出。

是否有人可以指导我正确构建这个查询?

下面是示例表和使用的 FOR XML 查询：

;WITH Temp(id, name1, name2)
AS
(
    SELECT 1, 'A', 'B' UNION
    SELECT 2, 'C', 'D' UNION
    SELECT 3, 'E', 'F'
)
SELECT
    id
    ,name1 AS [names/name]
    ,name2 AS [names/name]
FROM
    Temp
FOR XML PATH('Data'), TYPE, ROOT('Feed')

输出：

<Feed>
  <Data>
    <id>1</id>
    <names>
      <name>AB</name>
    </names>
  </Data>
  <Data>
    <id>2</id>
    <names>
      <name>CD</name>
    </names>
  </Data>
  <Data>
    <id>3</id>
    <names>
      <name>EF</name>
    </names>
  </Data>
</Feed>

预期输出：

<Feed>
  <Data>
    <id>1</id>
    <names>
      <name>A</name>
      <name>B</name>
    </names>
  </Data>
  <Data>
    <id>2</id>
    <names>
      <name>C</name>
      <name>D</name>
    </names>
  </Data>
  <Data>
    <id>3</id>
      <name>E</name>
      <name>F</name>
    </names>
  </Data>
</Feed>

- Sarang

---

如果你将name2列的别名重命名为除[names/name]之外的其他名称，比如[name2]，那么你将得到预期的输出。唯一的问题是XML标签将不再是<name></name>，而是<name2></name2>。如果需要的话，你可以在生成XML后进行查找和替换。 - Abhay Chauhan

@AbhayChauhan 这是我计划做的事情，但只作为最后的手段。 - Sarang

Answer 1

您可以在子查询中选择名称。

;WITH Temp(id, name1, name2)
AS
(
    SELECT 1, 'A', 'B' UNION
    SELECT 2, 'C', 'D' UNION
    SELECT 3, 'E', 'F'
)
SELECT
    id
    ,(SELECT name 
        FROM (
                SELECT name1 AS name 
                FROM Temp t2 
                WHERE t1.id = t2.id 
                UNION ALL 
                SELECT name2 AS name 
                FROM Temp t2 
                WHERE t1.id = t2.id) AS t 
        FOR XML PATH(''), TYPE) AS names
FROM
    Temp t1
FOR XML PATH('Data'), TYPE, ROOT('Feed')

- JamieD77

---

这个方法可以工作，但是当源表有大量数据时可能会出现性能问题。在那之前，我将使用@AbhayChauhan在问题评论中提到的解决方案。 - Sarang

如果id是主键，它不应该太慢...但这取决于你。 - JamieD77

Answer 2

我猜这应该是相当有效的（至少在SQL Server中可行）：

;WITH Temp(id, name1, name2)
AS
(
    SELECT 1, 'A', 'B' UNION
    SELECT 2, 'C', 'D' UNION
    SELECT 3, 'E', 'F'
)
SELECT
    id,
    (
        SELECT
            name1 AS name
            ,null
            ,name2 AS name
        FOR XML PATH(''), TYPE
    ) AS names
FROM
    Temp
FOR XML PATH('Data'), TYPE, ROOT('Feed')

- Janne Jämsen

Answer 3

你可以使用Cross Apply来很好地完成这个任务：

;WITH Temp(id, name1, name2)
AS
(
    SELECT 1, 'A', 'B' UNION
    SELECT 2, 'C', 'D' UNION
    SELECT 3, 'E', 'F'
)
SELECT
    id
    ,x.name AS [names/name]

FROM
    Temp
    CROSS APPLY 
        (VALUES
            (name1),
            (name2)
        ) x (name)
FOR XML PATH('Data'), TYPE, ROOT('Feed')

- DaveFoyf

Answer 4

稍微更优雅一些：

;WITH Temp(id, name1, name2)
AS
(
    SELECT 1 id, 'A', 'B' UNION
    SELECT 2 id, 'C', 'D' UNION
    SELECT 3 id, 'E', 'F'
)
SELECT
    id [Data/id],
    (select name1 name, null, name2 name 
     for xml path('names'), type) [Data]
FROM
    Temp
FOR XML PATH(''), TYPE, ROOT('Feed')

生成：

<Feed>
  <Data>
    <id>1</id>
    <names>
      <name>A</name>
      <name>B</name>
    </names>
  </Data>
  <Data>
    <id>2</id>
    <names>
      <name>C</name>
      <name>D</name>
    </names>
  </Data>
  <Data>
    <id>3</id>
    <names>
      <name>E</name>
      <name>F</name>
    </names>
  </Data>
</Feed>

微软XML默认自然折叠同名字段并删除空值，因此null变成了nothing，并允许同名字段在输出中并列。

- Zachary Scott