Tutorial :XQuery if exists conditional insert / replace



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
Previous
Next Post »