仅当存在XQuery SQL SELECT节点时 [英] XQuery sql select node only if exists

问题描述

我使用的是SQL Server 2012
我正在寻找一种从我的表中选择一些具有XML数据类型的节点值的方法，如下所示。

我有以下架构

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>')

现在我想使用一条SELECT语句来获取没有TextReadingId节点的所有步骤，这是我尝试过的，但我无法在节点中执行eXist

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')

所以我的预期输出是哪些是没有TextreadingID的所有节点StepID

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

推荐答案

请尝试以下解决方案。

XPath谓词.nodes('/Steplist/Step[not(TextReadingId)]')实现了所有的魔力。

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')

输出

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

这篇关于仅当存在XQuery SQL SELECT节点时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！