Check if the node if exists in a SQL Server xml column

Question

I have a column in my SQL Server table with this XML value :

<ValidTestResult version="1.0" encoding="UTF-8">
  <item CarType="پراید" SystemType="2" model="1382">
    <count>7777</count>
  </item>
  <item CarType="1" SystemType="2" model="1382">
    <count>100</count>
  </item>
  <item CarType="1" SystemType="2" model="1380">
    <count>100</count>
  </item>
</ValidTestResult>

I want to check if a node exists, update the value of count. I do that using this code:

UPDATE InquiryStatus
SET TotalCarburetorResult.modify(N'replace value of 
(/ValidTestResult/item[@model=("1382") and @CarType=("پراید")]/count/text())[1] with ("7777")') where id=27
GO

UPDATE InquiryStatus
SET TotalCarburetorResult.modify(N'insert <item CarType="پراید" SystemType="2" model="1382"><count>100</count></item> into (/Result)[1]')

As you can see my code inserts and updates the value, but I need something else: I want to check if the node exists, then update the value count and if doesn't exist, it should insert a new node. How can I check if the node exists?

For example a node with this attributes:

<item CarType="1" SystemType="2" model="1382">

Do you want to check on all three attributes? Or does one of them suffice? — marc_s
– marc_s, Commented Oct 29, 2017 at 15:44

marc_s · Accepted Answer · 2017-10-29 17:13:38Z

1

SELECT TotalCarburetorResult.exist(N'/ValidTestResult/item[@model=("1380") and @CarType=("پراید")]') 
FROM InquiryStatus 
WHERE id = 27

edited Oct 29, 2017 at 17:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 29, 2017 at 16:14

Ehsan Akbar

7,35723 gold badges106 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check if the node if exists in a SQL Server xml column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related