SQL Server XML – How to Modify XML Attributes into Elements

sql serverxmlxquery

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

<Root>
    <Elements>
        <Element Code="1" Value="aaa"></Element>
        <Element Code="2" Value="bbb"></Element>
        <Element Code="3" Value="ccc"></Element>
    </Elements>
</Root>

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

<Root>
    <Elements>
        <Element Code="1">
            <Value>aaa</Value>
        </Element>
        <Element Code="2">
            <Value>bbb</Value>
        </Element>
        <Element Code="3">
            <Value>ccc</Value>
        </Element>
    </Elements>
</Root>

Update:

My XML looks more like this:

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

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

Best Answer

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

declare @X xml = '
<Root attr1="val1" attr2="val2">
    <Elements>
        <Element Code="1" Value="aaa" ExtraData="extra" />
        <Element Code="2" Value="" ExtraData="extra" />
        <Element Code="3" ExtraData="extra" />
    </Elements>
    <ExtraData>
       <!-- Some XML is here -->
    </ExtraData>
</Root>';

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:

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

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.

Related Question