XML Schema totalDigits/fractionDigits vs. SQL precision/scale

I’d like to find out the correspondence between XML Schema totalDigits/fractionDigits and SQL numeric precision/scale.

1) Assume we have the following simple type:

<xs:simpleType name="DecimalNumber">
    <xs:restriction base="xs:decimal">
        <xs:fractionDigits value="17"/>
        <xs:totalDigits value="18"/>
    </xs:restriction>
</xs:simpleType>

How would I represent it in SQL? Let’s assume HSQL dialect (it does not matter much). I’m interested in the most restrictive SQL type which could hold all the values from the value space of the XML Schema simple type.

Would it be numeric(18,17)? Or numeric(35,17)?

2) What if we have just totalDigits?

<xs:simpleType name="DecimalNumber">
    <xs:restriction base="xs:decimal">
        <xs:totalDigits value="18"/>
    </xs:restriction>
</xs:simpleType>

What would be an adequate representation in SQL?

3) Or just fractionDigits?

<xs:simpleType name="DecimalNumber">
    <xs:restriction base="xs:decimal">
        <xs:fractionDigits value="17"/>
    </xs:restriction>
</xs:simpleType>

Answer

Start with this in mind:

scale ≡ fractionDigits
precision ≡ totalDigits

In HSQL:

1. DECIMAL (35, 17) — edit: fractionDigits is an upper bound; in your case from none to 17. Hence, the domain of values to be represented ranges from 18 digits on the left, to 17 on the right (of the decimal point). So you need a scale of 17, and (18 + 17) digits to be able to represent the max values: 18 digits with no decimal digits.

2. A naïve translation to DECIMAL (18) really means 18 digits without a decimal point since the scale is defaulted to 0 – not XSD’s equivalent. To allow capturing all numbers, one would need to code DECIMAL (36, 18) which would exceed, in terms of domain values, what’s representable by XSD’s 18 total digits. You’ll have to make a call here.

3. In SQL, I am not aware of a way to specify scale without precision. Since XSD doesn’t provide an upper limit (it asks for a minimum of 18)… you may have to just choose a number, maybe as limited by your infrastructure (e.g. MS-SQL has max 38 precision), or don’t bother at all (Apache’s HsqlDB uses unlimited precision and scale).