在SQL Server中统计XML非空b节点的技术问询

The problem with your current query is that count(//a/b) counts all <b> nodes—including empty ones like <b/>. To get the expected result of 2 (only non-empty <b> nodes), you need to add a filter predicate to exclude nodes with no content.

Solution 1: Filter nodes with existing text content #

Use the text() function to check if the <b> node has a text child. Empty <b/> nodes don’t have a text node, so they’ll be excluded automatically:

select top 1 rc.XmlContent.value('count(//a/b[text()])', 'int') from Table rc

Solution 2: Check string length of the node value #

Another reliable approach is to verify the string length of the <b> node’s value is greater than 0. This works even if you need to account for edge cases like whitespace-only nodes (just add normalize-space):

-- Basic check for non-empty values
select top 1 rc.XmlContent.value('count(//a/b[string-length(.) > 0])', 'int') from Table rc

-- Exclude whitespace-only nodes (e.g., <b>  </b>)
select top 1 rc.XmlContent.value('count(//a/b[normalize-space(.) != ""])', 'int') from Table rc

Why this works for your sample XML #

For the input <a> <b>1</b> <b/> <b>g</b> </a>:

• Both solutions will pick <b>1</b> and <b>g</b> since they have non-empty content.
• The empty <b/> node gets filtered out because it has no text child and its string length is 0.

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