views:

38

answers:

2

I have a Table with an XML column, I want to update the xml to insert attribute or to change the attribute value if the attribute already exists.

Let's say the starting xml is: < d />

Inserting:

UPDATE Table 
set XmlCol.modify('insert attribute att {"1"} into /d[1]')

Changing:

UPDATE Table
set XmlCol.modify('replace value of /d[1]/@att with "1"')

insert will fail if the attribute already exists, replace will fail if the attribute doesn't exists. I have tried to use 'if' but I don't think it can work, there error I get: "XQuery [modify()]: Syntax error near 'attribute', expected 'else'."

IF attempt

UPDATE Table 
set XmlCol.modify('if empty(/d[1]/@att) 
                   then insert attribute att {"1"} into /d[1]
                   else replace value of /d[1]/@att with "1"')

Currently I select the xml into a variable and then modify it using T-SQL and then updating the column with new xml, this requires me to lock the row in a transaction and is probably more expensive for the DB.

A: 

This XQuery:

declare namespace local = "http://example.org"; 
declare function local:copy-replace($element as element()) { 
  element {node-name($element)} 
    {$element/@*[not(self::att)],
     if ($element/self::d)
     then attribute {"att"} {1}
     else $element/@att, 
     for $child in $element/node() 
        return if ($child instance of element()) 
               then local:copy-replace($child) 
               else $child 
    } 
}; 
local:copy-replace(/*)

With this input:

<d />

Output:

<d att="1"/>
Alejandro
A: 

I didn't found a way to declare xquary functions in SQL Server, I don't think it is possible.

I must admit that I don't understant the query to try to modify it to remove the function declaration.

hans