How to detect a string of data from xml in Xquery

Question

I have an untyped XML column in Sql Server Database which holds values such as

 1
 <root><a>123</a></root>
 <root>23d</root>
 23

I was trying with the following query

declare @x xml
set @x='1'
select @x.exist('xs:int(.)')

But here the problem is exist function will return 1 even if @x='<root><a>12</a></root>'

I would like the output to '0' in such cases.

Is there a way out?

Answer 1

The .exist() method returns 1 if the XQuery expression evaluates to non-null node list, otherwise it returns 0. So it'll return 1 every time in your example.

If I understand you correctly, you want it to return 1 when the value is just an integer, and 0 when it's XML?

In that case you need to do the following:

declare @x xml
set @x='<root><a>12</a></root>'
select ISNUMERIC(CAST(@x.query('/') AS nvarchar))

This should return 0

declare @x xml
set @x='12'
select ISNUMERIC(CAST(@x.query('/') AS nvarchar))

This should return 1

Answer 2

What about the built-in type test in XQuery?

. instance of xs:integer

Will give you a boolean true if the context item is an integer, false otherwise.