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?
3条答案
按热度按时间abithluo1#
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 columndb<>fiddle
cidc1ykv2#
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
Output
dojqjjoe3#
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]
: