Update XML Node value in SQL-Server 2008

Sometimes we need to change the value of a node in Sql-Server. Here is the example by which we can update the xml node value


Example : 



DECLARE @StrXml AS XML = '<BooksHome><Books>1</Books><Books>2</Books><Books>3</Books><Books>4</Books><Books>5</Books><Books>6</Books></BooksHome>'


DECLARE @i INT = 1
DECLARE @j INT = 141


SET @StrXml.modify('replace value of (//BooksHome/Books[sql:variable("@i")]/text())[1] with sql:variable("@j")')


we can see the updated result as :


SELECT @StrXml