CODEWITHSUNDEEP
sql-server
Ravi Satyadarshi
Ravi Satyadarshi

Reputation: 21

Xml column attributes manipulation in sql server

I have a table named User in my database. In that there is a xml column named XmlText which contains lots of attributes. 

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

What is needed to do is i have to merge all the 'residential_villa_bungalow' values to 'residential_apartment_flat' if 'residential_apartment_flat' exists in the XmlText Column else 'residential_apartment_flat' will be left by default. There are approx 700000 record in the database so keep in mid that what technique can be used among normal update vs cursot.

Fire query with following columns "UserID,XmlText"

Probable logic wud be something like this..

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

Upvotes: 2

Views: 288

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 1

Related Questions