3

I have an Excel worksheet with a list of cars:

Row 1 contains column descriptors for vehicle attributes

I created an XML schema document to allow me to export the worksheet to XML. The schema is as follows:

<?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>

In Excel, I clicked Developer > Source > XML Maps > Add, then I selected the XML schema file from above and added it to my worksheet. I mapped each element in the XML Source window to the corresponding column in the Excel worksheet (the "Brand" element is mapped to A:A, the "Model" element is mapped to B:B, etc.).

enter image description here

When it came to exporting the mapped worksheet (using Developer > Export, then selecting a directory and file name), the resultant XML file only had the first row of data from the worksheet. As my XML mapping included the title row of the worksheet, the XML file looked like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Root>
    <Brand>Brand</Brand>
    <Model>Model</Model>
    <Colour>Colour</Colour>
    <Price>Price</Price>
</Root>

I've looked through numerous different help pages, and I'm not sure how to make my XML file store every row (all five, including the title row) of the Excel worksheet.

pnuts
  • 58,317
  • 11
  • 87
  • 139

3 Answers3

4

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>
J.Doe
  • 53
  • 5
1

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:

Window showing the root selection

Select the element which references the other element (as this allows for recursion). In the XML source panel, the map will look like this:

The Schema source which will allow for recursive exporting to an XML file

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:

The data in the sheet after successfully mapping the schema

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:

Data from the Cars sheet in XML format

This was done using Excel 2010, other versions will differ somewhat.

1

What worked for me is simply edit the schema and have multiple entries for each entity. So in your case:

<?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="Brand" type="xsd:string"/>
                <xsd:element name="Model" type="xsd:string"/>
                <xsd:element name="Model" type="xsd:string"/>
                <xsd:element name="Colour" type="xsd:string"/>
                <xsd:element name="Colour" type="xsd:string"/>
                <xsd:element name="Price" type="xsd:string"/>  
                <xsd:element name="Price" type="xsd:string"/> 
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

Just need to put in two entries for each and this allows for multiple entries to be exported.

MapZombie
  • 25
  • 8