How to easily edit SQL XML column in SQL Management Studio

harlev_1363 0
Viewed 43013

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.

xml sql-server sql
10 Answers

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.

Paul_54330

I do not think you can use the Management Studio GUI to update XML-columns without writing the UPDATE-command yourself.

One way you could let users update xml-data is to write a simple .net based program (winforms or asp.net) and then select/update the data from there. This way you can also sanitize the data and easily validate it against any given schema before inserting/updating the information.

user_22570

I'm a bit fuzzy in this are but could you not use the OPENXML method to shred the XML into relational format then save it back in XML once the user has finished?

Like others have said I think it might be easier to write a small app to do it!

Almond_16030

I know this is a really old question but I hope this might help someone.

If you do not wish to write an update statement or an application as the question suggests, then I believe the following will help given that you are a power user.

Alter the XML column to varchar and you will be able to modify this column in the SSMS edit table screen. I could not alter the column using the SSMS table designer. The Following script worked.

ALTER TABLE [tablename]
ALTER COLUMN [columnname] varchar(max);

Once you are done with edits, alter the column back to XML.

ALTER TABLE [tablename]
ALTER COLUMN [columnname] XML;
user_11495800
Related