How to easily edit SQL XML column in SQL Management Studio

Question

I have a table with an XML column. This column is storing some values I keep for configuring my application. I created it to have a more flexible schema. I can't find a way to update this column directly from the table view in SQL Management Studio. Other (INT or Varchar for example) columns are editable. I know I can write an UPDATE statement or create some code to update it. But I'm looking for something more flexible that will let power users edit the XML directly.

Any ideas?

Reiterating again: Please don't answer I can write an application. I know that, And that is exactly what I'm trying to avoid.

Answer 1

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)

Answer 2

sql server management studio is missing this feature.

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

Of course, we want to edit xml columns.