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?
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
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.
