CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
frans
frans

Reputation: 181

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

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?

Upvotes: 0

Views: 3455

Answers (1)

GriGrim
GriGrim

Reputation: 2921

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

Upvotes: 2

Related Questions