how to update element name in a xml column in sql server

Question

I have an xml column in my table like following:

   <info>
      <teacher>     
            <name>John</name>   
      </teacher>
      <StInfo>
        <name>William</name>    
        <address>India</address>    
      </StInfo>
    </info>

I have to just update to

 <info>
          <teacher>     
                <name>John</name>   
          </teacher>
          <Student>
            <name>William</name>    
            <address>India</address>    
          </Student>
        </info>

Roberto Conte Rosito · Accepted Answer · 2013-11-13 17:09:13Z

4

I've built an example for you in SQL Server using your data:

DECLARE @StackExample TABLE (
     Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT(NEWID())
    ,XmlColumn XML
)

INSERT INTO @StackExample(XmlColumn) VALUES('<info>
  <StInfo>
    <name>William</name>    
    <address>India</address>    
  </StInfo>
</info>')


UPDATE T
SET XmlColumn = XmlColumn.query('<info>
  <Student>
  {info/StInfo/*}
  </Student>
</info>')
FROM @StackExample t

SELECT * FROM @StackExample

I hope this can help

answered Nov 13, 2013 at 17:09

Roberto Conte Rosito

2,11813 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

donstack Over a year ago

it's working but sorry i have not given complete structure of xml as there are some other elemts too at <stinfo> level which get removed after execution, i have updated xml in question

Roberto Conte Rosito Over a year ago

You simply add {info/teacher} before <Student> tag in the update statement and it's done . Sorry but I can't test it, but i want to sent you a fast response to give you the change to try.

roman · Accepted Answer · 2013-11-13 17:26:56Z

0

As far as I know, there's no way to modify xml element names with SQL DML. You can replace it after converting to varchar:

update Table1 set
    data = cast(
      replace(
        replace(
          cast(data as nvarchar(max)),
        '</StInfo>', '</Student>'
        ),
      '<StInfo>', '<Student>'
      )
    as xml)

Or you can reconstruct your xml using XQuery:

update Table1 set
    data = data.query('<info>{
                for $i in info/StInfo
                return element Student {$i/*}
                }</info>'
    )

These appoaches will work even for multiple elements.

sql fiddle demo

edited Nov 13, 2013 at 17:26

answered Nov 13, 2013 at 17:13

roman

118k30 gold badges205 silver badges209 bronze badges

Collectives™ on Stack Overflow

how to update element name in a xml column in sql server

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related