CODEWITHSUNDEEP
sql-serverxmlsql-server-2012sqlxml
Ash
Ash

Reputation: 592

How to rename the xml root element in SQL

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

Upvotes: 2

Views: 945

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37480

The easiest would be:

select cast(replace(cast(myXmlCol as varchar(max)), 'root', 'discount') as xml)
from my_table

Upvotes: 0

Related Questions