Best practices - currency elements

Hello! I'm a long-time SQLServer developer, but new to XML.

I find myself doing some XML related to EDI messages.

When you have a field containing a dollar amount, what format should you use in the XSD?

We've been using decimal.

But that's just half the question!

When the XML comes in and there is a round dollar amount, we've been getting the data in integer format, a five dollar order just looks like <mytotal>5</mytotal>.

Wouldn't it seem like a best practice to make this <mytotal>5.00</mytotal>?

Thanks.

Josh

Could this be a problem with the specification of the database column? For example:

Code Snippet

declare @.testo table(myTotal decimal , dec_9_2 decimal(9,2))
insert into @.testo select 5, 3
--select myTotal from @.testo

select myTotal
from @.testo
for xml path('')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
--
<myTotal>5</myTotal>
*/

select dec_9_2
from @.testo
for xml path('')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
--
<dec_9_2>3.00</dec_9_2>
*/

In the first query the source column is simply defined as a DECIMAL column and is displayed without any fractional "decimal" portion. When this column is converted to XML it displays only the whole number portion because really, the data consists of whole number only.

In the second query the source column is defined as DECIMAL (9, 2) column. This provides for 7 whole number digits and 2 decimal digits. When this column is converted to XML it displays the desired decimal places.

Can you provide the DDL for your source column?

|||

The XML is prepared by an outside source, in fact it comes from an EDI message.

I'm wondering whether - more like just how - to raise it with them as an improvement they should make.

Thanks.

Josh

|||

What I would wander is first, are ANY of these fields coming in with decimals. If none, I would definitely raise the issue if your are supposed to be getting 2-decimal accuracy. They may have an error that they are not aware of.

Also, the advantage of getting the decimals is that it eliminates doubt -- which is exactly what you are expressing. It is probably a good idea just to ask the question so that the doubt is eliminated. Much better to talk now than miss something.

sql