FLWOR Power in SQL

Have XML inside your database table? would you like to update it based on certain conditions?

XPath, XQuery , FLWOR is not full compatible in Microsoft SQL server, but others have come along further. Below is an example on MSSQL and will update the First and Last name and obfuscate it for QA environments (to keep production data safe) by concatenating the words "first" and the identity id of the record.

;WITH cte AS(

SELECT *

,( SELECT t.AdditionalInfo .query(N'/Params/Account[(TenantFirstName/text())[1]=sql:column("FirstName") and (TenantLastName/text())[1]=sql:column("LastName")]') .query(N'<Account>

{ for $nd in /Account/* return

if(local-name($nd)="TenantFirstName") then

<TenantFirstName>{concat("first"[1],xs:string(sql:column("se.id")))}</TenantFirstName> else if(local-name($nd)="TenantLastName") then

<TenantLastName>{concat("last"[1],xs:string(sql:column("se.id")))}</TenantLastName> else

$nd

}

</Account> ') AS [*] FROM #TempData AS t

INNER JOIN #SEAAccountExtended AS se ON t.RefID=se.SEAccountRefID

where t.id = ilv.id and t.AdditionalInfo.exist('/Params/Account/TenantFirstName/text()') = 1 ORDER BY se.id

FOR XML PATH(''),ROOT('Params'),TYPE ) AS NewAdditionalInfo FROM #TempData AS ilv

)

update cte set AdditionalInfo = NewAdditionalInfo where not cte.NewAdditionalInfo is null