如何在SQL Server中将XML的Key节点值转为MainNode属性？

现有如下XML结构的SQL变量：

DECLARE @XMLDocument XML = '
<Root>
    <MainNode>
        <Key>10</Key>
        <Info></Info>
    </MainNode>
    <MainNode>
        <Key>13</Key>
        <Info>15</Info>
        <Info>18</Info>
    </MainNode>
</Root>';

需要修改XML结构：移除每个MainNode下的Key节点，并将Key的内容作为MainNode的属性，最终得到如下结果：

<Root>
    <MainNode Key="10">
        <Info></Info>
    </MainNode>
    <MainNode Key="13">
        <Info>15</Info>
        <Info>18</Info>
    </MainNode>
</Root>

可以利用SQL Server的XML XQuery功能实现结构转换，具体代码如下：

DECLARE @XMLDocument XML = '
<Root>
    <MainNode>
        <Key>10</Key>
        <Info></Info>
    </MainNode>
    <MainNode>
        <Key>13</Key>
        <Info>15</Info>
        <Info>18</Info>
    </MainNode>
</Root>';

-- 使用XQuery构造新的XML结构
SET @XMLDocument = @XMLDocument.query('
    <Root>
        {
            for $mn in /Root/MainNode
            return
                <MainNode Key="{$mn/Key/text()}">
                    {$mn/*[local-name() != "Key"]}
                </MainNode>
        }
    </Root>
');

-- 输出修改后的XML
SELECT @XMLDocument AS ModifiedXML;

代码逻辑说明 #

1. 通过for $mn in /Root/MainNode遍历所有MainNode节点
2. 为每个MainNode创建新元素，将原Key节点的文本内容赋值给新元素的Key属性
3. 保留原MainNode下除Key之外的所有子节点（此处为Info节点）
4. 将所有处理后的MainNode包裹在Root节点中，完成结构转换

执行上述代码后，即可得到目标XML结构。

内容的提问来源于stack exchange，提问作者SolvencyII