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?
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.)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With