Error converting data type nvarchar to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Error converting data type nvarchar to numeric

Author

Topic

Liat001
Starting Member

17 Posts

Posted - 2010-07-08 : 04:51:35

Hi All,
I have an Error :Error converting data type nvarchar to numeric.
when I try to opem xml like that:
set @Line =<RootXMLGrid><Row><length></length>...</Row><RootXMLGrid>

EXEC sp_xml_preparedocument @handle OUTPUT, @Lines

SELECT * INTO #tmpLinesTable
FROM OPENXML (@handle, 'RootXMLGrid/Row',2)
WITH (

Length numeric (4,2)
.
.
.
)

when the tag is empty or blank,the sql failed with the Error.
It is only happen with sql 2008 and not with 2005.

What can I do?
Thanks,

Sachin.Nand

2937 Posts

Posted - 2010-07-08 : 05:34:48

I think it is better you move away from OpenXml and use the new xquery methods supported from SQL 2005.


declare @Line xml
set @Line ='<RootXMLGrid><Row><length></length></Row></RootXMLGrid>'
select @Line
select x.i.value('.','varchar(10)')from @line.nodes('/RootXMLGrid/Row')x(i)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Liat001
Starting Member

17 Posts

Posted - 2010-07-09 : 04:05:36

Thanks ,I like it.
but still when I need to insert the value into param with type numeric. It is failed.
I need to transfer params per row to another SP.
Is the only way is with "CASE WHEN"?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-09 : 04:25:21

[code]declare @Line xml

set @Line ='<RootXMLGrid><Row><length></length></Row></RootXMLGrid>'

select @Line

select CAST(x.i.value('.', 'varchar(10)') + '0' AS numeric(10,2)) AS yak
from @line.nodes('/RootXMLGrid/Row') as x(i)[/code]

N 56°04'39.26"
E 12°55'05.63"

Liat001
Starting Member

17 Posts

Posted - 2010-07-11 : 03:38:40

Hi,
It is not so good.
Because if I have in the length tag :8, I get 80.

Kristen
Test

22859 Posts

Posted - 2010-07-11 : 03:59:53

I know nothing about XML, so this may be no help, but just in case:

CAST(COALESCE(x.i.value('.', 'varchar(10)'), '0') AS numeric(10,2))

or maybe

CAST(NullIf(RTrim((x.i.value('.', 'varchar(10)')), '') AS numeric(10,2))

which should give you NULL for blank ones, instead of zero

Liat001
Starting Member

17 Posts

Posted - 2010-07-11 : 08:42:56

:)
NullIf is more better then "CASE WHEN"
It is work thanks.
and NullIf(x.i.value('.', 'varchar(10)'), '') is Enough.

But what is the difference betweem open xml and the other option?
Do I need to write for any tag :
@line.nodes('/RootXMLGrid/Row/length') as x(i)
@line.nodes('/RootXMLGrid/Row/width') as y(i)
with the tag name if I want to insert them into a difference Column.
Apparently there is another way,No?

godaniel
Starting Member

1 Post

Posted - 2013-03-04 : 16:40:58

Hello all,

I realize this is an old topic but I thought I'd add what I ended up doing in case anyone still comes across the topic while searching. Take a look at the example I provide below, specifically at the filter on the Length portion of the WITH block.


SELECT * INTO #tmpLinesTable
FROM OPENXML (@handle, 'RootXMLGrid/Row',2) 
WITH (

Length	 numeric (4,2) 'Length[.!=""]'
.
.
.
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources