Daily Archives: February 7, 2012

SQL Server: Updating XML fields – XPath, XQuery

Further to my post SQL Server: Querying XML fields – XPath, XQuery, I’d like to expand on this and cover updating XML fields too.

Unfortunately it appears you can only do one update at at a time, so often looping is required, extracting data into temporary tables or complex XPath.
I’m going to cover the later as it’s one that isn’t so well written about.

The great thing about SQL Server is you can use the XPath String Functions, for example I’ve used the concatenation and substring functions before and here is a simple example to show how they can be used. Of note is also the neat way to include SQL variables into the XPath.

-- Create a temporary table for the example, drop if already exists

BEGIN TRY
DROP TABLE #T1
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE #T1
(Val XML)

-- Stick in some XML data

INSERT INTO #T1 (Val) VALUES ('<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">cycling</leaf>
    <leaf name="alternativename" type="String">is sometimes fun</leaf>
  </branch>
</root>')
INSERT INTO #T1 (Val) VALUES ('<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">running</leaf>
    <leaf name="alternativename" type="String">is sometimes fun</leaf>
  </branch>
</root>')

-- Update the cycling row using a SQL variable and some functions (concat and substring)

DECLARE @timvar VARCHAR(MAX)
SELECT @timvar = ' is always '

UPDATE #T1 SET Val.modify('declare namespace TIM="http://tim.xsd";
  replace value of (/TIM:root/TIM:branch/TIM:leaf[@name="alternativename"]/text())[1]
  with fn:concat(
    "Everybody knows that ",
    (/TIM:root/TIM:branch/TIM:leaf[@name="displayname"]/text())[1],
    sql:variable("@timvar"),
    fn:substring((/TIM:root/TIM:branch/TIM:leaf[@name="alternativename"]/text())[1], 14, 3))
')
WHERE Val.query('declare namespace TIM="http://tim.xsd";(/TIM:root/TIM:branch[1]/TIM:leaf[@name="displayname"]/text())[1]')
.value('.', 'NVARCHAR(MAX)') = 'cycling'

-- Check the result

SELECT * FROM #T1

And the result is:

<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">cycling</leaf>
    <leaf name="alternativename" type="String">Everybody knows that cycling is always fun</leaf>
  </branch>
</root>

<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">running</leaf>
    <leaf name="alternativename" type="String">is sometimes fun</leaf>
  </branch>
</root>