<p>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.</p>

<p>Any ideas?</p>

<blockquote>
  <p>Reiterating again: Please don't answer
  I can write an application. I know
  that, And that is exactly what I'm
  trying to avoid.</p>
</blockquote>

[{'answer_id': 53032, 'author': 'Espo', 'author_id': 2257, 'author_profile': 'https://Stackoverflow.com/users/2257', 'pm_score': 0, 'selected': False, 'text': '<p>I do not think you can use the Management Studio GUI to update XML-columns without writing the UPDATE-command yourself.</p>\n\n<p>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.</p>\n'}, {'answer_id': 53051, 'author': 'Paulj', 'author_id': 5433, 'author_profile': 'https://Stackoverflow.com/users/5433', 'pm_score': 2, 'selected': False, 'text': "<p>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.</p>\n\n<p>edit: Also c# contains the xmldocument class that simplifies reading/writing the data.</p>\n"}, {'answer_id': 53084, 'author': 'Almond', 'author_id': 1603, 'author_profile': 'https://Stackoverflow.com/users/1603', 'pm_score': 0, 'selected': False, 'text': "<p>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?</p>\n\n<p>Like others have said I think it might be easier to write a small app to do it!</p>\n"}, {'answer_id': 3474289, 'author': 'Todd', 'author_id': 419230, 'author_profile': 'https://Stackoverflow.com/users/419230', 'pm_score': 4, 'selected': False, 'text': '<p>sql server management studio is missing this feature.</p>\n\n<p>I can see Homer Simpson as the Microsoft project manager\nbanging his head with the palm of his hand:\n"Duh!"</p>\n\n<p>Of course, we want to edit xml columns.</p>\n'}, {'answer_id': 8827253, 'author': 'Jacob', 'author_id': 181298, 'author_profile': 'https://Stackoverflow.com/users/181298', 'pm_score': 6, 'selected': True, 'text': "<p>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.</p>\n\n<p>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.</p>\n\n<p>e.g.</p>\n\n<pre><code>select 'update [table name] set [xml field name] = ''' + \nconvert(varchar(max), [xml field name]) +\n''' where [primary key name] = ' + \nconvert(varchar(max), [primary key name]) from [table name]\n</code></pre>\n\n<p>which produces a lot of queries that look like this (with some sample table/field names):</p>\n\n<pre><code>update thetable set thedata = '&lt;root&gt;&lt;name&gt;Bob&lt;/name&gt;&lt;/root&gt;' where thekey = 1\n</code></pre>\n\n<p>You then copy these queries from the results window back up to the query window, edit the xml strings, and then run the queries.</p>\n\n<p>(Edit: changed 10 to max to avoid error)</p>\n"}, {'answer_id': 16287206, 'author': 'laktak', 'author_id': 52817, 'author_profile': 'https://Stackoverflow.com/users/52817', 'pm_score': 2, 'selected': False, 'text': "<p>@Jacob's answer works very well, though you should add a REPLACE if you XML contains any ' characters:</p>\n\n<pre><code>select 'update [table name] set [xml field name] = ''' + \nREPLACE(convert(varchar(max), [xml field name]), '''', '''''') +\n''' where [primary key name] = ' + \nconvert(varchar(max), [primary key name]) from [table name]\n</code></pre>\n"}, {'answer_id': 23250530, 'author': 'NightShovel', 'author_id': 895218, 'author_profile': 'https://Stackoverflow.com/users/895218', 'pm_score': 1, 'selected': False, 'text': '<p>Ignoring the "easily" part the question title,  here is a giant hack that is fairly decent, provided you deal with small XML columns.</p>\n\n<p>This is a proof of concept without much thought to optimization.  Written against 2008 R2.</p>\n\n<pre><code>--Drop any previously existing objects, so we can run this multiple times.\nIF EXISTS (SELECT * FROM sysobjects WHERE Name = \'TableToUpdate\')\n    DROP TABLE TableToUpdate\nIF EXISTS (SELECT * FROM sysobjects WHERE Name = \'vw_TableToUpdate\')\n    DROP VIEW vw_TableToUpdate\n\n--Create our table with the XML column.\nCREATE TABLE TableToUpdate(\n    Id INT NOT NULL CONSTRAINT Pk_TableToUpdate PRIMARY KEY CLUSTERED IDENTITY(1,1),\n    XmlData XML NULL\n)\n\nGO\n\n--Create our view updatable view.\nCREATE VIEW dbo.vw_TableToUpdate\nAS\nSELECT\n    Id,\n    CONVERT(VARCHAR(MAX), XmlData) AS XmlText,\n    XmlData\nFROM dbo.TableToUpdate\n\nGO\n\n--Create our trigger which takes the data keyed into a VARCHAR column and shims it into an XML format.\nCREATE TRIGGER TR_TableToView_Update\nON dbo.vw_TableToUpdate\nINSTEAD OF UPDATE\n\nAS\n\nSET NOCOUNT ON\n\nDECLARE\n@Id INT,\n@XmlText VARCHAR(MAX)\n\nDECLARE c CURSOR LOCAL STATIC FOR\nSELECT Id, XmlText FROM inserted\nOPEN c\n\nFETCH NEXT FROM c INTO @Id, @XmlText\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    /*\n    Slight limitation here.  We can\'t really do any error handling here because errors aren\'t really "allowed" in triggers.\n    Ideally I would have liked to do a TRY/CATCH but meh.\n    */\n    UPDATE TableToUpdate\n    SET\n        XmlData = CONVERT(XML, @XmlText)\n    WHERE\n        Id = @Id\n\n    FETCH NEXT FROM c INTO @Id, @XmlText\nEND\n\nCLOSE c\nDEALLOCATE c\n\nGO\n\n--Quick test before we go to SSMS\nINSERT INTO TableToUpdate(XmlData) SELECT \'&lt;Node1/&gt;\'\nUPDATE vw_TableToUpdate SET XmlText = \'&lt;Node1a/&gt;\'\nSELECT * FROM TableToUpdate\n</code></pre>\n\n<p>If you open vw_TableToUpdate in SSMS, you are allowed to change the "XML", which will then update the "real" XML value.</p>\n\n<p>Again, ugly hack, but it works for what I need it to do.</p>\n'}, {'answer_id': 37735167, 'author': 'Vinnie Amir', 'author_id': 5336001, 'author_profile': 'https://Stackoverflow.com/users/5336001', 'pm_score': 2, 'selected': False, 'text': "<p>I have a cheap and nasty workaround, but is ok.  So, do a query of the record, i.e. </p>\n\n<pre><code>SELECT XMLData FROM [YourTable]\nWHERE ID = @SomeID\n</code></pre>\n\n<p>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:</p>\n\n<pre><code>UPDATE [YourTable] SET XMLData = '&lt;row&gt;&lt;somefield1&gt;Somedata&lt;/somefield1&gt; \n                                  &lt;/row&gt;'\nWHERE ID = @SomeID\n</code></pre>\n\n<p>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.</p>\n"}, {'answer_id': 49806856, 'author': 'Jeremy', 'author_id': 1203651, 'author_profile': 'https://Stackoverflow.com/users/1203651', 'pm_score': 0, 'selected': False, 'text': '<p>Another non-answer answer. You can use LinqPad. (<a href="https://www.linqpad.net/" rel="nofollow noreferrer">https://www.linqpad.net/</a>). 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.</p>\n\n<p>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. </p>\n'}, {'answer_id': 72232414, 'author': 'A P', 'author_id': 1149580, 'author_profile': 'https://Stackoverflow.com/users/1149580', 'pm_score': 0, 'selected': False, 'text': '<p>I know this is a really old question but I hope this might help someone.</p>\n<p>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.</p>\n<p>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.</p>\n<pre><code>ALTER TABLE [tablename]\nALTER COLUMN [columnname] varchar(max);\n</code></pre>\n<p>Once you are done with edits, alter the column back to XML.</p>\n<pre><code>ALTER TABLE [tablename]\nALTER COLUMN [columnname] XML;\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/53026', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1363/']