How to Easily Edit SQL Xml Column in SQL Management Studio

How to easily edit SQL XML column in SQL Management Studio

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.

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.

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.

(Edit: changed 10 to max to avoid error)

How to update xml field in SQL Server

You were told already, that your XML should be stored as native XML.

Your problem is multifolded

• wrong datatype (NVARCHAR(MAX) instead of XML)
• A cast to XML from NVARCHAR is not allowed with a declaration stating UTF-8 encoding
• .modify is not applicable on the fly

So the workaround is a temp table

A mockup scenario

DECLARE @tbl TABLE(ID INT IDENTITY, YourXmlAsString NVARCHAR(MAX));
INSERT INTO @tbl VALUES
('<?xml version="1.0" encoding="UTF-8"?>
<main>  
    <sub>
        <subMode>
            <name>abc</name>
            <address>add abc</address>
        </subMode>
        <subMode>
            <name>xyz</name>
            <address>add xyz</address>
        </subMode>
    </sub>
</main>');

--This SELECT converts your string-XML and stores the result as real XML

SELECT ID
        ,CAST(REPLACE(YourXmlAsString,'UTF-8','UTF-16') AS XML) CastedToRealXML
        ,YourXmlAsString AS OriginalValue
INTO #tempTblKeepsCastedValue
FROM @tbl 
--WHERE SomeCriteria;

--Your search for xyz and append something to the existing value

DECLARE @SearchForName NVARCHAR(100)=N'xyz';
DECLARE @Append NVARCHAR(100)=N'add to the value';

UPDATE #tempTblKeepsCastedValue
SET CastedToRealXML.modify('replace value of 
                            (/main/sub/subMode[name/text()=sql:variable("@SearchForName")]/address/text())[1]
                            with concat((/main/sub/subMode[name/text()=sql:variable("@SearchForName")]/address/text())[1],sql:variable("@Append"))');

--Now you update the original tabel using an INNER JOIN to the temp table

UPDATE t
SET YourXmlAsString=CAST(tmp.CastedToRealXml AS NVARCHAR(MAX))
FROM @tbl AS t
INNER JOIN #tempTblKeepsCastedValue AS tmp ON t.ID=tmp.ID;

--The result (and clean-up)

SELECT * FROM @tbl
DROP TABLE #tempTblKeepsCastedValue;

ID  YourXmlAsString
1   <main><sub><subMode><name>abc</name><address>add abc</address></subMode><subMode><name>xyz</name><address>add xyzadd to the value</address></subMode></sub></main>

Update XML stored in a XML column in SQL Server

Since there is a XML namespace (xmlns:dev="http://www.w3.org/2001/XMLSchema") in your XML document, you must inlcude that in your UPDATE statement!

Try this:

;WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/2001/XMLSchema')
UPDATE XmlTable
SET XmlDocument.modify('replace value of (/Doc/@Settings)[1] with "NewTest"')
WHERE XmlId = 1

Can't update an xml field in a column of a database

After correctly setting the value of @a to be an xml fragment, not a value, if y is an attribute, your syntax would be similar to:

DECLARE @a xml = N'<root><x y="2"/></root>';
SELECT @a;

SET @a.modify('replace value of (/root/x/@y)[1] with "42"');
SELECT @a;

yields <root><x y="42"/></root>

If y is an element your syntax would be:

DECLARE @a xml = N'<root><x><y>hello</y></x></root>';
SELECT @a;

SET @a.modify('replace value of (/root/x/y/text())[1] with "world"');
SELECT @a;

yields <root><x><y>world</y></x></root>

Applying this to a table, here is sample code that will run as is:

DECLARE @T TABLE(NS varchar(32), NM varchar(16), DocXml xml)
INSERT INTO @T 
  VALUES ('MyNamespace1', 'MyName1', N'<root><x y="2"/></root>'),
         ('MyNamespace2', 'MyName2', N'<root><x y="2"/></root>')

UPDATE @T SET DocXml.modify('replace value of (/root/x/@y)[1] with "42"')
 WHERE NS = 'MyNamespace1' AND NM = 'MyName1';

SELECT * FROM @T     

However, if the 'xml' column is a text column with xml data and not typed as xml, you cannot both cast to xml and call modify in one update statement. You ought to consider either altering the table column to the xml type, or adding an xml column.

Here would be one workaround, assuming your original table has a unique Id:

-- represents original table...
CREATE TABLE T(Id int, NS varchar(32), NM varchar(16), DocXml ntext)
INSERT INTO T 
  VALUES (1, 'MyNamespace1', 'MyName1', N'<root><x y="2"/></root>'),
         (2, 'MyNamespace2', 'MyName2', N'<root><x y="2"/></root>');

-- select target records into tempdb
SELECT Id, CAST(DocXml AS xml) AS DocXml INTO #tmp FROM T WHERE NS = 'MyNamespace1' AND NM = 'MyName1';

-- update tempdb
UPDATE #tmp SET DocXml.modify('replace value of (/root/x/@y)[1] with "42"');

-- and copy back to original table
    UPDATE T
       SET DocXml = CAST(U.DocXml AS nvarchar(MAX))
      FROM dbo.T TT
INNER JOIN #tmp U ON TT.Id = U.Id

SELECT * FROM T

SQL Server XML is Read Only?

It appears that you are trying to edit this through SSMS - you can't do it through that. (I believe that XML fields cannot be edited in SSMS as it isn't set up to do the necessary validation, and MS didn't want people doing edits and getting errors.)

However, if you use another client, such as MS Access, to edit the table/view data, you will probably find that it can be edited.

Alternatively, you could copy the XML, and paste it into an: UPDATE TableOrViewName SET FieldName = 'Your modified XML Here' WHERE PKField = PKValue statement in a new query window. You wouldn't want to do this manually for any more than a few records.

Return a XML field when using GROUP BY clause in MS SQL Management Studio?

Uhm, this seems dirty... If your XMLs are identically within the group, you might try something like this:

DECLARE @tbl TABLE(ID INT IDENTITY,Col1 VARCHAR(100),SomeValue INT,SomeXML XML);
INSERT INTO @tbl(col1,SomeValue,SomeXML) VALUES
 ('testA',1,'<root><a>testA</a></root>')
,('testA',2,'<root><a>testA</a></root>')
,('testB',3,'<root><a>testB</a></root>')
,('testB',4,'<root><a>testB</a></root>');

WITH GroupedSource AS
(
    SELECT SUM(SomeValue) AS SumColumn
          ,CAST(SomeXml AS NVARCHAR(MAX)) AS XmlColumn
    FROM @tbl AS tbl
    GROUP BY Col1,CAST(SomeXml AS NVARCHAR(MAX))
)
SELECT SumColumn
      ,CAST(XmlColumn AS XML) AS ReCasted
FROM GroupedSource

Another approach was this

WITH GroupedSource AS
(
    SELECT SUM(SomeValue) AS SumColumn
          ,MIN(ID) AS FirstID 
    FROM @tbl AS tbl
    GROUP BY Col1
)
SELECT SumColumn
      ,(SELECT SomeXML FROM @tbl WHERE ID=FirstID) AS ReTaken
FROM GroupedSource

Update or insert element into xml column dependent on existence

The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

It's probably simplest to do it in two statements.

UPDATE Table 
SET xmlCol.modify('replace value of /blah')
WHERE xmlCol.exist('/blah') = 1;

UPDATE Table
SET xmlCol.modify('insert /blah')
WHERE xmlCol.exist('/blah') = 0;

Update XML node value in SQL Server

You can do something like this:

UPDATE 
   dbo.profiles
SET 
   ProfileXML.modify('replace value of (/ProblemProfile/GroupID/text())[1] with "0"')
WHERE
   id = 23

Check out this article on SQL Server 2005 XQuery and XML-DML for more details on what you can do with the .modify keyword (insert, delete, replace etc.).

Marc

PS: In order to get the value, it would be a lot easier to do just this:

SELECT ProfileXML.value('(/ProblemProfile/GroupID)[1]', 'int') as ID
FROM dbo.profiles
WHERE id = 23

(unless of course you need the XML as a SQL variable for something else later on)

---

Related Topics