Why Excel Complains About an XML Source Having No Schema

The message appears when you open an XML file in Excel or import one into a worksheet, and in substance it says that the XML source does not refer to a schema and Excel will create one from the data (I paraphrase; the exact wording varies by version). It sounds like a fault. It is a description of what is about to happen, and it matters because the schema Excel creates decides how the data lands.

What a schema is for, in Excel

Excel's XML features rest on the XML map (importing XML into Excel with an XML map). A map links the elements of a schema to cells. Without a schema there is nothing to link. So when the file you open does not point to an XSD (through a schemaLocation attribute or similar), Excel builds one from the data. Microsoft's documentation says so: if the XML data file does not refer to a schema, Excel infers the schema from the data file. The warning tells you it is doing that.

If the file does refer to a schema, Excel looks for it, and a file that points at an unreachable schema (a URL that does not load, a relative path that is wrong) can produce a different complaint. The two situations are different and the message wording names which.

What inference does

Inference reads the file and writes down what it saw: the elements, their nesting, which ones repeated and the type of each text value. Excel's exact rules are not documented in detail, but the nature of the process is the same in any tool, and I ran one to see it. .NET's XmlSchemaInference, given two sample orders, produced a schema that was correct about the structure and guessed at the rest:

<xs:element minOccurs="0" name="note" type="xs:string" />
<xs:element maxOccurs="unbounded" name="item">...
<xs:attribute name="id" type="xs:unsignedByte" use="required" />
<xs:attribute name="qty" type="xs:unsignedByte" use="optional" />

(From the inference run in generating an XSD from sample XML.) The structure came out right where the two samples differed: note is optional because one order lacked it, item repeats because one order had two. Where the samples agreed, the schema is rigid or too small: ids and quantities typed as unsignedByte (0 to 255) because the sample values were small. An order number of 300 in the next file would not fit.

Excel's inference has the same property: the schema is what the sample contained. Four consequences follow.

An element that is not in the sample is not in the schema. A file where the optional coupon element never appears maps without it. The next file that has coupons will not find a place for them, or will not map.

A field that never repeated is single-valued. If every order in the sample had one item, item is a single element. A later file with two items per order does not fit the map.

Types come from values. Numbers become numeric types, dates may become date types, and anything else is a string. An identifier 02134 that looks like a number can be typed as one.

Order and nesting are frozen. The sequence of children in the sample is the schema's sequence.

The practical effect

For a flat file of records from one export, inference is fine: you get a table of the columns present. The failures come when the next file differs. A weekly import into a mapped sheet works for three weeks and fails in the fourth because a record has a field that did not appear before. The message is again about the schema, and now the guessed schema is the reason.

The fix for a recurring import is to stop letting Excel guess. Write or obtain an XSD that describes the whole format, with the optional elements marked optional, the repeating elements marked repeating and the types you want:

<xs:element name="zip" type="xs:string"/>
<xs:element name="total" type="xs:decimal"/>
<xs:element name="coupon" type="xs:string" minOccurs="0"/>

Load it as the map's schema in the XML Source pane (Developer tab, Source, XML Maps), map the elements and import. From then on the types are what you declared and a file with a coupon fits. The same schema validates the incoming files outside Excel (validating XML against an XSD schema), and it is the document you give a supplier when you say what you expect.

If you do not have a schema, derive a draft from many samples with an inference tool, and then edit it: widen the numeric types, add optional markers, type identifiers as strings and name the allowed values. That workflow is in the inference article and applies unchanged here.

When there is no schema and no need

If you only want to look at the data, dismiss the message and use the table. If you need a one-off conversion, a script that writes the workbook with types you choose is clearer than negotiating with a guess (converting XML to Excel with Python).

Other messages that look similar

The same family of messages appears in other situations, and the cause differs. A schema that Excel cannot use because it contains constructs the map cannot represent (a choice, a recursive element) triggers an unsupported-schema message when you try to map it. A mapping that Excel refuses because elements sit in different repeating contexts is a structure problem, not a schema-missing problem. And an export that fails verification reports the specific element. Read the message for the noun: no schema means none was referenced; cannot be used means it exists and does not fit the grid.

Related reading

For the cluster overview see moving XML into and out of spreadsheets; for inference see generating an XSD from sample XML; for opening files directly, opening XML files in Excel without mangled data. Test a path in the XPath tester.

When the message is the least of the problems

The file is not well-formed. Excel reports a parse error before any schema question. Check with xmllint --noout first.

The file is a document. Mixed content has no grid representation, and no schema helps.

The file is nested and large. Flatten before importing, or load into a database.

The import must be exact. If one wrong digit matters, do the conversion in code and verify counts and totals.