Symptoms

Modifying XML in SQL table
 

Diagnoses

I am working on making a copy of our Production environment and I want to bring up the instance without any values pointing to Production. I need to change the Prod values in the K2SmartBroker databases in the ServiceInstance table. I would make the change in the SmartObjectServiceTester but that is only accessible if K2 is started but I can't start K2 with Prod values. How do I modify this XML?

I tried using the following SQL statement and it said 1 row affected but I looked at the XML and it did not change.

USE k2smartbroker
UPDATE ServiceInstance
SET ServiceInstanceXML.modify('replace value of
(/serviceinstancec@NAME=("LLBLService")]/serviceconfig/settings/keye@NAME=("DatabaseConnectionString")]/text()))1] with (" data source=ashvrgdb09pinitial catalog=VsuiteSandboxuid=vsurveydbpwd=redacted")')
WHERE ServiceInstanceGUID = '54DBF854-6CD2-4387-84B3-8420E3D51915'
GO
 

Resolution

Customer was able to update the XML in the database successfully using lowercase '@name'. If you wish to do so, please also perform a database backup.