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

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

問題描述

我正在使用SQL Server 2012，正在尋找一種通過查找另一個表中的值來更新我的XML列的方法。

我有以下架構：

USE tempdb;
GO

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

CREATE TABLE [dbo].[tblstepid](
    [stepid] [uniqueidentifier]  NOT NULL,
    [name] nvarchar(32) NOT NULL
);

INSERT INTO dbo.tblstepid ([stepid], name ) VALUES ('E36A3450-1C8F-44DA-B4D0-58E5BFE2A987','step1')

INSERT INTO dbo.tblstepid ([stepid], name ) VALUES ('11D70A50-08AC-4767-A0D3-87717384FF45','step2')

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>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>
    <TextReadingName>reading1</TextReadingName>
  </Step>
</Steplist>')

現在，我希望使用匹配的步驟ID更新另一個表中的所有步驟，如下所示：

UPDATE sl
SET Data = (
    SELECT v.Step.query('
<Step>{./*,    
            <stepname>{sql:column("sr.name")}</stepname>
        }
</Step>
    ')
    FROM sl.Data.nodes('/Steplist/Step') v(Step)
     left JOIN tblStepID sr ON sr.StepId = v.Step.value('(StepId/text())[1]','uniqueidentifier')
    FOR XML PATH(''), ROOT('Steplist'), TYPE
)
FROM tblStepList sl;

并且我的結果XML輸出有一個空節點，這是我不想要的。
我如何才能編寫連接而不為自己添加空節點，并且在沒有匹配的情況下不設置數據，這意味著我的表XML中的第二條記錄永遠不應該被觸動？

我試圖解決的問題是，我試圖通過從另一個表聯接來更新包含數百萬條記錄的表的XML，并且如果聯接不匹配，我不希望UPDATE語句觸及數據。

<Steplist>
  <Step>
    <StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>
    <stepname>step1</stepname>
  </Step>
  <Step>
    <StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
    <TextReadingId>12</TextReadingId>
    <stepname />
  </Step>
</Steplist>

我的預期輸出如下：

<Steplist>
  <Step>
    <StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>
    <stepname>step1</stepname>
  </Step>
  <Step>
    <StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
    <TextReadingId>12</TextReadingId>
  </Step>
</Steplist>

推薦答案

請嘗試以下解決方案。

我添加了幾個XQueryif/else語句以防止<stepname />標記為空。

SQL

USE tempdb;
GO

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

CREATE TABLE dbo.tblstepid(
    stepid uniqueidentifier  NOT NULL,
    [name] nvarchar(32) NOT NULL
);
INSERT INTO dbo.tblStepID (StepId, [Name]) VALUES
('e36a3450-1c8f-44da-b4d0-58e5bfe2a987', 'step1'),
('4078c1b1-71ea-4578-ba61-d2f6a5126ba1', 'step2');

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>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>
    <TextReadingName>reading1</TextReadingName>
  </Step>
</Steplist>');
-- DDL and sample data population, end

UPDATE sl
SET Data = (
    SELECT v.Step.query('
    <Step>{./*,   
            if (not(./stepname)) then 
                if (not(empty(sql:column("sr.name")))) then <stepname>{sql:column("sr.name")}</stepname>
                else ()
            else ()
        }
    </Step>
')
FROM sl.Data.nodes('/Steplist/Step') AS v(Step)
LEFT JOIN tblStepID sr ON sr.StepId = v.Step.value('(StepId/text())[1]','uniqueidentifier')
FOR XML PATH(''), ROOT('Steplist'), TYPE
)
FROM tblStepList AS sl;

-- test
SELECT * FROM dbo.tblStepList;

這篇關于當連接不匹配時，XQuery SQL連接插入空節點的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，