You should add somewhere this:
minOccurs="0" maxOccurs="unbounded"
To tell excel that your elements can appear several times.
But you can't add it within the "Root" element.
Try this:
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Root">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Car" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Brand" type="xsd:string"/>
<xsd:element name="Model" type="xsd:string"/>
<xsd:element name="Colour" type="xsd:string"/>
<xsd:element name="Price" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
You should add somewhere this:
minOccurs="0" maxOccurs="unbounded"
To tell excel that your elements can appear several times.
But you can't add it within the "Root" element.
Try this:
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Root">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Car" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Brand" type="xsd:string"/>
<xsd:element name="Model" type="xsd:string"/>
<xsd:element name="Colour" type="xsd:string"/>
<xsd:element name="Price" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I didn't use J.Doe's answer, but I assume that it's correct because it's similar to what I ended up doing. When linking the original XML schema:
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Root">
<xsd:complexType>
<xsd:sequence>
<!--Below are the primary vehicle descriptors - essentially the attributes for the cars-->
<xsd:element name="Brand" type="xsd:string"/>
<xsd:element name="Model" type="xsd:string"/>
<xsd:element name="Colour" type="xsd:string"/>
<xsd:element name="Price" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Excel assumed that there was only one record in the sheet. The solution to this was to create a second complex element (in addition to the element named "Root") in my schema, which contains >1 of my Root elements. The resultant file looked like this:
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Car_Table">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="Root" minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="Root">
<xsd:complexType>
<xsd:sequence>
<!--Below are the primary vehicle descriptors - essentially the attributes for the cars-->
<xsd:element name="Brand" type="xsd:string"/>
<xsd:element name="Model" type="xsd:string"/>
<xsd:element name="Colour" type="xsd:string"/>
<xsd:element name="Price" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
When adding the schema using Developer > Source > XML Maps > Add, you get a prompt like this:
Select the element which references the other element (as this allows for recursion). In the XML source panel, the map will look like this:
You can then map each element in the schema (Brand, Model, Colour, Price) to the corresponding column in the sheet. Once the schema is mapped to the sheet, your data will look like this:
You can then export the data to XML by going to Developer > Export. After Selecting a filename and directory using the export dialog, the resultant XML file should look something like this:
This was done using Excel 2010, other versions will differ somewhat.
Videos
I am attempting to export my Excel data using an XML schema. I have already got the XML map and I am trying to apply it to my columns.
Every tutorial I look at, such as this one: http://www.excel-easy.com/examples/xml.html, shows the person simply dragging the item from XML source onto the column at which point it turns blue.
I am trying to do the same, but it does not work. I can apply the "schema element" to one cell in a column, but never anymore. When I apply it to one column it has a small blue outline.
Another related question, is once I have applied the schema to a cell (or hopefully multiple cells after this post), how do I delete that schema from the cells? The only way I have seen so far is to delete the map itself from the workbook.
I am using Excel 2010. Any help would be really appreciated! Thanks!
I have a whole ton of XML files, each with one top RECORD, and individual elements underneath. For instance, I'll have an ID, ORDERNUMBER, COMPANYNAME,STREETADDRESSS, etc. What I'd like to do is have column headers for each of these XML elements (Col1 = ID, Col2 = Order Number, Col3 = Company Name, etc). I'd then like to import multiple XML files and have that corresponding data fill in down the columns.
Is this possible?
Currently I've tried XML Maps, where I drag the elements into the top row of the worksheet. When I import a file or refresh the XML data, it fills the info in as expected, but it seems as if it can only show one XML files worth of data at a time. There's always only one row with information and the rest is blank.
Thoughts on what I could do or what I'm doing wrong?
Excel experience is... average I suppose. I'm working in Excel 2016.
