Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

SQL: XML DML

Modified: 2009/09/11 17:20 by jim - Categorized as: SQL Server, XML
Some basic ways to manipulate XML in SQL server. Some things can be done with less lines, but I like to see what is happening

Determine whether key exists (quick and dirty)
DECLARE @xml XML
SET @xml = '<root></root>'

DECLARE @keyvalue NVARCHAR(max)
SET @keyvalue = (SELECT CONVERT(NVARCHAR(max), @xml.value('(/root/insertedkey)[1]', 'INT')))
–– returns NULL if insertedkey does not exist

IF (@keyvalue IS NULL)
BEGIN
    SET @xml.modify('insert <insertedkey>insertedkeyvalue</insertedkey> into (/root)[1]')

END

Get a value from a key and reuse it in a new key

SET @xml = '<root><usethisvalue>1234</usethisvalue></root>'
–– we know how to establish whether the key exists
DECLARE @valuetobeinserted NVARCHAR(max)
SET @valuetobeinserted = (SELECT CONVERT(NVARCHAR(max), @xml.value('(/root/usethisvalue)[1]', 'INT')))

DECLARE @keytobeinserted XML
SET @keytobeinserted = '<keytobeinserted>' + @valuetobeinserted + '</keytobeinserted>'

SET @xml.modify('insert sql:variable("@keytobeinserted") as last into (/root)[1]')
–– use "as first into" to enter as first key
–– use "after (/root/usethisvalue)" to state after which key insert should happen

Replace a key value

SET @xml.modify('
  replace value of (/root/usethisvalue/text())[1]
  with     "new text"
')

Deleting things

– delete text node 
SET @xml.modify('delete /root/usethisvalue/text()')
SELECT @xml
–– delete element
SET @xml.modify('delete /root/usethisvalue')
SELECT @xml

Be sure to check back for new articles

 © Evident Interactive BV