XQuery: Error converting data type nvarchar to numeric.

Question

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?

Answer 1

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.

 

Answer 2

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
*/

 

 

Answer 3

Although an old post , but just to help others

a simpler way of doing the above is

 

declare @xml xml

set @xml =

'<Root>

<Data></Data>

<Data>17</Data>

</Root>'

 

 

select t.value('(./text())[1]', 'decimal')

 

as targetValue

from @xml.nodes('/Root/Data') as x(t)

---

usm121

Answer 4

hi, how this can be achieved while retreiving attribute value

Answer 5

Please post a small piece of sample XML and your expected results.