8 deleted 368 characters in body
source | link
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 nodeelement in /Root/Elements :)
                       for $e in /Root/Elements/Element
                       return 
                              (: Add nodeelement Element :)
                              element Element 
                                {
                                  (: Add all attributes except Value to Element :)
                                  $e/@*[local-name() != "Value"], 

                                  (: Check if Attribute Value has a valueexist :)
                                  if (data($e/@Value) != "")
                                  then
 
                                    (: Create a Value nodeelement under Element :)
                                    element Value 
                                    {
                                      (: Add the value in attribute Value as data to 
                                         the element Element :)
                                      data($e/@Value)
                                    }
                                  else () (: Empty element :)
                                } 
                       },
 
      (: Add all childnodeschildelements to Root except the Elements nodeelement :)
      /Root/*[local-name() != "Elements"]
    }');
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 node in /Root/Elements :)
                       for $e in /Root/Elements/Element
                       return 
                              (: Add node Element :)
                              element Element 
                                {
                                  (: Add all attributes except Value to Element :)
                                  $e/@*[local-name() != "Value"], 

                                  (: Check if Attribute Value has a value :)
                                  if (data($e/@Value) != "")
                                  then
 
                                    (: Create a Value node under Element :)
                                    element Value 
                                    {
                                      (: Add the value in attribute Value as data to 
                                         the element Element :)
                                      data($e/@Value)
                                    }
                                  else () (: Empty element :)
                                } 
                       },
 
      (: Add all childnodes to Root except the Elements node :)
      /Root/*[local-name() != "Elements"]
    }');
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"]
    }');
7 deleted 1034 characters in body
source | link

Best way is toYou can shred itthe XML and putrebuild it back together again using XQuery.

declare @X xml = '
<Root>
    <Elements>
        <Element Code="1" Value="aaa"></Element>
        <Element Code="2" Value="bbb"></Element>
        <Element Code="3" Value="ccc"></Element>
    </Elements>
</Root>'

select (
       select T.X.value('@Code', 'int') as '@Code',
              T.X.value('@Value', 'char(3)') as Value
       from @X.nodes('/Root/Elements/Element') as T(X)
       for xml path('Element'), root('Elements'), type
       )
for xml path('Root')
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 node in /Root/Elements :)
                       for $e in /Root/Elements/Element
                       return 
                              (: Add node Element :)
                              element Element 
                                {
                                  (: Add all attributes except Value to Element :)
                                  $e/@*[local-name() != "Value"], 

                                  (: Check if Attribute Value has a value :)
                                  if (data($e/@Value) != "")
                                  then

                                    (: Create a Value node under Element :)
                                    element Value 
                                    {
                                      (: Add the value in attribute Value as data to 
                                         the element Element :)
                                      data($e/@Value)
                                    }
                                  else () (: Empty element :)
                                } 
                       },

      (: Add all childnodes to Root except the Elements node :)
      /Root/*[local-name() != "Elements"]
    }');

UpdateResult:

More complex XML only means more complex query. The easiest way is still to shred and rebuild.

declare @X xml = '
<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" />
    </Elements>
    <ExtraData>
       <!-- Some XML is here -->
    </ExtraData>
</Root>';

select Root.X.value('@attr1', 'char(4)') as '@attr1',
       Root.X.value('@attr2', 'char(4)') as '@attr2',
       (
       select Element.X.value('@Code', 'int') as '@Code',
              Element.X.value('@ExtraData', 'char(5)') as '@ExtraData',
              Element.X.value('@Value', 'char(3)') as Value
       from Root.X.nodes('Elements/Element') as Element(X)
       for xml path('Element'), root('Elements'), type
       ),
       Root.X.query('ExtraData')
from @X.nodes('/Root') as Root(X)
for xml path('Root');

And one extra shred and rebuild just for fun, a bit more generic, not for the faint of heart.

select @X.query('
  element Root 
  {/Root/@*, 
    element Elements {
                     for $e in /Root/Elements/Element
                     return element Element {$e/@*[local-name() != "Value"], 
                            element Value {data($e/@Value)}} 
                     },
    /Root/*[local-name() != "Elements"]
  }')

And a version with comments to explain what each part does.

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 node in /Root/Elements :)
                       for $e in /Root/Elements/Element
                       return 
                              (: Add node Element :)
                              element Element 
                                {
                                  (: Add all attributes except Value to Element :)
                                  $e/@*[local-name() != "Value"], 

                                  (: Create a Value node under Element :)
                                  element Value 
                                  {
                                    (: Add the value in attribute Value as data to the element Element :)
                                    data($e/@Value)
                                  }
                                } 
                       },

      (: Add all childnodes to Root except the Elements node :)
      /Root/*[local-name() != "Elements"]
    }')
<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 a bit to add all elements before Elements first and all elements after Elements after.

Best way is to shred it and put it back together again.

declare @X xml = '
<Root>
    <Elements>
        <Element Code="1" Value="aaa"></Element>
        <Element Code="2" Value="bbb"></Element>
        <Element Code="3" Value="ccc"></Element>
    </Elements>
</Root>'

select (
       select T.X.value('@Code', 'int') as '@Code',
              T.X.value('@Value', 'char(3)') as Value
       from @X.nodes('/Root/Elements/Element') as T(X)
       for xml path('Element'), root('Elements'), type
       )
for xml path('Root')

Update:

More complex XML only means more complex query. The easiest way is still to shred and rebuild.

declare @X xml = '
<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" />
    </Elements>
    <ExtraData>
       <!-- Some XML is here -->
    </ExtraData>
</Root>';

select Root.X.value('@attr1', 'char(4)') as '@attr1',
       Root.X.value('@attr2', 'char(4)') as '@attr2',
       (
       select Element.X.value('@Code', 'int') as '@Code',
              Element.X.value('@ExtraData', 'char(5)') as '@ExtraData',
              Element.X.value('@Value', 'char(3)') as Value
       from Root.X.nodes('Elements/Element') as Element(X)
       for xml path('Element'), root('Elements'), type
       ),
       Root.X.query('ExtraData')
from @X.nodes('/Root') as Root(X)
for xml path('Root');

And one extra shred and rebuild just for fun, a bit more generic, not for the faint of heart.

select @X.query('
  element Root 
  {/Root/@*, 
    element Elements {
                     for $e in /Root/Elements/Element
                     return element Element {$e/@*[local-name() != "Value"], 
                            element Value {data($e/@Value)}} 
                     },
    /Root/*[local-name() != "Elements"]
  }')

And a version with comments to explain what each part does.

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 node in /Root/Elements :)
                       for $e in /Root/Elements/Element
                       return 
                              (: Add node Element :)
                              element Element 
                                {
                                  (: Add all attributes except Value to Element :)
                                  $e/@*[local-name() != "Value"], 

                                  (: Create a Value node under Element :)
                                  element Value 
                                  {
                                    (: Add the value in attribute Value as data to the element Element :)
                                    data($e/@Value)
                                  }
                                } 
                       },

      (: Add all childnodes to Root except the Elements node :)
      /Root/*[local-name() != "Elements"]
    }')

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

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 node in /Root/Elements :)
                       for $e in /Root/Elements/Element
                       return 
                              (: Add node Element :)
                              element Element 
                                {
                                  (: Add all attributes except Value to Element :)
                                  $e/@*[local-name() != "Value"], 

                                  (: Check if Attribute Value has a value :)
                                  if (data($e/@Value) != "")
                                  then

                                    (: Create a Value node under Element :)
                                    element Value 
                                    {
                                      (: Add the value in attribute Value as data to 
                                         the element Element :)
                                      data($e/@Value)
                                    }
                                  else () (: Empty element :)
                                } 
                       },

      (: Add all childnodes to Root except the Elements node :)
      /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.

6 added 1 character in body
source | link
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 node in /Root/Elements :)
                       for $e in /Root/Elements/Element
                       return 
                              (: Add node Element :)
                              element Element 
                                {
                                  (: Add all attributes except Value to Element :)
                                  $e/@*[local-name() != "Value"], 

                                  (: Create a Value node under Element :)
                                  element Value 
                                  {
                                    (: Add the value in attribute Value as data to the element Element :)
                                    data($e/@Value)
                                  }
                                } 
                       },

      (: Add all childnodes to Root except the ElemetsElements node :)
      /Root/*[local-name() != "Elements"]
    }')
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 node in /Root/Elements :)
                       for $e in /Root/Elements/Element
                       return 
                              (: Add node Element :)
                              element Element 
                                {
                                  (: Add all attributes except Value to Element :)
                                  $e/@*[local-name() != "Value"], 

                                  (: Create a Value node under Element :)
                                  element Value 
                                  {
                                    (: Add the value in attribute Value as data to the element Element :)
                                    data($e/@Value)
                                  }
                                } 
                       },

      (: Add all childnodes to Root except the Elemets node :)
      /Root/*[local-name() != "Elements"]
    }')
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 node in /Root/Elements :)
                       for $e in /Root/Elements/Element
                       return 
                              (: Add node Element :)
                              element Element 
                                {
                                  (: Add all attributes except Value to Element :)
                                  $e/@*[local-name() != "Value"], 

                                  (: Create a Value node under Element :)
                                  element Value 
                                  {
                                    (: Add the value in attribute Value as data to the element Element :)
                                    data($e/@Value)
                                  }
                                } 
                       },

      (: Add all childnodes to Root except the Elements node :)
      /Root/*[local-name() != "Elements"]
    }')
5 added 1587 characters in body
source | link
4 deleted 30 characters in body
source | link
3 added 463 characters in body
source | link
2 added 1100 characters in body
source | link
1
source | link