Creating XML from Excel

Question

I am using Excel for Office 365 ProPlus. And I'm trying to create an XML file from Excel data (not more than 100 rows).

Started with the following sample schema file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <record>
       <LastName>Smith</LastName>
       <Sales>16753</Sales>
       <Country>UK</Country>
       <Quarter>Qtr 3</Quarter>
    </record>
    <record>
       <LastName>Johnson</LastName>
       <Sales>14808</Sales>
       <Country>USA</Country>
       <Quarter>Qtr 4</Quarter>
    </record>
</data-set>

And it worked on a sample data file:

Now I'm trying the same schema on an extract from my real data (though in fact I will need a more sophisticated schema with 2 layers - see below):

Now I'm getting an error: "...is not exportable because it contains the following: A mapped element relationship with other elements can't be preserved".

I may be blind but I can't see any principal difference between two sources. I've tried copying formatting, remapping from scratch, renaming the columns to no avail.

Does anyone have an idea of how to make it work?

BTW, my desired schema will look similar to this one:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Graduate>
<HUSID>1600000000003</HUSID>
<OWNSTU>8936</OWNSTU>
<COUNTRY>XF</COUNTRY>
<EMAIL>[email protected]</EMAIL>
<FNAMES>Diana</FNAMES>

<SURNAME>XYZ</SURNAME>
<UKTEL>02080000000</UKTEL>
<UKMOB>07700000000</UKMOB>
<PostalAddress>
<ADDRESSLN1>36 Street </ADDRESSLN1>
<ADDRESSLN2>London</ADDRESSLN2>
<ADDRESSLN4>United Kingdom</ADDRESSLN4>
<POSTCODE>HA7 HA7</POSTCODE>
</PostalAddress>
</Graduate>
<Graduate>
<HUSID>160000000013</HUSID>
<OWNSTU>8936</OWNSTU>
<COUNTRY>XF</COUNTRY>
<EMAIL>[email protected]</EMAIL>
<FNAMES>Ana</FNAMES>

<SURNAME>LLL</SURNAME>
<UKTEL>02089999999</UKTEL>
<UKMOB>07777777777</UKMOB>
<PostalAddress>
<ADDRESSLN1>36  Park ,</ADDRESSLN1>
<ADDRESSLN2>London</ADDRESSLN2>
<ADDRESSLN4>United Kingdom</ADDRESSLN4>
<POSTCODE>HA7 HA8</POSTCODE>
</PostalAddress>
</Graduate>
</data-set>

Dominique · Accepted Answer · 2019-02-26 12:24:53Z

1

SOLVED (though I still have no explanation as to why this work around was needed).

Leave all columns you need to map next to each other and in the order of tags in your schema;
Not sure this is mandatory - but blank values may prevent mapping of the cells after them;
Drag the top-level tag (GRADUATE in my case) to the first data cell in the first column (may work with header but not tested yet);

You will now have all columns mapped in their order and in the order of tags in your schema. The message changes to Map is exportable.

Comments are welcome from those who can explain why mapping works this way and not by mapping individual tags as all manuals suggest.

edited Feb 26, 2019 at 12:24

Dominique

17.6k20 gold badges81 silver badges161 bronze badges

answered Feb 26, 2019 at 11:01

user2097141

3472 gold badges4 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating XML from Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related