Modify XML: attributes into elements

I have an XML column that contains data with similar structure:










How can I modify the data using SQL Server to change each Value attribute into an element?




aaa


bbb


ccc





Update:

My XML looks more like this:















I would like only to move Value attribute and preserve all other attributes and elements.

You can shred the XML and rebuild it again using XQuery.

declare @X xml = '









';

select @X.query('
(: Create element Root :)
element Root
{
(: Add all attributes from Root to Root :)
/Root/@*,
(: create element Elements under Root :)
element Elements
{
(: For each Element element in /Root/Elements :)
for $e in /Root/Elements/Element
return
(: Add element Element :)
element Element
{
(: Add all attributes except Value to Element :)
$e/@*[local-name() != "Value"],

(: Check if Attribute Value exist :)
if (data($e/@Value) != "")
then
(: Create a Value element under Element :)
element Value
{
(: Add attribute Value as data to the element Element :)
data($e/@Value)
}
else () (: Empty element :)
}
},
(: Add all childelements to Root except the Elements element :)
/Root/*[local-name() != "Elements"]
}');


Result:




aaa










If Elements is not the first element under Root the query need to be modified to add all elements before Elements first and all elements after Elements after.

StackExchange Database Administrators Q#120060, answer score: 13