Modify XML: attributes into elements I have an XML column that contains data with similar structure:

&lt;Root&gt;
    &lt;Elements&gt;
        &lt;Element Code="1" Value="aaa"&gt;&lt;/Element&gt;
        &lt;Element Code="2" Value="bbb"&gt;&lt;/Element&gt;
        &lt;Element Code="3" Value="ccc"&gt;&lt;/Element&gt;
    &lt;/Elements&gt;
&lt;/Root&gt;


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

&lt;Root&gt;
    &lt;Elements&gt;
        &lt;Element Code="1"&gt;
            &lt;Value&gt;aaa&lt;/Value&gt;
        &lt;/Element&gt;
        &lt;Element Code="2"&gt;
            &lt;Value&gt;bbb&lt;/Value&gt;
        &lt;/Element&gt;
        &lt;Element Code="3"&gt;
            &lt;Value&gt;ccc&lt;/Value&gt;
        &lt;/Element&gt;
    &lt;/Elements&gt;
&lt;/Root&gt;


Update:

My XML looks more like this:

&lt;Root attr1="val1" attr2="val2"&gt;
    &lt;Elements&gt;
        &lt;Element Code="1" Value="aaa" ExtraData="extra" /&gt;
        &lt;Element Code="2" Value="bbb" ExtraData="extra" /&gt;
        &lt;Element Code="3" Value="ccc" ExtraData="extra" /&gt;
        &lt;Element Code="4" Value="" ExtraData="extra" /&gt;
        &lt;Element Code="5" ExtraData="extra" /&gt;
    &lt;/Elements&gt;
    &lt;ExtraData&gt;
       &lt;!-- Some XML is here --&gt;
    &lt;/ExtraData&gt;
&lt;/Root&gt;


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 = '
&lt;Root attr1="val1" attr2="val2"&gt;
    &lt;Elements&gt;
        &lt;Element Code="1" Value="aaa" ExtraData="extra" /&gt;
        &lt;Element Code="2" Value="" ExtraData="extra" /&gt;
        &lt;Element Code="3" ExtraData="extra" /&gt;
    &lt;/Elements&gt;
    &lt;ExtraData&gt;
       &lt;!-- Some XML is here --&gt;
    &lt;/ExtraData&gt;
&lt;/Root&gt;';

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:

&lt;Root attr1="val1" attr2="val2"&gt;
  &lt;Elements&gt;
    &lt;Element Code="1" ExtraData="extra"&gt;
      &lt;Value&gt;aaa&lt;/Value&gt;
    &lt;/Element&gt;
    &lt;Element Code="2" ExtraData="extra" /&gt;
    &lt;Element Code="3" ExtraData="extra" /&gt;
  &lt;/Elements&gt;
  &lt;ExtraData&gt;
    &lt;!-- Some XML is here --&gt;
  &lt;/ExtraData&gt;
&lt;/Root&gt;


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.