| Author |
 Topic  |
|
|
Liat001
Starting Member
17 Posts |
 Posted - 07/08/2010 : 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
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 07/08/2010 : 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 - 07/09/2010 : 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
Sweden
29138 Posts |
Posted - 07/09/2010 : 04:25:21
|
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" |
|
|
|
Liat001
Starting Member
17 Posts |
Posted - 07/11/2010 : 03:38:40
|
Hi,
It is not so good.
Because if I have in the length tag :8, I get 80.
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/11/2010 : 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 |
Edited by - Kristen on 07/11/2010 04:01:29 |
|
|
|
Liat001
Starting Member
17 Posts |
Posted - 07/11/2010 : 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 Posts |
Posted - 03/04/2013 : 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[.!=""]'
.
.
.
)
|
|
|
| |
Topic |
|
Error converting data type nvarchar to numeric
