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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s