xml XQuery [value()]: 'value()' 需要一个单例（或空序列），找到类型为 'xdt:untypedAtomic *' 的操作数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19940566/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
提问by birdus
I'm trying to insert rows into a table using a select from XML. I think I'm close. Where am I going wrong?
我正在尝试使用从 XML 中选择将行插入到表中。我想我很接近了。我哪里错了？
declare @xmldata xml;
set @xmldata = '<Database>
<PurchaseDetails>
<PurchaseDetail>
<Upc>72594206916</Upc>
<Quantity>77</Quantity>
<PurchaseDate>9/2010</PurchaseDate>
<PurchaseCity>Dallas</PurchaseCity>
<PurchaseState>TX</PurchaseState>
</PurchaseDetail>
<PurchaseDetail>
<Upc>72594221854</Upc>
<Quantity>33</Quantity>
<PurchaseDate>12/2013</PurchaseDate>
<PurchaseCity>Nashville</PurchaseCity>
<PurchaseState>TN</PurchaseState>
</PurchaseDetail>
</PurchaseDetails>
</Database>'
insert into PurchaseDetails
(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
select
x.Rec.value('Upc','char(11)'),
x.Rec.value('Quantity','int'),
x.Rec.value('PurchaseDate','varchar(7)'),
x.Rec.value('PurchaseCity','varchar(50)'),
x.Rec.value('PurchaseState','char(2)')
from @xmlData.nodes('//Database/PurchaseDetails/PurchaseDetail') as x(Rec)
回答by birdus
A co-worker had tackled a similar problem before. Here is what we came up with. NOT intuitive!
一位同事之前曾解决过类似的问题。这是我们想出的。不直观！
insert into PurchaseDetails
(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
select
pd.value('Upc[1]','char(11)'),
pd.value('Quantity[1]','int'),
pd.value('PurchaseDate[1]','varchar(7)'),
pd.value('PurchaseCity[1]','varchar(50)'),
pd.value('PurchaseState[1]','char(2)')
from @xmlData.nodes('//Database/PurchaseDetails') as x(Rec)
cross apply @xmlData.nodes('//Database/PurchaseDetails/PurchaseDetail') as i(pd)
回答by marion-jeff
Try this!
query() then value()
run this in SQL Server and 100% worked
put a dot (.) first then the child tag.
PurchaseDetail tag exists 2 times so the dot (.) replaces the first and the second tag.
The dot can prevent using of [1] on XQuery.
The dot represents the first and the second PurchaseDetail tags.
尝试这个！
query() 然后 value()
在 SQL Server 中运行它并且 100% 工作首先
放置一个点 (.) 然后是子标记。
PurchaseDetail 标记存在 2 次，因此点 (.) 替换了第一个和第二个标记。
该点可以防止在 XQuery 上使用 [1]。
点代表第一个和第二个 PurchaseDetail 标签。
INSERT INTO PurchaseDetails(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
SELECT col.query('./Upc').value('.', 'char(11)'),
col.query('./Quantity').value('.', 'int'),
col.query('./PurchaseDate').value('.', 'varchar(7)'),
col.query('./PurchaseCity').value('.', 'varchar(50)'),
col.query('./PurchaseState').value('.', 'char(2)')
FROM @xmlData.nodes('/Database/PurchaseDetails/PurchaseDetail') as ref(col)
It is more simplified query so far.
See if it works
到目前为止，它是更简化的查询。
看看它是否有效
回答by Mikael Eriksson
insert into PurchaseDetails(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
select T.X.value('(Upc/text())[1]', 'char(11)'),
T.X.value('(Quantity/text())[1]', 'int'),
T.X.value('(PurchaseDate/text())[1]', 'varchar(7)'),
T.X.value('(PurchaseCity/text())[1]', 'varchar(50)'),
T.X.value('(PurchaseState/text())[1]', 'char(2)')
from @xmlData.nodes('/Database/PurchaseDetails/PurchaseDetail') as T(X)
回答by Rand.Function
select
x.Rec.query('./Upc').value('.','char(11)')
,x.Rec.query('./Quantity').value('.','int')
,x.Rec.query('./PurchaseDate').value('.','varchar(7)')
,x.Rec.query('./PurchaseCity').value('.','varchar(50)')
,x.Rec.query('./PurchaseState').value('.','char(2)')
from @xmlData.nodes('/Database/PurchaseDetails/PurchaseDetail') as x(Rec)
回答by pbz
Struggling with a similar problem, and found that @birdus's answer didn't work if you have additional layers of nesting in your xml that you were referencing in your XQuery, e.g. supposing a slightly different XML shape, if you had
正在努力解决类似的问题，并发现如果您在 XQuery 中引用的 xml 中有额外的嵌套层，则@birdus 的答案不起作用，例如，假设一个稍微不同的 XML 形状，如果您有
T.x.value('PurchasePlace/PurchaseCity[1]','varchar(50)')
you would still get the singleton error. Though @birdus's solution does work for this specific case a more generally applicable solution that combines the best of @birdus's & @Mikael-Eriksson's solution is to do:
你仍然会得到单身错误。尽管@birdus 的解决方案确实适用于这种特定情况，但结合了@birdus 和@Mikael-Eriksson 的最佳解决方案的更普遍适用的解决方案是：
insert into PurchaseDetails(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
select T.X.value('(Upc)[1]', 'char(11)'),
T.X.value('(Quantity)[1]', 'int'),
T.X.value('(PurchaseDate)[1]', 'varchar(7)'),
T.X.value('(PurchaseCity)[1]', 'varchar(50)'),
T.X.value('(PurchaseState)[1]', 'char(2)')
from @xmlData.nodes('/Database/PurchaseDetails/PurchaseDetail') as T(X)
This combine's @birdus's omission of /text(), which is superfluous, but adds @Mikael-Eriksson's parentheses around the element selector, to allow multiple element selectors as in my modified example which becomes:
这结合了@birdus 的省略/text()，这是多余的，但在元素选择器周围添加了@Mikael-Eriksson 的括号，以允许多个元素选择器，如我修改后的示例所示：
T.x.value('(PurchasePlace/PurchaseCity)[1]','varchar(50)')
The reasonfor this, that a few have asked about, is not that @birdus's version returns something other than a singleton in any of the examples discussed here, but that it might. Per Microsoft Docs:
这样做的原因，一些人已经问过，不是@birdus 的版本在此处讨论的任何示例中返回的不是单例，而是它可能. 根据Microsoft Docs：
Location steps, function parameters, and operators that require singletons will return an error if the compiler cannot determine whether a singleton is guaranteed at run time.
如果编译器无法确定在运行时是否保证单例，则需要单例的位置步骤、函数参数和运算符将返回错误。
回答by WutDuk
To address the question of whythe need for the positional predicate (i.e., [1]) within the XQuery string literal, like @pbz indicated, a singleton is required and therefore must be guaranteed. To add more substance to @pbz's answer, see below.
为了解决为什么在[1]XQuery 字符串文字中需要位置谓词（即）的问题，就像@pbz 指出的那样，需要一个单例，因此必须保证。要为@pbz 的答案添加更多内容，请参见下文。
Per Microsoft's SQL Docs:
根据 Microsoft 的SQL 文档：
In the following example, an XML instance is stored in a variable of xml type. The value() method retrieves the ProductID attribute value from the XML. The value is then assigned to an int variable.
DECLARE @myDoc xml DECLARE @ProdID int SET @myDoc = '<Root> <ProductDescription ProductID="1" ProductName="Road Bike"> <Features> <Warranty>1 year parts and labor</Warranty> <Maintenance>3 year parts and labor extended maintenance is available</Maintenance> </Features> </ProductDescription> </Root>' SET @ProdID = @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' ) SELECT @ProdIDValue 1 is returned as a result.
Although there is only one ProductID attribute in the XML instance, the static typing rules require you to explicitly specify that the path expression returns a singleton. Therefore, the additional
[1]is specified at the end of the path expression.For more information about static typing, see XQuery and Static Typing.
在以下示例中，XML 实例存储在 xml 类型的变量中。value() 方法从 XML 中检索 ProductID 属性值。然后将该值分配给一个 int 变量。
DECLARE @myDoc xml DECLARE @ProdID int SET @myDoc = '<Root> <ProductDescription ProductID="1" ProductName="Road Bike"> <Features> <Warranty>1 year parts and labor</Warranty> <Maintenance>3 year parts and labor extended maintenance is available</Maintenance> </Features> </ProductDescription> </Root>' SET @ProdID = @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' ) SELECT @ProdID结果返回值 1。
尽管 XML 实例中只有一个 ProductID 属性，但静态类型规则要求您明确指定路径表达式返回单例。因此，在
[1]路径表达式的末尾指定了附加项。有关静态类型的更多信息，请参阅XQuery 和静态类型。
Following that link then leads us to:
按照该链接，我们将进入：
As mentioned earlier, type inference frequently infers a type that is broader than what the user knowsabout the type of the data that is being passed. In these cases, the user has to rewrite the query.Some typical cases include the following:
...
- The type infers a higher cardinalitythan what the data actually contains. This occurs frequently, because the xml data type can
contain more than one top-level element, and an XML schema collection cannot constrain this. In order to reduce the static type and
guarantee that there is indeed at most one valuebeing passed, you
should use the positional predicate[1]. For example, to add 1 to the value of the attribute c of the element b under the top-level a
element, you must write(/a/b/@c)[1]+1. Additionally, the DOCUMENT
keyword can be used together with an XML schema collection.
如前所述，类型推断通常推断出的类型比用户对所传递数据类型的了解范围更广。在这些情况下，用户必须重写查询。一些典型案例包括：
...
- 该类型推断出比数据实际包含的基数更高的基数。这种情况经常发生，因为xml 数据类型可以
包含多个顶级元素，而 XML 架构集合无法限制这一点。为了减少静态类型并
保证确实最多传递一个值，您
应该使用位置谓词[1]。比如顶层a
元素下的元素b的属性c的值要加1 ，必须写(/a/b/@c)[1]+1. 此外，DOCUMENT
关键字可以与 XML 模式集合一起使用。
