XQuery: Error converting data type nvarchar to numeric.

SQL Server XML

lcj


I am trying to run a query off of a xml data type using Xquery (nodes() and value() methods) but am running inot a conversion error for a numeric type in the xml which is empty or blank. I want it to come back as null but it looks like it is trying to convert an empty string. Is there any way that an empty tag could pull back a null



Top

Re: XQuery: Error converting data type nvarchar to numeric.

Martin Honnen


Can you show us an example of your XML and of your query Then it is easier to understand what you are trying to achieve and where it fails.






Top

Re: XQuery: Error converting data type nvarchar to numeric.

Kent Waldrop Au07

I am not sure that I understand the problem; however you might be able to use the TSQL NULLIF function to mold the data as you need it. Perhaps something like this:

Code Snippet

declare @xml xml
set @xml =
'<Root>
<Data></Data>
<Data>17</Data>
</Root>'

select cast(nullif(t.value('.', 'varchar(11)'),'') as int)
as targetValue
from @xml.nodes('/Root/Data') as x(t)

/*
targetValue
-----------
NULL
17
*/





Top