sql-server - 在 SQL 中合并和修改 XML

Question

使用 SQL Server 2005，是否可以合并 XML 并同时添加属性？

不幸的是，由于项目限制，我需要一个 SQL Server 2005 解决方案。

考虑以下情况，我需要在一个新的 <root> 中组合多行的 XML元素...

; WITH [TestTable] AS (
    SELECT 7 AS [PkId], CAST('<data><id>11</id><id>12</id></data>' AS XML) AS [Data]
    UNION ALL
    SELECT 12, CAST('<data><id>22</id></data>' AS XML)
    UNION ALL
    SELECT 43, CAST('<data><id>33</id></data>' AS XML)
)
SELECT (
    SELECT XMLDATA as [*]
    FROM (
        SELECT [Data] AS [*]
        FROM [TestTable]
        FOR XML PATH(''), TYPE
    ) AS DATA(XMLDATA)
    FOR XML PATH('root')
)

这会产生所需的输出...

<root>
  <data><id>11</id><id>12</id></data>
  <data><id>22</id></data>
  <data><id>33</id></data>
</root>

但我需要做的是，如果可能的话，向带有 PkId 的每一行中的现有 数据元素添加一个属性。值(value)。所需的输出将如下所示...

<root>
  <data pkid="7"><id>11</id><id>12</id></data>
  <data pkid="12"><id>22</id></data>
  <data pkid="43"><id>33</id></data>
</root>

我的直觉是，如果不使用光标，这是不可能的，但如果有人知道这样做的方法，我很乐意听到。

---

应@MattA 的要求，这里是表中一些随机数据的示例...

[PkId]  [UserId]  [SubmittedDate]      [Data]
1       1         2015-03-24 12:34:56  '<data><id>1</id><id>2</id></data>'
2       1         2015-03-23 09:15:52  '<data><id>3</id></data>'
3       2         2015-03-22 16:01:23  '<data><id>4</id><id>5</id></data>'
4       1         2015-03-21 13:45:34  '<data><id>6</id></data>'

请注意，为了让问题更简单，我声明我需要 PkId列作为数据的属性。实际情况并非如此——相反，我需要 [SubmittedDate]要使用的列。如果造成混淆，我深表歉意。

使用 UserId=1作为过滤器，我希望从上面得到的 XML 是...

<root>
  <data submitteddate="2015-03-24T12:34:56"><id>1</id><id>2</id></data>
  <data submitteddate="2015-03-23T09:15:52"><id>3</id></data>
  <data submitteddate="2015-03-21T13:45:34"><id>6</id></data>
</root>

日期将使用 CONVERT 提供的 126 日期格式进行格式化。

Answer 1

这是为您快速解答的问题。 XML 确实支持“修改”，但在像这样的小数据集上分解也很有效。

代码

--The existing XML
DECLARE @XML XML = '<root>
  <data><id>11</id></data>
  <data><id>22</id></data>
  <data><id>33</id></data>
</root>'

--XML Shredded Back to a table
;WITH
ShreddedXML AS (
    SELECT
        ID = FieldAlias.value('(id)[1]','int')
    FROM
        @XML.nodes('/root/data') AS TableAlias(FieldAlias)
), ArbitraryPKGenerator AS (
    SELECT  CURRENT_TIMESTAMP AS PKid,
            ID
    FROM    ShreddedXML
)
SELECT  A.PKId AS "@PKid",
        A.ID AS "id"
FROM    ArbitraryPKGenerator AS A
FOR     XML PATH('data'), ROOT('root')

和 XML

<root>
  <data PKid="2015-03-24T09:44:55.770">
    <id>11</id>
  </data>
  <data PKid="2015-03-24T09:44:55.770">
    <id>22</id>
  </data>
  <data PKid="2015-03-24T09:44:55.770">
    <id>33</id>
  </data>
</root>