2

I have a xml variable like this:

<root a1="3794" a2="7">
  <price p1="8805" p2="5" p3="259.9000" />
  <price p1="8578" p2="5" p3="100.0000" />
</root>

I want to rename the root to discount, output should be:

<discount a1="3794" a2="7">
  <price p1="8805" p2="5" p3="259.9000" />
  <price p1="8578" p2="5" p3="100.0000" />
</discount>

Does anybody have a better solution for this while keeping the variable as a xml?

Thanks

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
Ash
  • 447
  • 2
  • 6
  • 19

2 Answers2

1

This is not really renaming the element. It is creating a new xml with a new root element name.

declare @X xml = 
'<root a1="3794" a2="7">
  <price p1="8805" p2="5" p3="259.9000" />
  <price p1="8578" p2="5" p3="100.0000" />
</root>';

select @X.query('element discount {root/@*, /root/*}');
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
0

The easiest would be:

select cast(replace(cast(myXmlCol as varchar(max)), 'root', 'discount') as xml)
from my_table
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69