XQuerysqlJoin在join不匹配時插入空節點

我正在使用 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，如果聯接不匹配，我不希望更新陳述句接觸資料。

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

uj5u.com熱心網友回復：

請嘗試以下解決方案。

我添加了幾個 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;

標籤：sql-server xml 查询语句 查询 xquery-sql

上一篇：決議命令列時出錯：無法識別的選項“--wiredTigerCacheSizeGB2”

下一篇：為什么@@rowcount在完全隔離運行時等于1？