日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

本文介紹了僅當存在XQuery SQL SELECT節點時的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！

問題描述

我使用的是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節點時的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，