SQL, Find node value in xml variable, if it exists insert additional nodes into xml variable

Question

I've got a Stored Procedure in SQL, where I have the following declaration:

Declare @fields xml

My SP gets passed values from the front end and then gets executed. The values it gets passed looks like this depending on what the user selects from the front end. For the purpose of this example I have included only 3 ID's.

'<F><ID>979</ID><ID>1000</ID><ID>989</ID></F>'

My question is this:

How can I find the node = 1000 and if that is present (exists) then insert (add) to 2 additional nodes,

<ID>992</ID><ID>993</ID>

to my existing '<F><ID>979</ID><ID>1000</ID><ID>989</ID></F>' xml.

If <ID>1000</ID> isn't present do nothing.

So, end result should be something like this if 1000 is present.

<F><ID>979</ID><ID>1000</ID><ID>989</ID><ID>992</ID><ID>993</ID></F>

If not, the result should stay:

<F><ID>979</ID><ID>1000</ID><ID>989</ID></F>

I just can't get my head around this?

GriGrim · Accepted Answer · 2013-09-16 14:02:04Z

2

Check this:

declare @fields xml = '<F><ID>979</ID><ID>1000</ID><ID>989</ID></F>'
    , @add xml = '<ID>992</ID><ID>993</ID>'
;
if @fields.exist('/F[1]/ID[text()="1000"]') = 1
    set @fields.modify('insert sql:variable("@add") as last into /F[1]');

select @fields

answered Sep 16, 2013 at 14:02

GriGrim

2,9411 gold badge25 silver badges38 bronze badges

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

3 Comments

frans Over a year ago

This works like a charm @GriGrim. The add variable is added to the end of my fields xml. This saves me a lot of effort and trouble in redoing a large portion of my SP. Thank you.

Si8 Over a year ago

I tried to use variable in my Insert statement and I got an error.

Si8 Over a year ago

stackoverflow.com/questions/32253235/…

Collectives™ on Stack Overflow

SQL, Find node value in xml variable, if it exists insert additional nodes into xml variable

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related