SQL Server 2012：无需CROSS APPLY从XML列筛选TextReadingId=1的ListId

在SQL Server 2012环境中，存在如下结构的表tblStepList，其中Data列存储XML格式数据：

USE tempdb;
GO

-- DDL and sample data population, start
DROP TABLE IF EXISTS [dbo].[tblStepList];

CREATE TABLE [dbo].[tblStepList](
    [listid] [int],
    [Data] [xml] NOT NULL
);

INSERT INTO dbo.tblStepList (Listid, [Data]) VALUES
(1, N'<Steplist>
  <Step>
    <StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingId>10</TextReadingId>      
  </Step>
  <Step>
    <StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
    <TextReadingId>12</TextReadingId>
  </Step>
</Steplist>'),
(2, N'<Steplist>
  <Step>
    <StepId>d9e42387-56e3-40a1-9698-e89c930d98d1</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>   
    <TextReadingId>0</TextReadingId>  
  </Step>
  <Step>
    <StepId>e5eaf947-24e1-4d3b-a92a-d6a90841293b</StepId>
    <Rank>2</Rank>
     <TextReadingId>1</TextReadingId>  
  </Step>
</Steplist>'),
(3, N'<Steplist>
  <Step>
    <StepId>d9e42387-56e3-40a1-9698-58e5bfe2a987</StepId>
    <Rank>1</Rank>
    <IsComplete>true</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>   
    <TextReadingId>1</TextReadingId>  
  </Step>
  <Step>
    <StepId>e5eaf947-24e1-4d3b-a92a-d2f6a5126ba1</StepId>
    <Rank>2</Rank>
     <TextReadingId>1</TextReadingId>  
  </Step>
</Steplist>');
-- DDL and sample data population, end

需求是筛选出Data列的XML中存在TextReadingId=1的ListId，预期结果为ListId 2、3。原使用CROSS APPLY的查询虽然可行，但大数据量下性能较差：

select  distinct Listid from
(
    SELECT 
        s.Listid,
        x.XmlCol.value('(TextReadingId)[1]', 'int') as [TextReadingId]
    FROM tblStepList s 
    CROSS APPLY s.Data.nodes('/Steplist/Step') x(XmlCol)
) a
where TextReadingId = 1

优化方案：使用XML的exist()方法 #

无需展开XML节点，直接利用SQL Server XML类型的exist()方法判断是否存在符合条件的节点，避免生成大量中间结果，大幅提升大数据量下的查询性能。

优化后的查询语句 #

SELECT Listid
FROM dbo.tblStepList
WHERE Data.exist('/Steplist/Step[TextReadingId = 1]') = 1;

原理说明 #

• exist()方法接收XPath表达式作为参数，返回1表示XML中存在匹配的节点，返回0表示不存在。
• XPath表达式/Steplist/Step[TextReadingId = 1]用于定位：在<Steplist>根节点下，找到包含<TextReadingId>值为1的<Step>节点。
• 该查询直接在原表上过滤，无需展开所有<Step>节点，避免了CROSS APPLY带来的额外开销，尤其在数据量较大时性能优势明显。

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