Single node required

Article
06/14/2005

This is one of the most common static typing errors you’re likely to encounter. Somewhere in your XQuery a single node is expected but the static type of the expression you wrote indicates that it can possibly return multiple nodes.

For example, let’s create the following collection

CREATE XML SCHEMA COLLECTION MyCollection AS '
<schema xmlns="https://www.w3.org/2001/XMLSchema" targetNamespace="https://myNS" xmlns:ns="https://myNS">

</schema>'
go

Now let’s create an XML variable typed according to this schema, assign it a value, and attempt to run a DML operation.

DECLARE @var XML(MyCollection)
SET @var = '<x:root xmlns:x="https://myNS">
<a>Data</a>
123
</x:root>'
SET @var.modify('declare namespace ns="https://myNS"; insert 4 as last into /ns:root ')
go

The XQuery will fail with the following error

XQuery [modify()]: The target of 'insert' must be a single node, found 'element(ns{https://myNS}:root,ns{https://myNS}:myType) *'

The destination for the ‘insert’ operation needs to be a single node. Since the variable could very well contain multiple instances of the ‘root’ element, the XPath expression /ns:root could potentially return more than one node. The static type of that expression is element(ns{https://myNS}:root,ns{https://myNS}:myType) *.
We need to modify the XPath expression in order to make sure that it will return no more than one node. We can do this by adding a position predicate to our XPath expression, turning it into /root[1].
The static type of that expression is element(ns{https://myNS}:root,ns{https://myNS}:myType) ?. The ‘?’ occurrence indicator means “0 or 1”. We now have an expression that will return a singleton or an empty sequence (in which case nothing will happen).

Let’s look at a similar example with the ‘replace’ operation. The following query will be successful

Since there has to be only one element ‘a’ under ‘root’, the static type of the path expression is element(a,xs:string) ?. This path expression will yield at the most one element named ‘a’ of type xs:string. The singleton requirement is met.

On the other hand, if we replaced the query with the following one, it would fail.

SET @var.modify('declare namespace ns="https://myNS"; replace value of /ns:root[1]/b with xs:byte(2)')

The server returns the following error message. XQuery [modify()]: The target of 'replace' must be at most one node, found 'element(b,xs:byte) *.

Since there are between 3 and 7 elements ‘b’ under an element ‘root’, the singleton requirement is not met.
There are a few different ways to fix the query. If it’s the first of the ‘b’ elements we want to update, we can replace path expression /ns:root[1]/b with /ns:root[1]/b[1] or with (/ns:root/b)[1].

In general “single node expected” errors can be resolved by using position predicates. However, you must be aware of the limitations of the server.

During the static typing phase, the presence of an integer literal or of a call to the last() function in a predicate is seen as a guarantee that no more than one node will be returned. For example, the static type of /ns:root[1]/b[last()] is also element(b,xs:byte) ?.

However, analysis of the predicates doesn’t go any further. For example, path expression (/ns:root/b)[position() = 1] is equivalent to (/ns:root/b)[1] but their static types in SQL Server 2005 are different. The former yields element(b,xs:byte) * and the later element(b,xs:byte) ?. This is because SQL Server 2005 does not statically analyze the expression in the predicate. It only makes a distinction between integer literals or last() and all other expressions.

Similarly, the values of integer literals are not examined during static typing. For example, path expression /ns:root[1]/b[10] also yields static type element(b,xs:byte) ?. Looking at the schema, we can tell that this path expression will never return any node, because there can be at the most 7 ‘b’ elements under a ‘root’ element. Static analysis however does not consider the value inside the predicate. It only cares that it is a literal.

In conclusion, the best way to fix those errors is to use positions predicates appropriately. If the server’s static analysis of a path expression doesn’t satisfy the singleton requirement, but if you know for a fact that the result is (at most) a single node, you can always apply the [1] predicate to the entire expression.
For example, because it is unable to analyze the contents of the predicate, the server will determine the static type of /ns:root[1]/b[position() = 2 and data(.) = 0] to be element(b,xs:byte) *.
Change the path expression to (/ns:root[1]/b[position() = 2 and data(.) = 0])[1] and the static type will now be element(b,xs:byte) ? which satisfies the singleton requirement.

As always, feedback, questions and comments are appreciated. In the next installment we’ll start looking at the (sometimes not so) subtle ways schema features may influence static typing.
-
Disclaimer:
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.

Single node required

Additional resources