使用 SQL 在 XML 中插入节点

我有以下 XML:

创建表#temp(cid int,xml_data xml)插入 cid值(1001，'<主><name>''John doe''</name><年龄>15</年龄></主要>')

我想根据一个简单的参数条件向这个 XML 添加一个额外的节点:

所需的输出:

<name>John doe</name><type>Q</type><年龄>15</年龄></主要>

代码:

select case when @type = 'Q' then更新#tempSET Main.modify('insert  into(/主要的)')走

我收到语法错误.任何帮助！

更新:

我在代码中实施了建议的解决方案，但出现以下错误.错过了一些愚蠢的东西！

 更新 #temp设置 xml_data =案件当@type = 'G'然后 xml_data.modify('insert G into (/Main)[1]');当@type = 'Q'然后 xml_data.modify('insert Q into (/Main)[1]');结尾

我收到'XML 数据类型方法'修改'的错误使用.在这种情况下需要一个非突变方法.错误

解决方案

不需要任何复杂的麻烦.只需根据需要插入所需的节点:

UPDATE #temp SET xml_data.modify('insert Q into (/Main)[1]');

使用 as first、as last 或 before/after 允许您指定节点的位置.下面将新节点直接放在 之后:

UPDATE #temp SET xml_data.modify('insert Q after (/Main/name)[1]');

更新您关于更新语句的问题

你的陈述有几个缺陷:

<块引用>

更新#temp设置 xml_data =案件当@type = 'G'然后 xml_data.modify('insert G into (/Main)[1]');当@type = 'Q'然后 xml_data.modify('insert Q into (/Main)[1]');结尾

您不能使用语法 SET xmlColumn = xmlColumn.modify().您必须使用 SET xmlColumn.modify()，而且分号无论如何都会破坏这一点.

老实说，我觉得这很复杂，试试这个:

DECLARE @type VARCHAR(1)='Q'UPDATE #temp SET xml_data.modify('insert {sql:variable("@type")} into (/Main)[1]');

这将创建一个新节点 content</type>，其中的内容从变量 @type 中取出.

I have the below XML:

create table #temp(cid int,xml_data xml)
insert into cid
values(1001,
     '<Main>
        <name>''John doe''</name>
        <age>15</age>
    </Main>')

I want to add an additional node to this XML based on a simple parametric condition:

desired output:

<Main>
    <name>John doe</name>
    <type>Q</type>
    <age>15</age>
</Main>

code:

select case when @type = 'Q' then
    UPDATE #temp
    SET Main.modify('insert <type = 'Q'> into 
        (/Main)')
    GO

I am getting syntax error. Any help!

UPDATE:

I implemented the suggested solution in my code and I'm getting below error. Missing out something silly!

 UPDATE #temp
         SET xml_data = 
            case
                when @type = 'G' 
                then xml_data.modify('insert <type>G</type> into (/Main)[1]');
                when @type = 'Q' 
                then xml_data.modify('insert <type>Q</type> into (/Main)[1]'); end

I am getting 'Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.' error

解决方案

No need for any complicated hassel. Just insert the node you want as you want it:

UPDATE #temp SET xml_data.modify('insert <type>Q</type> into (/Main)[1]');

Using as first, as last or before / after allows you to specify the node's position. The following will place the new node directly after <name>:

UPDATE #temp SET xml_data.modify('insert <type>Q</type> after (/Main/name)[1]');

UPDATE Your question about an update-statement

Your statement has several flaws:

UPDATE #temp
     SET xml_data = 
        case
            when @type = 'G' 
            then xml_data.modify('insert <type>G</type> into (/Main)[1]');
            when @type = 'Q' 
            then xml_data.modify('insert <type>Q</type> into (/Main)[1]'); 
         end

You cannot use the syntax SET xmlColumn = xmlColumn.modify(). You have to use SET xmlColumn.modify(), Furthermore the semicolons are breaking this anyway.

To be honest, I think this is to complicated, try this:

DECLARE @type VARCHAR(1)='Q'
UPDATE #temp SET xml_data.modify('insert <type>{sql:variable("@type")}</type> into (/Main)[1]');

This will create a new node <type>content</type>, with a content taken out ot the variable @type.