Question:
What would the XQuery look like to check if a node exists, and if it does then run a replace statement, if not then an insert statement?
Here's what I have in mind. I want to store whether or not a user has read an important message in XML. Here's what the data would look like.
<usersettings> <message haveRead="0" messageId="23" ></message> <message haveRead="1" messageId="22" ></message> </usersettings> Basically this XML tells me that the user has read one message, while the other message still needs to be viewed / read.
I want to combine my insert / replace xquery into one statement. Here's what I had in mind.
UPDATE WebUsers SET UserSettings.modify(' declare default element namespace "http://www.test.com/test"; IF a node exists with the messageId code to replace node with new update ELSE code to insert a new node with the provided variables ') WHERE Id = @WebUserId
Solution:1
I haven't found a really satisfactory way of doing this, but one of these might work for you. I like the first technique better, but I'm annoyed that I haven't found a more elegant way of doing it.
Make sure the node always exists first
DECLARE @messageID int; SET @messageID=24; DECLARE @myDoc xml; SET @myDoc = '<usersettings> <message haveRead="0" messageId="23" >msg</message> <message haveRead="1" messageId="22" >msg</message> </usersettings>'; SELECT @myDoc; SET @myDoc.modify(' insert if (count(//message[@messageId=sql:variable("@messageID")]) = 0) then <message haveRead="0">new msg</message> else() as last into (/usersettings)[1] '); SELECT @myDoc; --now do the rest, safe that the node exists Switching
DECLARE @myDoc xml; SET @myDoc = '<usersettings> <message haveRead="0" messageId="23" >msg</message> <message haveRead="1" messageId="22" >msg</message> </usersettings>'; SELECT @myDoc; DECLARE @messageID int; SET @messageID=23; IF @myDoc.exist('//message[@messageId=sql:variable("@messageID")]') = 1 BEGIN SET @myDoc.modify('replace value of (//message[@messageId=sql:variable("@messageID")]/text())[1] with "test"') END ELSE BEGIN SET @myDoc.modify('insert <message haveRead="0">new msg</message> into (/usersettings)[1]') END SELECT @myDoc; Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon