Modify xml element name in SQL Server

Question

How to change element name from Cust to Customer?

<Cust id="1">
  <Name>aaaaaaaaaa</Name>
  <Desc>bbbbbbbbbb</Desc>
</Cust>

When I'm using following statement

select @myXml.query('/node()[1]/node()') for xml raw('Customer')

sql removes attributes

<Customer>
  <Name>aaaaaaaaaa</Name>
  <Desc>bbbbbbbbbb</Desc>
</Customer>

Answer 1

Try this:

SELECT
    @myXml.value('(/Cust/@id)[1]', 'int') AS '@id',
    @myXml.query('/node()[1]/node()') 
FOR XML PATH('Customer')

Gives me an output of:

<Customer id="1">
  <Name>aaaaaaaaaa</Name>
  <Desc>bbbbbbbbbb</Desc>
</Customer>

With the FOR XML PATH, you can fairly easily "restore" that attribute that gets lost in the conversion.

Answer 2

You could use replace:

replace(replace(@YourXml, '<Cust id', '<Customer id)', '</Cust>', '</Customer>')

This is fairly safe, as < is not valid as data in XML, it would appear as < or an ASCII or UNICODE sequence.