Import whole numbers but with decimals into sqlexpress

[Jimdeb03]

I'm importing data from an XML file into SQL Express using SQLXML 4.0 through a vb.net app. The numbers in the xml's are whole numbers, meaning no decimal places or points exist.

However, the numbers do represent a number with 2 decimal places.

So 59455 is actually 594.55
15500 is 155.00

I've tried data type decimal(10,2) but all this does is treat 59455 like 59455.00 which is incorrect
If I use data type decimal(10,0), then 59455 is imported as 59455 which isn't entirely correct either.

Of course decimal(10,-2) doesn't work.

Is my only recourse to create a function and convert these number values with a precision and scale of 10,2 manually?

Or is there a data type that'll force the placement of the decimal wherever desired?

 

[ousoonerjoe]

multiply the value by 0.01 on the way in and it will give you your decimal value to place in a DECIMAL field.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[Jimdeb03]

OF COURSE!

So what's needed is a function and/or sproc to do this little multiplication on each decimal field. SQLXML takes care of the actual import so the only recourse is to UPDATE the decimal fields after the fact.

The saving grace is SQLXML imports into temporary tables before an UPDATE occurs to the permament tables so some functionality can be added before the UPDATE occurs.

thanks again