SQL Server 2005: XQuery snippets

Suppose we have an XML snippet:

<root>
  <tag1>val1</tag1>
  <tag2>val2</tag2>
</root>

1. To get all pairs
tag1 => val1
tag2 => val2
etc.

DECLARE @x XML;
SET @x = '<root><tag1>...</root>';
SELECT
  CAST(C.query('local-name(.)') AS NVARCHAR(4000)) AS [Tag],
  CAST(C.value('.', 'nvarchar(4000)') AS NVARCHAR(4000)) AS [Val]
FROM @x.nodes('/root/*') T(C);

2. To insert a new pair where value is to be taken from a local variable:

DECLARE @val NVARCHAR(4000);
SET @val = 'val0';
SET @x.modify('insert <tag0>{sql:variable("@val")}</tag0> as first into(/root)[1]');

Note how sql:variable() function was taken into curly braces, otherwise it would have inserted ‘sql:variable(“@val”)’. Also the modify() argument must be a string literal, i.e. the following construction does not work: ‘insert ‘ + @val + ‘ as first into(/root)[1]‘

3. To update a value:

DECLARE @val NVARCHAR(4000);
SET @val = 'val000';
SET @x.modify('replace value of (/root/tag0/text())[1] with sql:variable("@val")');

No curly braces but still have to use sql:variable() function.

Advertisement