Replace XML node name in sql server 2008

Question

HI,

How can I update and xml node name using sql server 2008?

I have the below xml in one of my tables.

<Employee>

<EMPName>

a</EMPName>

<Designation>a1 </designation>

//some more details are available here

</Employee>

I need to replace it as given below:

<Employee>

<Name>

a</Name>

<Designation>a1 </designation>

//some more details are available here

</Employee>

Please help me to solve the above issue.

Regards,

Vidya

Answer 1

Check out this thread:

http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/5143d593-e750-4975-b227-885495c47f31

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

Answer 2

Yes similar thread as pointed out by Russ, slightly different implementation:

DECLARE @t TABLE ( x XML )

INSERT INTO @t VALUES ( '<Employee>
	<EMPName>a</EMPName>
	<Designation>a1</Designation>
	<!-- ... -->
	<x/>
	<y/>
</Employee>' )

INSERT INTO @t VALUES ( '<Employee>
	<EMPName>b</EMPName>
	<Designation>b1</Designation>
	<!-- ... -->
</Employee>' )

SELECT 'Before' s, DATALENGTH(x) l, x FROM @t

UPDATE t
SET x = x.query('<Employee>
  <Name>{data(Employee/EMPName)}</Name>
  
  (: Get all other elements :)
  {Employee/*[local-name() != "EMPName"]}
</Employee>')
FROM @t t

SELECT 'After' s, DATALENGTH(x) l, x FROM @t