OPENXML - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

OPENXML

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-16 : 12:14:43

I am getting an error when inserting into table using open XML

Error converting data type nvarchar to numeric.

alter PROCEDURE [dbo].[Yahoo_Insert_Into_Yahoo_Order_Product]
@xmlDocument XML
AS

DECLARE @idoc int

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDocument

INSERT INTO Yahoo_Order_Product
([Order_Id]
,[Item_Number]
,[Item_Id]
,[Item_Code]
,[Item_Quantity]
,[Item_Unit_Price]
,[Item_Description]
,[Item_Url]
,[Item_Taxable]
,[Item_Thumb]
)

-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/OrderList/Order/Item',2)
WITH (Order_Id VARCHAR(250) '../@id',
Item_Number varchar(50) '@num',
Item_Id varchar(500) 'Id',
Item_Code varchar(500) 'Code',
Item_Quantity int 'Quantity',
Item_Unit_Price decimal(18,2) 'Unit-Price',
Item_Description varchar(500) 'Description',
Item_Url varchar(500) 'Url',
Item_Taxable decimal(18,2) 'Taxable',
Item_Thumb varchar(500) 'Thumb'
)

EXEC sp_xml_removedocument @idoc

[dvx_5_Yahoo_Insert_Into_Yahoo_Order_Product]
'<OrderList StoreAccountName="games">
<Order currency="USD" id="games-128093">
<Item num="0">
<Id>72267402068</Id>
<Code>72267402068</Code>
<Quantity>1</Quantity>
<Unit-Price>29.99</Unit-Price>
<Description>Ace Combat 2 PS</Description>
<Url>d</Url>
<Taxable>YES</Taxable>
<Thumb>d</Thumb>
</Item>
<Item num="1">
<Id>72267402068</Id>
<Code>72267402068</Code>
<Quantity>1</Quantity>
<Unit-Price>29.99</Unit-Price>
<Description>Ace Combat 2 PS</Description>
<Url>d</Url>
<Taxable>YES</Taxable>
<Thumb>d</Thumb>
</Item>
</Order>
</OrderList>'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 12:45:23

its because of the below statement

Item_Taxable decimal(18,2) 'Taxable'

see value of Taxable in XML

<Taxable>YES</Taxable>

how do you think you can convert this to numeric? thats the reason why it errors stating it cant convert text data YES to numeric

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-16 : 13:14:41

Thanks Visakh for your reply. It is not that.

It is still throwing the same error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 13:23:34

Its definitely one among the reasons if there are others too. also look for cases where you've text values coming from xml and you're putting it into numerical field like decimal,numeric,int etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-16 : 13:39:40

You were right Visakh. It works great.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 13:41:17

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources