在单个查询中将多个节点插入到 xml 字段 [英] Insert multiple nodes to xml field in single query

问题描述

我有一个表(我们称她为 t)，其中包含字段 id(int) 和 XmlField(xml).

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.

查询是:

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

我收到错误:

XQuery [t.XmlField.modify()]:'' 附近的语法错误，预期为 'as'、'into'、'before' 或 'after'.

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

当我尝试仅添加一个 xml 节点时，它正在工作(示例):

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?

推荐答案

SQL Server 文档确实很清楚地说明 insert 语句可以处理多个节点.所以我的猜测是你的问题只是一个语法错误.(Microsoft 语法与 XQuery Update Facility 规范，但很相似.)

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

我会尝试将元素 f1 和 f2 组合成一个序列并将它们括在括号中(规范在这里需要一个 ExprSingle，这意味着不允许使用顶级逗号):

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

(未针对 SQL Server 进行测试.)

(Not tested against SQL Server.)

这篇关于在单个查询中将多个节点插入到 xml 字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！