Replace an attribute name in an Xml column with a SQL query

Question

I need to replace the attribute's name "TimeStamp" by "Timestamp" in the following XML example :

<CMD CommandID="6000">
   <DATA>
      <ReportData Key="10000">
         <Event TimeStamp="2013-03-07 15:42:49.000" Name="ABC" />
         <Event TimeStamp="2013-03-07 15:42:50.000" Name="DEF" />
         <Event TimeStamp="2013-03-07 16:22:03.000" Name="GHI" />
      <ReportData>
   <DATA>
<CMD>

It is stored in a SQL Server 2008 database within a column of XML type.

I tried something like this but it's incorrect:

DECLARE @variable NVARCHAR(20) = 'Timestamp'

UPDATE MyDatabase.dbo.MyTable
SET MyXmlColumn.modify('replace value of (/CMD/DATA/ReportData/Event/@TimeStamp) with sql:variable("@variable") ')

Thank you for your help.

You can't do that; you can update the values of elements and attributes, but you cannot change the attribute name in a XML — marc_s
– marc_s, Commented Mar 15, 2013 at 12:28

Mikael Eriksson · Accepted Answer · 2013-03-15 12:48:36Z

5

You can use replace.

update MyDatabase.dbo.MyTable
set MyXMLColumn = replace(cast(MyXMLColumn as nvarchar(max)), '<Event TimeStamp="', '<Event Timestamp="')

answered Mar 15, 2013 at 12:48

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

1 Comment

dahui Over a year ago

How hasn't this had a +1 over 3 years!? Nice solution

Collectives™ on Stack Overflow

Replace an attribute name in an Xml column with a SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related