Writing a new subtree to an XML node in SQL

Question

Is there a way to write a new subtree to an XML node in SQL, specifically for a dynamic query? I know it's possible using some combination of insert and delete statements, but I'm looking for something that works in a fairly implicit way. IE, I don't want to have to write a different query for each specific case that this is needed. So for example, say I have this XML hierarchy:

<root>
    ...
    <node>
        <node1>
            ...
        </node1>
        <node2>
            ...
        </node2>
        ...
    </node>
    ...
</root>

If I want to replace the value of a single node, I have this query:

DECLARE @newVal varchar(max)
DECLARE @XPath varchar(max)
SELECT @newVal = 'newValue'
SELECT @XPath = 'root/node/node1/text()'

DECLARE @query varchar(max)

SET @query = '
    UPDATE  [dbo].[Users]
    SET     [NewSettingsXml].modify(''
        replace value of (' + @XPath + ')[1]
        with "' + @newVal + '"'')'
exec(@query)

I was hoping that I would just be able to do something like @newVal = '<newNode>foo</newNode>', where the new XML would come from a serialized C# object, but, not surprisingly, it encodes the value into text, setting it as <newNode>foo</newNode> instead. Is there a way to do this in a similar fashion?

Scott Hewison · Accepted Answer · 2012-11-26 16:14:33Z

4

You might try converting your @newVal to an nvarchar(max) and then do your mofify as follows:

DECLARE @newVal xml
SELECT @newVal = '<newNode>foo</newNode>'

DECLARE @strNewVal nvarchar(max)
SET @strNewVal = (Select (CONVERT(nvarchar(max), @newVal)))

UPDATE  [dbo].[Users]
SET     [SettingsXml].modify('
    replace value of (root/node/node1/text())[1]
    with sql:variable("@strNewVal")')

answered Nov 26, 2012 at 16:14

Scott Hewison

412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikael Eriksson · Accepted Answer · 2011-06-17 06:44:36Z

I don't think it is possible to replace a value with a node.

This will take the string as a value and of course encode <>

replace value of (root/node/node1/text())[1] with "<newNode>foo</newNode>"

You would probably have to do something like this

replace value of (root/node/node1/text())[1] with <newNode>foo</newNode>

But that gives the error :

The 'with' clause of 'replace value of' cannot contain constructed XML

If you use a xml variable instead:

DECLARE @newVal xml
SELECT @newVal = '<newNode>foo</newNode>'

UPDATE  [dbo].[Users]
SET     [SettingsXml].modify('
    replace value of (root/node/node1/text())[1]
    with sql:variable("@newVal")')

You get the error

An XML instance is only supported as the direct source of an insert using sql:column/sql:variable.

So I guess that you are stuck to use insert when you want to insert nodes.

Raj Jayaswal · Accepted Answer · 2011-06-16 19:09:04Z

0

Try using FOR XML contruct. This might give you the desired result.

answered Jun 16, 2011 at 19:09

Raj Jayaswal

4781 gold badge9 silver badges23 bronze badges

1 Comment

Chris Over a year ago

Unless I'm missing something, FOR XML is specifically for SELECT statements. Since I'm doing a SET, I don't think that would really apply here. Please correct me if I'm wrong, though. :)

Collectives™ on Stack Overflow

Writing a new subtree to an XML node in SQL

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related