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]

Andrei Odegov · Accepted Answer · 2017-11-29 18:13:15Z

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

Collectives™ on Stack Overflow

Replace attribute name in SQL Server XML

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related