Insert multiple nodes to xml field in single query

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.

  • ORDER BY enum integer field SQL
  • SQL Server making rows into columns
  • SQL Server: Select multiple records in one select statement
  • Securing access to SQL Server data
  • t sql “select case” vs “if … else” and explaination about “begin”
  • The text, ntext, and image data > types cannot be compared or sorted, except when using IS NULL or LIKE > operator
  • 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?

    One Solution collect form web for “Insert multiple nodes to xml field in single query”

    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.)

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.