Bulk Insert - Precision lost - XML format file

[lameid]

SQL Express 2008 R2

I am using bulk insert to load data into a temp table and after finally figuring out that format files really hate spaces in field names (header row in source file has spaces, I was trying to be consistent - only time I make that mistake)... I find that even using an XML format file with data types specified, Bulk insert is not keeping the full precision of the data in the file. Specifically my Decimal column is being limited to a precision of 2.

The file is Pipe delimited. I created a physical table that matches my temp table, exported and replaced the \t with |...

Some field names changed...

<?xml version="1.0"?>
<BCPFORMAT xmlns="[URL unfurl="true"]http://schemas.microsoft.com/sqlserver/2004/bulkload/format"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance">[/URL]
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="200" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="200" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="200" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="30"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="30"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="30"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="30"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="30"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="30"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="510" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="41"/>
  <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="30"/>
  <FIELD ID="16" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="ddddddd_Period" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="Country" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Term_Country" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="XX_Description" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="Pass_Through" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="6" NAME="Inventory_Volume" xsi:type="SQLFLT8"/>
  <COLUMN SOURCE="7" NAME="Contractual_Volume" xsi:type="SQLFLT8"/>
  <COLUMN SOURCE="8" NAME="XXXX" xsi:type="SQLFLT8"/>
  <COLUMN SOURCE="9" NAME="YXXX" xsi:type="SQLFLT8"/>
  <COLUMN SOURCE="10" NAME="XXX_XXX_Rate" xsi:type="SQLFLT8"/>
  <COLUMN SOURCE="11" NAME="CCC_XXX_Rate" xsi:type="SQLFLT8"/>
  <COLUMN SOURCE="12" NAME="Variance" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="13" NAME="XXX_XXX_Band_Exceeded" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="14" NAME="XXX_XXX_Revenue" xsi:type="SQLDECIMAL" PRECISION="24" SCALE="10"/>
  <COLUMN SOURCE="15" NAME="Manual_Adjustments" xsi:type="SQLFLT8"/>
  <COLUMN SOURCE="16" NAME="A7_A8_UID" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Column 14 is the one I care about the most. I am validating the charge that appears in this column... and apparently this vendor thinks you should keep precision, sum and then round.

And in case it matters, my bulk insert...

et @BulkInsert	 = N'BULK INSERT dbo.#xxx FROM ''' + @File + N''' WITH (FIRSTROW = 2, Formatfile = ''C:\file.xml'')'

exec sp_executesql @BulkInsert

Many thanks for the help.