SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Error converting data type nvarchar to numeric
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Liat001
Starting Member

17 Posts

Posted - 07/08/2010 :  04:51:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 07/08/2010 :  05:34:48  Show Profile  Reply with Quote
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
Go to Top of Page

Liat001
Starting Member

17 Posts

Posted - 07/09/2010 :  04:05:36  Show Profile  Reply with Quote
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"?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 07/09/2010 :  04:25:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Liat001
Starting Member

17 Posts

Posted - 07/11/2010 :  03:38:40  Show Profile  Reply with Quote
Hi,
It is not so good.
Because if I have in the length tag :8, I get 80.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/11/2010 :  03:59:53  Show Profile  Reply with Quote
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

Edited by - Kristen on 07/11/2010 04:01:29
Go to Top of Page

Liat001
Starting Member

17 Posts

Posted - 07/11/2010 :  08:42:56  Show Profile  Reply with Quote
:)
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?
Go to Top of Page

godaniel
Starting Member

1 Posts

Posted - 03/04/2013 :  16:40:58  Show Profile  Reply with Quote
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[.!=""]'
.
.
.
)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000