Xpath Get data from xml column with out cross apply-CodePudding

I have a table in SQL Server 2012 with this structure:

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

I want sql query that can give me the rows of listids where textreadingid = 1 exists in the data xml.

Which means in this case my result will be a table with ListIds 2, 3

What I tried below works but uses cross apply which was super slow in large data. I want a soltion with out 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

CodePudding user response：

The slow bit is not the CROSS APPLY .nodes but the DISTINCT.

Be that as it may, you can do this using the .exist function

select
  Listid
FROM tblStepList s 
WHERE s.Data.exist('/Steplist/Step/TextReadingId[text()="1"]') = 1

db<>fiddle

CodePudding user response：

I'm not familiar with sql and xquery you are using here, but the XPath expression to select Steplists you are looking for could be something like the following:

"//Steplist[.//TextReadingId[text()='1']]"