我正在使用 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>