当连接不匹配时，XQuery SQL连接插入空节点 [英] XQuery sql Join inserts empty node when join does not match

问题描述

我正在使用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连接插入空节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！