Modify xml element name in SQL Server

Question

I want to change element name with following statement:

SET @myDoc.modify('replace value of (/CustomerInfo)[1] with "Customer"')   

from
<CustomerInfo>
    <ID>1</ID>
</CustomerInfo>
to
<Customer>
    <ID>1</ID>
</Customer>
But failed. So how can i change it just in sql ?

Answer 1

I have a non-grace method:
Convert xml to varchar, then replace it.

Answer 2

I think you need to select the values with .query or .nodes into a resultset that will render into the XML you want using FOR XML

Answer 3

I tried following sql:
select @myDoc=(
select @myDoc.query('/CustomerInfo/node()')
for xml raw('Customer'),type)
I guess it solve your question.