XQuery sql select node only if exists-CodePudding

I am using sql server 2012 I am looking a way to select some node values from my table with xml data type as follows

I have a the following schema

USE tempdb;
GO

DROP TABLE IF EXISTS [dbo].[tblStepList];

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

INSERT INTO dbo.tblStepList ([Data]) VALUES
(N'<Steplist>
  <Step>
    <StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>     
  </Step>
  <Step>
    <StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
    <TextReadingId>12</TextReadingId>

  </Step>
</Steplist>');





    INSERT INTO dbo.tblStepList ([Data]) VALUES
(N'<Steplist>
  <Step>
    <StepId>9960222B-897F-44E9-82FE-F33705D0C2B6</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>   
    <TextReadingId>0</TextReadingId>  
  </Step>
  <Step>
    <StepId>11D70A50-08AC-4767-A0D3-87717384FF45</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
  </Step>
</Steplist>')

Now I want to a select statement to get all the steps with no TextReadingId nodes and this is what I tried but I am not able to do exists in nodes

SELECT

x.XmlCol.value('(StepId)[1]', 'uniqueidentifier') as StepId

FROM tblStepList   s
CROSS APPLY s.Data.nodes('/Steplist/Step') x(XmlCol)  --where not exists('/Steplist/Step/TextReadingId')

So my expected out put is which are all nodes StepIds with no TextreadingID

Step:
E36A3450-1C8F-44DA-B4D0-58E5BFE2A987
11D70A50-08AC-4767-A0D3-87717384FF45

CodePudding user response：

Please try the following solution.

The XPath predicate .nodes('/Steplist/Step[not(TextReadingId)]') does all the magic.

SQL

USE tempdb;
GO

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

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

INSERT INTO dbo.tblStepList ([Data]) VALUES
(N'<Steplist>
  <Step>
    <StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>     
  </Step>
  <Step>
    <StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
    <TextReadingId>12</TextReadingId>
  </Step>
</Steplist>'),
(N'<Steplist>
  <Step>
    <StepId>9960222B-897F-44E9-82FE-F33705D0C2B6</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>   
    <TextReadingId>0</TextReadingId>  
  </Step>
  <Step>
    <StepId>11D70A50-08AC-4767-A0D3-87717384FF45</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
  </Step>
</Steplist>');
-- DDL and sample data population, end

SELECT c.value('(StepId/text())[1]', 'uniqueidentifier') as StepId
FROM tblStepList 
CROSS APPLY Data.nodes('/Steplist/Step[not(TextReadingId)]') AS t(c);  --where not exists('/Steplist/Step/TextReadingId')

Output

 -------------------------------------- 
|                StepId                |
 -------------------------------------- 
| E36A3450-1C8F-44DA-B4D0-58E5BFE2A987 |
| 11D70A50-08AC-4767-A0D3-87717384FF45 |
 --------------------------------------