1

I want to update xml like this one

<?xml version="1.0" encoding="utf-16"?>
<Properties xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <BackupPath>MyLocalPath</BackupPath>
</Properties>

using stored procedure

declare @PropertyName       nvarchar(500)
declare @PropertyValue      nvarchar(MAX)

set @PropertyName = 'BackupPath'
set @PropertyValue = 'MyTestPath'

DECLARE @ExistingSettings XML;
DECLARE @NewSettings nvarchar(MAX);

SET @ExistingSettings = (SELECT TOP(1) CAST(SettingsDefinition  AS XML) FROM ApplicationSettings)

SET @ExistingSettings.modify('replace value of (/Properties/*[local-name() = sql:variable("@PropertyName")]/text())[1] with sql:variable("@PropertyValue")')

UPDATE ApplicationSettings
SET SettingsDefinition = CAST(@ExistingSettings AS nvarchar(MAX)), LastUpdate = GetDate()
WHERE ID = (SELECT TOP(1) ID FROM ApplicationSettings)

and this works fine as long as 'BackupPath' contains value 

<BackupPath>MyLocalPath</BackupPath>

How to modify this query to insert value (or validate if needed) if 

<BackupPath />
davorn
  • 423
  • 2
  • 5
  • 7

1 Answers1

0

Just don't require a text node in your XPath.

SET @ExistingSettings.modify(
  'replace value of (/Properties/' + @PropertyName + ')[1] with sql:variable("@PropertyValue")'
)
Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • I'm getting an error message now. The argument 1 of the XML data type method "modify" must be a string literal. – davorn Nov 08 '14 at 11:15
  • By any chance, does this approach work? http://stackoverflow.com/a/21457475/18771 – Tomalak Nov 08 '14 at 11:40