如何在 SQL Management Studio 中轻松编辑 SQL XML 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/53026/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to easily edit SQL XML column in SQL Management Studio
提问by Ron Harlev
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.
我有一个带有 XML 列的表。此列存储了我为配置应用程序而保留的一些值。我创建它是为了拥有更灵活的架构。我找不到直接从 SQL Management Studio 中的表视图更新此列的方法。其他（例如 INT 或 Varchar）列是可编辑的。我知道我可以编写 UPDATE 语句或创建一些代码来更新它。但我正在寻找更灵活的东西，让高级用户可以直接编辑 XML。
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.
再次重申：请不要回答我可以写一个应用程序。我知道，而这正是我想要避免的。
采纳答案by Jacob
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.
这是一个老问题，但我今天需要这样做。我能想到的最好的方法是编写一个查询来生成可以在查询编辑器中编辑的 SQL 代码 - 这有点蹩脚，但它可以节省您复制/粘贴的东西。
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.
注意：您可能需要进入工具 > 选项 > 查询结果 > 结果转文本并将显示的最大字符数设置为足够大的数字以适合您的 XML 字段。
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.
然后将这些查询从结果窗口复制回查询窗口，编辑 xml 字符串，然后运行查询。
(Edit: changed 10 to max to avoid error)
（编辑：将 10 更改为最大值以避免错误）
回答by Todd
sql server management studio is missing this feature.
sql server management studio 缺少此功能。
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.
当然，我们要编辑 xml 列。
回答by Paulj
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.
我最终编写了一个 .net c# UI 来处理 xml 数据。使用 xsl 进行显示和 xml 模式有助于很好地显示 xml 并保持其完整性。
edit: Also c# contains the xmldocument class that simplifies reading/writing the data.
编辑：此外，c# 包含简化读取/写入数据的 xmldocument 类。
回答by laktak
@Jacob's answer works very well, though you should add a REPLACE if you XML contains any ' characters:
@Jacob 的回答非常有效，但如果您的 XML 包含任何 ' 字符，您应该添加一个 REPLACE：
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]
回答by NightShovel
Ignoring the "easily" part the question title, here is a giant hack that is fairly decent, provided you deal with small XML columns.
忽略问题标题的“轻松”部分，这是一个相当不错的巨型黑客，前提是您处理小型 XML 列。
This is a proof of concept without much thought to optimization. Written against 2008 R2.
这是一个概念证明，没有过多考虑优化。针对 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.
如果您在 SSMS 中打开 vw_TableToUpdate，您可以更改“XML”，这将更新“真实”的 XML 值。
Again, ugly hack, but it works for what I need it to do.
再次，丑陋的黑客，但它适用于我需要它做的事情。
回答by Arvin Amir
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:
单击应为“超链接”的 xml 数据字段。这将在新窗口中打开 XML。编辑它，然后将 XML 复制并粘贴回新的查询窗口：
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.
但是是的，我们非常需要能够编辑。如果您正在听 Mr. Soft，请查看 Oracle，您可以在其 Mgt Studio 中编辑 XML。让我们把它归结为一个疏忽，我仍然是 SQL 服务器的忠实粉丝。
回答by Jeremy
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.
另一个没有答案的答案。您可以使用 LinqPad。（https://www.linqpad.net/）。它能够编辑 SQL 行，包括 XML 字段。如果您不使用 LINQ，还可以通过 SQL 查询要编辑的行。
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.
我的特殊问题是尝试将空的 XML 值编辑为 NULL 值。在 SSMS 中，该值显示为空白。但是在 LinqPad 中它显示为空。因此，在 LinqPad 中，我必须将其更改为 ，然后返回 null 以保存更改。现在 SSMS 也将其显示为 null。
回答by Almond
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?
我在这方面有点模糊，但是您不能使用 OPENXML 方法将 XML 分解为关系格式，然后在用户完成后将其保存回 XML 中吗？
Like others have said I think it might be easier to write a small app to do it!
就像其他人所说的那样，我认为编写一个小应用程序来做到这一点可能会更容易！
回答by Espo
I do not think you can use the Management Studio GUI to update XML-columns without writing the UPDATE-command yourself.
我不认为您可以使用 Management Studio GUI 来更新 XML 列，而无需自己编写 UPDATE 命令。
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.
让用户更新 xml-data 的一种方法是编写一个简单的基于 .net 的程序（winforms 或 asp.net），然后从那里选择/更新数据。通过这种方式，您还可以在插入/更新信息之前清理数据并根据任何给定模式轻松验证数据。
