Insert multiple nodes to xml field in single query

Question

I'm having a table (let's call her t) that contains the fields id(int) and XmlField(xml).

I try to add multiple node in one query but no matter what I tried I keep getting errors.

The query is:

update t 
set XmlField.modify('insert <f1>value here</f1><f2>value there</f2> into (/xmldoc)') 

and I getting the error:

XQuery [t.XmlField.modify()]: Syntax error near '', expected 'as', 'into', 'before' or 'after'.

When I trying to add only one xml node it's working (example):

update t set XmlField.modify('insert <f1>value here</f1> into (/xmldoc)') 

it's also working when I try to add nested nodes like this:

update t set XmlField.modify('insert <f><f1>value here</f1><f2>value there</f2></f> into (/xmldoc)') 

Is there any way to make it happen?

Answer 1

The SQL Server documentation does say pretty clearly that the insert statement can handle multiple nodes. So my guess is that your problem is just a syntax error. (The Microsoft syntax varies slightly from that defined in the XQuery Update Facility spec, but it's recognizably similar.)

I'd try making the elements f1 and f2 into a sequence and wrapping them in parentheses (the spec requires an ExprSingle here, which means no top-level commas are allowed):

update t 
set XmlField.modify(
  'insert (<f1>value here</f1>, <f2>value there</f2>) into (/xmldoc)') 

(Not tested against SQL Server.)