How to easily edit SQL XML column in SQL Management Studio

This is an old question, but I needed to do this today. The best I can come up with is to write a query that generates SQL code that can be edited in the query editor - it's sort of lame but it saves you copy/pasting stuff.

Note: you may need to go into Tools > Options > Query Results > Results to Text and set the maximum number of characters displayed to a large enough number to fit your XML fields.

e.g.

select 'update [table name] set [xml field name] = ''' + convert(varchar(max), [xml field name]) +''' where [primary key name] = ' + convert(varchar(max), [primary key name]) from [table name]

which produces a lot of queries that look like this (with some sample table/field names):

update thetable set thedata = '<root><name>Bob</name></root>' where thekey = 1

You then copy these queries from the results window back up to the query window, edit the xml strings, and then run the queries.

(Edit: changed 10 to max to avoid error)

sql server management studio is missing this feature.

I can see Homer Simpson as the Microsoft project managerbanging his head with the palm of his hand:"Duh!"

Of course, we want to edit xml columns.

I wound up writing a .net c# UI to deal with the xml data. Using xsl for display and an xml schema helped display the xml nicely and maintain it's integrity.

edit: Also c# contains the xmldocument class that simplifies reading/writing the data.