下面是我的表字段中的XML
<CtcConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Ctc>3</Ctc>
<SalaryComponent>
<SalaryComponentConfiguration>
<Name>Basic</Name>
<DisplayOrder>0</DisplayOrder>
<Value>5634655</Value>
</SalaryComponentConfiguration>
<SalaryComponentConfiguration>
<Name>HR</Name>
<DisplayOrder>0</DisplayOrder>
<Value>1234</Value>
</SalaryComponentConfiguration>
<SalaryComponentConfiguration>
<Name>medical</Name>
<DisplayOrder>0</DisplayOrder>
<Value>0</Value>
</SalaryComponentConfiguration>
</SalaryComponent>
</CtcConfiguration>
我希望根据node(Name)
更新node(DisplayOrder)
的值。例如,如果我将name设置为medical,它应该根据需要更新displayorder的值。
这就是我到目前为止所尝试的:
UPDATE payroll.pays set
CtcConfiguration.modify('replace value of (/CtcConfiguration/SalaryComponent/SalaryComponentConfiguration/DisplayOrder/text())[1] with ("99999")')
where
CtcConfiguration.value('((/CtcConfiguration/SalaryComponent/SalaryComponentConfiguration/Name)[]/text())[1]','varchar(50)') = 'HR'
发布于 2018-06-04 22:04:29
像这样试一下:
提示:此示例操作<Value>
,但它也以相同的方式工作于<DisplayOrder>
。
DECLARE @mockupTable TABLE(ID INT IDENTITY, YourXML XML);
INSERT INTO @mockupTable VALUES
(N'<CtcConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Ctc>3</Ctc>
<SalaryComponent>
<SalaryComponentConfiguration>
<Name>Basic</Name>
<DisplayOrder>0</DisplayOrder>
<Value>5634655</Value>
</SalaryComponentConfiguration>
<SalaryComponentConfiguration>
<Name>HR</Name>
<DisplayOrder>0</DisplayOrder>
<Value>1234</Value>
</SalaryComponentConfiguration>
<SalaryComponentConfiguration>
<Name>medical</Name>
<DisplayOrder>0</DisplayOrder>
<Value>0</Value>
</SalaryComponentConfiguration>
</SalaryComponent>
</CtcConfiguration>');
DECLARE @AttributeName VARCHAR(100)=N'medical';
DECLARE @NewValue INT=12345;
UPDATE @mockupTable
SET YourXML.modify(N'replace value of (/CtcConfiguration
/SalaryComponent
/SalaryComponentConfiguration[(Name/text())[1]=sql:variable("@AttributeName")]
/Value/text())[1]
with sql:variable("@NewValue")');
SELECT * FROM @mockupTable;
简短的解释
您的XML是一个相当简单的属性-值模型(具有可见的等级)。您可以使用XQuery
谓词来过滤正确的属性并设置这个特殊的<Value>
。因此,您必须查找<Name>
包含特殊内容的<SalaryComponentConfiguration>
。
https://stackoverflow.com/questions/50682031
复制相似问题