Insert XML node value as attribute with SQL Server

Question

I have a XML document with the following structure:

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

I want to modify the structure of the XML by getting rid of the Key node in every Main Node, inserting the information they contain as an atribute, so that the end result would look like this.

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

I have tried different approaches but none has worked. Any suggestions?

Answer 1

As siggemannen commented, this might be easier by consuming the XML and creating new XML from that. You can then easily move the key from a node to an attribute by aliasing it as [@Key]:

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

SELECT R.MN.value('(Key/text())[1]','int') AS [@Key],
       (SELECT ISNULL(MN.I.value('(./text())[1]','varchar(10)'),'') AS Info --As you want a blank row we need to use a string, as '' would be 0 as a int.
        FROM  R.MN.nodes('Info') MN(I)
        FOR XML PATH(''),TYPE)
FROM @XMLDocument.nodes('/Root/MainNode')R(MN)
FOR XML PATH('MainNode'),ROOT('Root');