How to easily edit an XML XML column in SQL Management Studio

Question

I have a table with an XML column. This column stores some values ​​that I save to configure my application. I created it to have a more flexible scheme. I cannot find a way to update this column directly from the table view in SQL Management Studio. Other (e.g. INT or Varchar) columns are editable. I know that I can write an UPDATE statement or create code to update it. But I'm looking for something more flexible that will allow power users to edit XML directly.

Any ideas?

I repeat: do not answer I can write a request. I know that, And that’s exactly what I’m trying to avoid.

Answer 1

This is an old question, but I needed to do it today. The best I can think of is to write a query that generates SQL code that can be edited in the query editor - it looks like a lame one, but it saves you copies / pastes.

Note: you may need to go to "Tools"> "Options"> "Query Results"> "Results in Text" and set the maximum number of characters displayed to a large enough number to match your XML fields.

eg.

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 creates a lot of queries that look like this (with some examples of table / field names):

 update thetable set thedata = '<root><name>Bob</name></root>' where thekey = 1 

Then you copy these queries from the results window to the query window, edit the xml lines and execute the queries.

(Edit: changed from 10 to maximum to avoid error)

Answer 2

Sql server management studio skips this function.

I see Homer Simpson as a Microsoft project manager hitting my head in the palm of my hand: "Duh!"

Of course, we want to edit the xml columns.

Answer 3

Answer to

@Jacob works very well, although you should add REPLACE if the 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] 

Answer 4

I started creating .net C # user interface for processing xml data. Using xsl for display and xml schemas helped to render xml and maintain integrity.

edit: Also C # contains an xmldocument class that makes reading / writing data easier.

Answer 5

I do not think that you can use GUI Management Studio to update XML columns without writing an UPDATE command.

One way to allow users to update xml data is to write a simple .net program (winforms or asp.net), and then select / update data from there. Thus, you can also sanitize the data and easily check for compliance with any given scheme before inserting / updating information.

Answer 6

I'm a little vague, but could you use the OPENXML method to destroy the XML in a relational format, and then save it back to XML after the user is finished?

Like others, I think it would be easier to write a small application to do this!

Answer 7

Ignoring the “easy” part of the question heading, here is a giant hack that is pretty decent if you are dealing with small XML columns.

This is a proof of concept without thinking about 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 can change the “XML”, which then updates the “real” XML value.

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

Answer 8

I have a cheap and unpleasant workaround, but everything is fine. So make a write request i.e.

 SELECT XMLData FROM [YourTable] WHERE ID = @SomeID 

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

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

But yes, WE Immediately need to be able to edit. If you are listening to Mr. Soft, please look at Oracle, you can edit XML in their equivalent Mgt Studio. Let me do this before being overlooked, I'm still a HUGE fan of SQL Server.