Inserting node in XML using SQL

Question

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

The syntax error is pretty obvious: Your insert <type = 'Q'> trys to insert a not well-formed node... Btw: Do you really want to wrap "John doe" in quotes? — Gottfried Lesigang
– Gottfried Lesigang, Commented Jun 20, 2018 at 8:07

Gottfried Lesigang · Accepted Answer · 2018-06-20 15:23:21Z

4

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.

edited Jun 20, 2018 at 15:23

answered Jun 20, 2018 at 8:05

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

1 Comment

Rick Over a year ago

this approach is looking simple. But I am missing out something silly that I'm getting an error. Updating the question with my new error and code trial

Ian-Fogelman · Accepted Answer · 2018-06-19 22:24:14Z

2

I was able to achieve this by breaking the update into two statements:

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


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

    declare @variable xml = 'q'
    UPDATE #temp
    set xml_data.modify('insert sql:variable("@variable") as last into (/Main/type)[1]')
    GO

    select * from #temp

answered Jun 19, 2018 at 22:24

Ian-Fogelman

1,6151 gold badge11 silver badges17 bronze badges

1 Comment

Gottfried Lesigang Over a year ago

No need for this approach. You can insert the new node in one go easily. See my answer...

Collectives™ on Stack Overflow

Inserting node in XML using SQL

2 Answers 2

UPDATE Your question about an update-statement

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

UPDATE Your question about an update-statement

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related