Updating multiple attribute in XML using "replace value of" is challenge as, we are constraint to update 1 value at once. To achieve this we need to loop for each attribute.
Below is script used in lesson:
DECLARE @T TABLE
(
xmltext XML
)
INSERT INTO @T
VALUES ('<Configuration> <OptIn> <CodeId IncludeChildren="True">2548578015</CodeId> <ReadCode>93C0%</ReadCode> </OptIn> <OptOut> <CodeId IncludeChildren="True">2533649013</CodeId> <CodeId IncludeChildren="True">2548920016</CodeId> </OptOut> </Configuration>'),
('<Configuration> <OptIn> <CodeId IncludeChildren="True">2548578015</CodeId> <ReadCode>93C0%</ReadCode> </OptIn> <OptOut> <CodeId IncludeChildren="True">2533649013</CodeId> <CodeId IncludeChildren="True">2548920016</CodeId> <CodeId IncludeChildren="True">2548920016</CodeId> </OptOut> </Configuration>'),
('<Configuration> <OptIn> <CodeId IncludeChildren="True">2548578015</CodeId> <ReadCode>93C0%</ReadCode> </OptIn> <OptOut> <CodeId IncludeChildren="True">2533649013</CodeId> <CodeId IncludeChildren="True">2548920016</CodeId> </OptOut> </Configuration>')
SELECT *
FROM @T
WHILE EXISTS (SELECT 1
FROM @t
WHERE
xmltext.exist('/Configuration/OptOut/CodeId[ contains(@IncludeChildren , "True")]') = 1)
BEGIN
UPDATE m
SET
xmltext.modify('replace value of (/Configuration/OptOut/CodeId[ contains(@IncludeChildren , "True")]/@IncludeChildren)[1] with "false" ')
FROM (SELECT xmltext,
Replace(t.u.value('./@IncludeChildren[1]', 'varchar(100)'), 'True', 'False') AS
replaceval
FROM @t p
CROSS apply
p.xmltext.nodes('/Configuration/OptOut/CodeId[ contains(@IncludeChildren , "True")]')t(u))m
END
SELECT *
FROM @T
|