How to rename the xml root element in SQL

Question

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

Answer 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/*}');