Clever Geek Handbook

SQL Server XQuery error: number () requires a node or nodeset,

I have the following query in SQL Server 2012:

DECLARE @xml XML

SET @xml =
'<Root>
    <Value>false</Value>
</Root>'

SELECT
    node.value('concat(substring("T", 1, number((./Value/text())[1] = "true")), substring("F", 1, number(not((./Value/text())[1] = "true"))))', 'NVARCHAR(MAX)') AS [ValueTF]
FROM @xml.nodes('/Root') AS input(node)

This is a ternary-style operation getter method, as described here: How do I create an if-then-else (aka trernary operator) expression into an XPath 1.0 expression?

I expect this query to return F

for ValueTF

, but instead it throws the following error message:

Msg 2374, Level 16, State 1, Line 10
XQuery [value ()]: for number ()

a node or set of nodes is required,

Even the simplified XPath number((./Value/text())[1] = "true")

returns the same error. Google search for "A node or a set of nodes is required for number ()" returns no results. The query succeeds and returns F

as expected when executed elsewhere, such as this online XPath tester .

The following query returns false

for Value

as expected, so I know that at least this part of the query is working correctly:

DECLARE @xml XML

SET @xml =
'<Root>
    <Value>false</Value>
</Root>'

SELECT
    node.value('(./Value/text())[1]', 'NVARCHAR(MAX)') AS [Value]
FROM @xml.nodes('/Root') AS input(node)

The W3 spec for the number function seems to indicate what xs:anyAtomicType

can be passed as an argument number

that includes xs:boolean

. So, is there a bug in my code or is it a difference in the SQL Server XQuery implementation?

+3


source to share


2 answers


Like you, I don't know why the SQL Server implementation number()

won't take a boolean argument ... or even a string, apparently! It's pretty weird.

However, if they do support XQuery, they must by definition support XPath 2.0, which means you don't have to use this ugly workaround to concat()

mimic the ternary conditional operator: XPath 2.0 has the real thing!

So instead of

'concat(substring("T", 1, number((./Value/text())[1] = "true")),
       substring("F", 1, number(not((./Value/text())[1] = "true"))))'


you should be able to say

'if ((./Value/text())[1] = "true") then "T" else "F"'

I have not tested this in SQL Server 2012, but it is part of XQuery and documented here so it's worth a try.

+1


source


After a bit more research, I came across the following page describing implementation details number()

in SQL Server 2012: number Function (XQuery) . From this page under "Implementation Limitations":

The number () function only accepts nodes. It does not accept atomic values.



So, it seems that the problem is that the SQL Server implementation number()

will not accept a boolean argument, although it is not clear to me why it was limited this way.

+1


source






More articles:


All Articles