SQL Server中XML多节点IsSelected更新问题及节点计数需求

Got it, let's work through your XML update issue together. Here's a breakdown of what's going wrong, how to fix it, and how to use node counting to refine your logic:

The core issue is that your modify statement only updates the first <Plan> node because your XPath targeting logic is off. Right now, /*/Plans/Plan[sql:variable("@PlanID")] is trying to match a node by its position index (1-based), but if you intended to target a node by an ID attribute (which isn't shown in your XML example), this syntax won't work. Additionally, getting an accurate count of <Plan> nodes will let you adjust your update logic to handle edge cases (like only one existing node).

To get the total number of <Plan> nodes in your XML (which should return 3 for your sample), use this SQL snippet—similar to how you counted <Option> nodes:

DECLARE @PlanCount INT;
SET @PlanCount = (SELECT COUNT(*) FROM @DataXml.nodes('/Plans/Plan') AS Tbl(Col));

This traverses all <Plan> nodes under <Plans> and returns their total count.

Scenario 1: Your <Plan> nodes have an ID attribute #

If your actual XML includes an ID on each <Plan> (e.g., <Plan ID="1">), fix your XPath to target nodes where the ID attribute matches @PlanID:

SET @DataXml.modify('
    replace value of (/*/Plans/Plan[@ID=sql:variable("@PlanID")]/Details/IsSelected/text())[1] 
    with sql:variable("@IsSelectedValue")
');

This will correctly target the specific <Plan> you want, not just the first one.

Scenario 2: You're targeting nodes by position index #

If you do want to target by position (1-based), ensure @PlanID is an integer corresponding to the node's position (e.g., @PlanID=2 for the second <Plan>). The syntax you used works here, but you can add a check against the node count to avoid invalid updates:

IF @PlanCount >= @PlanID
BEGIN
    SET @DataXml.modify('
        replace value of (/*/Plans/Plan[sql:variable("@PlanID")]/Details/IsSelected/text())[1] 
        with sql:variable("@IsSelectedValue")
    ');
END

You can extend the node counting approach to handle <Plan> nodes just like you did with <Option> nodes. For example:

DECLARE @PlanCount INT;
SET @PlanCount = (SELECT COUNT(*) FROM @DataXml.nodes('/Plans/Plan') AS Tbl(Col));

IF @PlanCount > 1
BEGIN
    -- Multiple plans exist: target by ID/position
    SET @DataXml.modify('
        replace value of (/*/Plans/Plan[@ID=sql:variable("@PlanID")]/Details/IsSelected/text())[1] 
        with sql:variable("@IsSelectedValue")
    ');
END
ELSE
BEGIN
    -- Only one plan exists: update the only node directly
    SET @DataXml.modify('
        replace value of (/*/Plans/Plan/Details/IsSelected/text())[1] 
        with sql:variable("@IsSelectedValue")
    ');
END

For your provided XML:

<Plans>
  <Plan>
    <Details>
      <IsSelected>true</IsSelected>
    </Details>
  </Plan>
  <Plan>
    <Details>
      <IsSelected>false</IsSelected>
    </Details>
  </Plan>
  <Plan>
    <Details>
      <IsSelected>false</IsSelected>
    </Details>
  </Plan>
</Plans>

• The node count query returns 3
• If you set @PlanID=2 (position) and @IsSelectedValue='true', the second <Plan>'s IsSelected will update to true instead of just the first node.

内容的提问来源于stack exchange，提问作者Kapil