Create an XSD that can be imported to EXCEL with restrictions based on element values

Question

I have been searching the web for a while and from what I can tell what I want is not possible using XSD 1.0. My requirement is:

We have an .xml that create the following table when imported to excel:

The requirement is only one, that the values under Bird 1,2,3 are restricted from the range of each Min & Max of each feature.

For example the Bird 1 weight must be between 10-20 but the Bird 1 height to be between 3-9.

Under the Bird 3 the weight and height are not valid methods because they are outside their min-max range.

Now I need to make an XSD file that can be imported to Excel and that it create the restriction that make the above work.

The XSD i have made is:

    xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Root">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Row" maxOccurs="unbounded" minOccurs="0">
          <xs:complexType>
            <xs:sequence>

              <xs:element type="xs:string" name="Feature"/>           
              <xs:element type="xs:int" name="Min"/>              
              <xs:element type="xs:int" name="Max"/>              
              <xs:element name="Bird 1">
                  <xs:simpleType>
                    <xs:restriction base="xs:int">
                        <xs:minInclusive value="X"/> <!-- X= The Min value -->
                        <xs:maxInclusive value="Y"/> <!-- Y= The Max value -->
                    </xs:restriction>
                  </xs:simpleType>
              </xs:element>           
              <xs:element name="Bird 2">
                  <xs:simpleType>
                    <xs:restriction base="xs:int">
                        <xs:minInclusive value="X"/>  <!-- X= The Min value -->
                        <xs:maxInclusive value="Y"/>  <!-- Y= The Max value -->
                    </xs:restriction>
                  </xs:simpleType>
              </xs:element>           
              <xs:element name="Bird 3">
                  <xs:simpleType>
                    <xs:restriction base="xs:int">
                        <xs:minInclusive value="X"/>  <!-- X= The Min value -->
                        <xs:maxInclusive value="Y"/>  <!-- Y= The Max value -->
                    </xs:restriction>
                  </xs:simpleType>
              </xs:element>

            </xs:sequence>
          </xs:complexType>
        </xs:element>       
      </xs:sequence>      
    </xs:complexType>
  </xs:element>
</xs:schema>

The XSD above works only if in the restriction values we have fixed numbers.

Ideally I would like to have the restriction minInclusive and maxInclusive values pointing to the Min Max element.

If I could use XSD 1.1 or something else like schematron that would be feasible. But because I need the xsd to be imported to Excel I am forced to use XSD 1.0.

I am currently trying to figure out a way to do it with Key and Key-ref based on the example sample but so far I had no luck.

Any suggestion or workaround to make this work???

Thank you in advance

Ghislain Fourny · Answer 1 · 2017-11-06T07:59:20.000

Indeed with XML Schema 1.1, you can define further constraints with XPath.

I have a comment on the data model though. In short, I would suggest swapping rows and columns on the logical level. If I am correctly assuming that new birds will be added more often than new features, the schema should be optimized for this, which happens to circumvent the problem.

If the features of the birds (age, weight, height) are known in advance or rarely changed, I would treat them as XML Schema types rather than rows. This way, the minimum and maximum values can be stored in the XML Schema (as done above) and will apply for all birds, and this should be achievable with XML Schema 1.0.

Birds could then be made first-class citizens and become "XML rows" while their features can become their properties (but nothing prevents displaying them as columns in excels, and their children elements as rows):

<birds>
  <bird id="Bird 1">
    <age>5</age>
    <weight>15</weight>
    <height>4</height>
  </bird>
  <bird id="Bird 2">
    <age>9</age>
    <weight>12</weight>
    <height>7</height>
  </bird>
  <bird id="Bird 3">
    <age>2</age>
    <weight>22</weight>
    <height>2</height>
  </bird>
</birds>

Dear Ghislain, thank you for your suggestion. what you suggest make sense and it would be an excellent workaround, however the example I have given is just an simplification of my real usecace. In the actual usecase the features are several hundredths and they keep growing, and the "Birds" are only a handful. So in the real usecase your suggestion is not possible, despite the fact that is very useful for other situations. I am sorry for not making clear in my question that the feature number is quite big. — Spyros, Nov 06 '17 at 08:37
Thank you for your feedback, Spyros. It makes sense and, indeed, the use case is very important and affects the design. I am afraid that XML Schema 1.1's `xs:assert` is needed for dynamically restricting ranges. — Ghislain Fourny, Nov 07 '17 at 16:54

Create an XSD that can be imported to EXCEL with restrictions based on element values

1 Answers1