sql server中的Xml列属性操作

Question

我的数据库中有一个名为User的表。其中有一个名为XmlText的xml列，其中包含许多属性。

  <userDetails>
  <MobileVerified>True</MobileVerified>
  <EmailVerified>True</EmailVerified>
  <IPAddress>122.160.65.232</IPAddress>
  <Gender>Male</Gender>
  <DateOfBirth>1970-03-22T00:00:00</DateOfBirth>
  <DealingInProperties>residential_apartment_flat</DealingInProperties>
  <DealingInProperties>residential_villa_bungalow</DealingInProperties>
  <DealingInProperties>residential_farm_house</DealingInProperties>
</userDetails>

需要做的是，如果XmlText列中存在'residential_apartment_flat‘，则必须将所有'residential_villa_bungalow’值合并到'residential_apartment_flat‘，否则默认情况下将保留'residential_apartment_flat’。在数据库中大约有700000条记录，所以在正常更新和当前更新之间可以使用什么技术。

使用以下列"UserID，XmlText“触发查询

可能的逻辑应该是这样的..

if ('residential_villa_bungalow') exists
(
if ('residential_apartment_flat') exists
    remove the 'residential_villa_bungalow' node as there must be only one 'residential_apartment_flat' node
else
    update 'residential_villa_bungalow' into 'residential_apartment_flat'
)

Answer 1

XML Data Modification Language (XML DML)

-- Delete bungalow where already exist a flat
update YourTable
set    XMLText.modify('delete /userDetails/DealingInProperties[. = "residential_villa_bungalow"] ')
where  XMLText.exist('/userDetails[DealingInProperties = "residential_apartment_flat"]') = 1 and
       XMLText.exist('/userDetails[DealingInProperties = "residential_villa_bungalow"]') = 1

-- Change value from bungalow to flat
update YourTable 
set    XMLText.modify('replace value of (/userDetails/DealingInProperties[. = "residential_villa_bungalow"]/text())[1] 
                       with "residential_apartment_flat"')
where  XMLText.exist('/userDetails[DealingInProperties = "residential_villa_bungalow"]') = 1