Replace attribute name in SQL Server XML

Question

I have a table MyTable with a xml column MyXmlField like this :

    <Root>
      <Node Type="type1" position="0"/>
      <Node Type="type2" position="2"/>
      <Node Type="type3" position="4"/>
      <Node Type="type4" position="2"/>
      <Node Type="type5" position="4"/>
      <Node Type="type6" position="0"/>
    </Root>

The Type attribute has a value which can be any value between those :

    type1, type2, type3, type4, type5, type6.

The position attribute has a value which can be any integer.

What I would like to do with a XQuery is to update attribute Type of every element Node as the following :

• change the name from Type to Identifier

So finally, I would like to have this :

    <Root>
      <Node Identifier="type1" position="0"/>
      <Node Identifier="type2" position="2"/>
      <Node Identifier="type3" position="4"/>
      <Node Identifier="type4" position="2"/>
      <Node Identifier="type5" position="4"/>
      <Node Identifier="type6" position="0"/>
    </Root> 

I am a beginner with XQuery, and I do not see how to do it properly other than transforming everything into varchar and doing a replace. Right now, I am only able to query each value with 6 hardcoded requests like this :

    /Root/Node[@Type=type1)]/text())[1]

Answer 1

Try the next approach, but you need to consider possible performance problems:

with
  MyTable as(
    select cast(x as xml) MyXmlField
    from(values('
<Root>
  <Node Type="type1" position="0"/>
  <Node Type="type2" position="2"/>
  <Node Type="type3" position="4"/>
  <Node Type="type4" position="2"/>
  <Node Type="type5" position="4"/>
  <Node Type="type6" position="0"/>
</Root>
'),('
<Root id="170">
  <Node Type="type1" position="0"/>
  <Node Type="type2" position="2"/>
  <Node Type="type3" position="4"/>
  <Node Type="type4" position="2"/>
  <Node Type="type5" position="4"/>
  <Node Type="type6" position="0"/>
  <foo/>
  <foo bar="42">170</foo>
</Root>
'))t(x)
  )
select MyXmlField.query('
<Root>
  {/Root/@*}
  {
    for $elem in /Root/*
      return
        if(local-name($elem) = "Node")
        then
          <Node>
          {
            for $attr in $elem/@*
              return
                if(local-name($attr) = "Type")
                then attribute Identifier {$attr}
                else $attr
          }
          {$elem/node()}
          </Node>
        else $elem
  }
</Root>
')
from MyTable