The target of 'replace' must be at most one node
One of the new features of SQL Server 2005 is the integration of XML in the database. Although XML in the database is not my favorite architecture there are situations where it is useful. Since we have not really used XML in a real life project before we are slowly learning its limitations.
A limitation we recently discovered is modifying more than 1 node at a time using XML DML. This limitation is documented in the Books Online under the "replace value of" topic: "If multiple nodes are selected, an error is raised."
The only idea we could come up with is to loop through the nodes until all the nodes have been updated. Lucky for us SQL Server also supports mixing XML with special functions like sql:variable() and sql:column().
CREATE
TABLE myXml
(xmlCol xml)
GO
INSERT
INTO myXml
VALUES ('<root><child updateme="true"/><child updateme="true"/><child updateme="true"/></root>')
SELECT * FROM myXml
/*
UPDATE myXml
SET xmlCol.modify('replace value of (/root/child/@updateme) with "false"')
This gives the following error:
Msg 2337, Level 16, State 1, Line 6
XQuery [myXml.xmlCol.modify()]: The target of 'replace' must be at most one node, found 'attribute(updateme,xdt:untypedAtomic) *'
*/
DECLARE
@nodeCount int
DECLARE @i int
SET
@i = 1
SELECT
@nodeCount = xmlCol.value('count(//root/child/@updateme)','int') FROM myXml
PRINT
'Number of nodes found: ' + STR(@nodeCount)
WHILE
(@i <= @nodeCount)
BEGIN
UPDATE myXml
SET xmlCol.modify('replace value of (/root/child/@updateme)[sql:variable("@i")][1] with "false"')
SET @i = @i + 1
END
SELECT
* FROM myXml
DROP
TABLE myXml
*EDIT*
An alternative for the node count approach might also be the exist function. This might come in handy if you have to update several records in a single query where the node count may be different for every record.
WHILE EXISTS (SELECT * FROM myXml WHERE xmlCol.exist('/root/child/[@updateme="true"]') = 1)