CODEWITHSUNDEEP
sqlxmlsql-server-2005
chomik
chomik

Reputation: 33

Modify xml element name in SQL Server

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>

Upvotes: 3

Views: 2901

Answers (2)

marc_s
marc_s

Reputation: 755321

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.

Upvotes: 1

Andomar
Andomar

Reputation: 238276

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 &lt; or an ASCII or UNICODE sequence.

Upvotes: 1

Related Questions