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');

Answer 2

It is better to use SQL Server native XQuery functionality via FLWOR expression.

It allows to compose a desired output XML "visually".

Check it out below.

SQL

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

SELECT @XMLDocument.query('<Root>
    {
        for $x in /Root/MainNode
        return <MainNode Key="{$x/Key}">
            {$x/*[not(local-name()="Key")]}
            </MainNode>
    }
</Root>');

Output

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

Answer 3

As mentioned, it's easier to just rebuild the XML.

A slightly shorter syntax than the other answer, and the benefit is that you don't need to rebuild everything.

Just select out everything else apart from <Key> using .query and add it as an unnamed column

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],
  R.MN.query('*[local-name() != "key"]')
FROM @XMLDocument.nodes('/Root/MainNode')R(MN)
FOR XML PATH('MainNode'), ROOT('Root'), TYPE;

db<>fiddle