Changing the name of an XML element in SQL Server

Question

How to change item name from Cust to client?

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

When I use the following instruction

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

sql removes attributes

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

Answer 1

Try the following:

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

Gives me the conclusion:

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

With the help of FOR XML PATHyou can quite easily "restore" this attribute, which will be lost during the conversion.

Answer 2

You can use replace:

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

This is pretty safe because it is <invalid as data in XML, it will look like <either an ASCII or UNICODE sequence.