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>

marc_s · Accepted Answer · 2011-02-25 16:54:20Z

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.

answered Feb 25, 2011 at 16:54

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Comments

Andomar · Accepted Answer · 2011-02-25 15:23:19Z

1

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.

answered Feb 25, 2011 at 15:23

Andomar

239k55 gold badges387 silver badges412 bronze badges

It's a non-grace method. Other ideas?

@chomik: XML modification support in SQL Server is entirely "non-grace". Consider doing this client site. :)