How to easily edit SQL XML column in SQL Management Studio

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.

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]

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 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;

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.

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.