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.
Reiterating again: Please don't answer
I can write an application. I know
that, And that is exactly what I'm
trying to avoid.
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.
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)