How to easily edit SQL XML column in SQL Management Studio

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.

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 manager banging 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.

@Jacob's answer works very well, though you should add a REPLACE if you XML contains any ' characters:

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

I have a cheap and nasty workaround, but is ok. So, do a query of the record, i.e.

SELECT XMLData FROM [YourTable]
WHERE ID = @SomeID

Click on the xml data field, which should be 'hyperlinked'. This will open the XML in a new window. Edit it, then copy and paste the XML back into a new query window:

UPDATE [YourTable] SET XMLData = '<row><somefield1>Somedata</somefield1> 
                                  </row>'
WHERE ID = @SomeID

But yes, WE Desparately need to be able to edit. If you are listening Mr. Soft, please look at Oracle, you can edit XML in their Mgt Studio equivalent. Let's chalk it up to an oversight, I am still a HUGE fan of SQL server.

Ignoring the "easily" part the question title, here is a giant hack that is fairly decent, provided you deal with small XML columns.

This is a proof of concept without much thought to optimization. Written against 2008 R2.

--Drop any previously existing objects, so we can run this multiple times.
IF EXISTS (SELECT * FROM sysobjects WHERE Name = 'TableToUpdate')
    DROP TABLE TableToUpdate
IF EXISTS (SELECT * FROM sysobjects WHERE Name = 'vw_TableToUpdate')
    DROP VIEW vw_TableToUpdate

--Create our table with the XML column.
CREATE TABLE TableToUpdate(
    Id INT NOT NULL CONSTRAINT Pk_TableToUpdate PRIMARY KEY CLUSTERED IDENTITY(1,1),
    XmlData XML NULL
)

GO

--Create our view updatable view.
CREATE VIEW dbo.vw_TableToUpdate
AS
SELECT
    Id,
    CONVERT(VARCHAR(MAX), XmlData) AS XmlText,
    XmlData
FROM dbo.TableToUpdate

GO

--Create our trigger which takes the data keyed into a VARCHAR column and shims it into an XML format.
CREATE TRIGGER TR_TableToView_Update
ON dbo.vw_TableToUpdate
INSTEAD OF UPDATE

AS

SET NOCOUNT ON

DECLARE
@Id INT,
@XmlText VARCHAR(MAX)

DECLARE c CURSOR LOCAL STATIC FOR
SELECT Id, XmlText FROM inserted
OPEN c

FETCH NEXT FROM c INTO @Id, @XmlText
WHILE @@FETCH_STATUS = 0
BEGIN
    /*
    Slight limitation here.  We can't really do any error handling here because errors aren't really "allowed" in triggers.
    Ideally I would have liked to do a TRY/CATCH but meh.
    */
    UPDATE TableToUpdate
    SET
        XmlData = CONVERT(XML, @XmlText)
    WHERE
        Id = @Id

    FETCH NEXT FROM c INTO @Id, @XmlText
END

CLOSE c
DEALLOCATE c

GO

--Quick test before we go to SSMS
INSERT INTO TableToUpdate(XmlData) SELECT '<Node1/>'
UPDATE vw_TableToUpdate SET XmlText = '<Node1a/>'
SELECT * FROM TableToUpdate

If you open vw_TableToUpdate in SSMS, you are allowed to change the "XML", which will then update the "real" XML value.

Again, ugly hack, but it works for what I need it to do.

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.

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!

Another non-answer answer. You can use LinqPad. (https://www.linqpad.net/). It has the ability to edit SQL rows, including XML fields. You can also query for the rows you want to edit via SQL if you're not into LINQ.

My particular issue was attempting to edit an empty XML value into a NULL value. In SSMS the value showed as blank. However in LinqPad it showed as null. So in LinqPad I had to change it to , then back to null in order for the change to be saved. Now SSMS shows it as null too.

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;