How to define data string from xml in Xquery

Question

I have an untyped XML column in a Sql Server Database that contains values ​​like

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

I tried to execute the following query

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

But here the problem exists, the function returns 1, even if @x='<root><a>12</a></root>'

I would like the output to be "0" in such cases.

Is there a way out?

Answer 1

The .exist () method returns 1 if the XQuery expression evaluates to a nonzero list of nodes, otherwise it returns 0. Thus, it will return 1 every time in your example.

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

:

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

0

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

1

Answer 2

XQuery?

. instance of xs:integer

true, , false.