FOR XML PATH issue

• steve.petruso

  SSC Veteran

  Points: 235

  More actions

  September 21, 2011 at 3:26 pm

  #257651

  I'm using SS 2008 and trying to read a sql table to create an XML string from the results returned. The sql table contains integers and decimals in the columns (counts and percents). The FOR XML function does return back an xml sting, but all the values are in scientific notation(sn). It appears that the columns with the percents (95.22) are causing all the values to return as sn. The integers are defined as smallint, and the decimals as float. Anyone know how to return the values real numbers?

  Thanks.

• paul_ramster

  Ten Centuries

  Points: 1382

  More actions

  September 22, 2011 at 4:38 am

  #1385035

  Cast the float as a decimal datatype first.

  e.g.

  create table #xml_test (id int identity primary key, int_val int, decimal_val float);

  set nocount on;

  insert into #xml_test (int_val, decimal_val)

  values (10,5.55),(15,6.75);

  select id

  ,int_val

  ,decimal_val

  , cast(decimal_val as decimal(12,2)) as decimal_val_cast

  from #xml_test

  for xml path('row'), root ('root'), type;

  which returns:

  <root>

  <row>

  <id>1</id>

  <int_val>10</int_val>

  <decimal_val>5.550000000000000e+000</decimal_val>

  <decimal_val_cast>5.55</decimal_val_cast>

  </row>

  <row>

  <id>2</id>

  <int_val>15</int_val>

  <decimal_val>6.750000000000000e+000</decimal_val>

  <decimal_val_cast>6.75</decimal_val_cast>

  </row>

  </root>

• steve.petruso

  SSC Veteran

  Points: 235

  More actions

  September 22, 2011 at 8:57 am

  #1385220

  Thanks for the reply that worked.:-)