Writing a new subtree to an XML node in SQL

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?

---

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")')

---

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.

---

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