Comment modifier facilement la colonne SQL XML dans SQL Management Studio
J'ai une table avec une colonne XML. Cette colonne stocke certaines valeurs que je garde pour configurer mon application. Je l'ai créé pour avoir un schéma plus flexible. Je ne trouve pas de moyen de mettre à jour cette colonne directement à partir de la vue table dans SQL Management Studio. D'autres colonnes (INT ou Varchar par exemple) sont modifiables. Je sais que je peux écrire une déclaration de mise à jour ou créer du code pour la mettre à jour. Mais je cherche quelque chose de plus flexible qui permettra aux utilisateurs de pouvoir éditer le XML directement.
Tout des idées?
Réitérant encore: Veuillez ne pas répondre Je peux écrire une application. Je sais que, Et c'est exactement ce que je suis essayez d'éviter.
9 réponses
C'est une vieille question, mais je devais le faire aujourd'hui. Le mieux que je puisse trouver est d'écrire une requête qui génère du code SQL qui peut être édité dans l'éditeur de requête - c'est un peu boiteux mais cela vous évite de copier/coller des choses.
Remarque: vous devrez peut-être aller dans Outils > Options > résultats de requête > résultats en texte et définir le nombre maximum de caractères affichés sur un nombre suffisant pour correspondre à vos champs XML.
Par exemple
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]
Qui produit beaucoup de requêtes qui regarde comme ceci (avec quelques exemples de noms de table / Champ):
update thetable set thedata = '<root><name>Bob</name></root>' where thekey = 1
Vous copiez ensuite ces requêtes à partir de la fenêtre de résultats dans la fenêtre de requête, modifiez les chaînes xml, puis exécutez les requêtes.
(Edit: changé 10 à max pour éviter l'erreur)
Sql server management studio manque cette fonctionnalité.
Je peux voir Homer Simpson en tant que gestionnaire de projet Microsoft se cogner la tête avec la paume de sa main: "Bah!"
Bien Sûr, nous voulons éditer des colonnes xml.
J'ai fini par écrire une interface utilisateur. Net c# pour traiter les données xml. L'utilisation de xsl pour l'affichage et d'un schéma xml a aidé à afficher le xml et à maintenir son intégrité.
Edit: c # contient également la classe xmldocument qui simplifie la lecture / écriture des données.
La réponse de @ Jacob fonctionne très bien, bien que vous devriez ajouter un REPLACE si vous XML contient des ' caractères:
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]
Je ne pense pas que vous puissiez utiliser L'interface graphique Management Studio pour mettre à jour les colonnes XML sans écrire vous-même la commande UPDATE.
Une façon de permettre aux utilisateurs de mettre à jour les données xml est d'écrire un simple programme basé sur. net (winforms ou asp.net) puis sélectionnez / mettez à jour les données à partir de là. De cette façon, vous pouvez également désinfecter les données et les valider facilement par rapport à un schéma donné avant d'insérer/mettre à jour les informations.
Je suis un peu flou dans ce sont mais ne pourriez-vous pas utiliser la méthode OPENXML pour déchiqueter le XML au format relationnel puis le sauvegarder en XML une fois que l'Utilisateur a terminé?
Comme d'autres l'ont dit, je pense qu'il pourrait être plus facile d'écrire une petite application pour le faire!
Ignorant la partie" facilement " du titre de la question, voici un hack géant qui est assez décent, à condition que vous traitiez de petites colonnes XML.
C'est une preuve de concept sans trop penser à l'optimisation. Écrit contre 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
Si vous ouvrez vw_TableToUpdate dans SSMS, vous êtes autorisé à modifier le "XML", qui mettra ensuite à jour la valeur XML" réelle".
Encore une fois, hack laid, mais cela fonctionne pour ce que j'ai besoin de faire.
J'ai une solution de contournement pas cher et méchant, mais est ok. Donc, faire une requête de l'enregistrement, c'est à dire
SELECT XMLData FROM [YourTable]
WHERE ID = @SomeID
Cliquez sur le champ de données xml, qui doit être 'hyperlien'. Cela ouvrira le XML dans une nouvelle fenêtre. Modifiez-le, puis copiez et collez le XML dans une nouvelle fenêtre de requête:
UPDATE [YourTable] SET XMLData = '<row><somefield1>Somedata</somefield1>
</row>'
WHERE ID = @SomeID
Mais oui, nous avons désespérément besoin de pouvoir éditer. Si vous écoutez MR. Soft, veuillez regarder Oracle, vous pouvez éditer XML dans leur équivalent Mgt Studio. Nous allons craie jusqu'à un oubli, je suis toujours un Grand fan de SQL server.
Une autre réponse sans réponse. Vous pouvez utiliser LinqPad. ( https://www.linqpad.net/). il a la possibilité d'éditer des lignes SQL, y compris des champs XML. Vous pouvez également interroger les lignes que vous souhaitez modifier via SQL si vous n'êtes pas dans LINQ.
Mon problème particulier tentait d'éditer une valeur XML vide en une valeur NULL. Dans SSMS, la valeur était vide. Cependant, dans LinqPad, il a montré comme null. Donc, dans LinqPad, j'ai dû le changer en, puis revenir à null pour que le changement soit enregistré. Maintenant SSMS le montre aussi comme null.