More details on xml – or what I have learned so far

In my last post, I didn’t go into too much details about using Excel to produce xml files, and I still won’t but I will elaborate just a little further. It appears that with Excel, your requirements on the schema have to be on the simpler side: no parent-child relationships or list of lists. (Schemas are typically xsd or xml files that are a set of instructions detailing what fields, what order, and what data types are required.) I’m not really sure I understand what a list of lists mean other than it’s a list of other kinds of lists (duh). As an example, I’m thinking that could mean you have a list called animals and within that list, you many lists such as a list of domesticated animals, a list of wild animals, a list of aquatic animals, and so on. These lists of lists are kind of similar to the parent-child relationships so I think parent-child and list of lists are the same.

I picture the parent-child relationship as similar to  set of folders in your computer directory. Excel can do xml conversions when there are only two levels of folders: a set of folders in the first level and then the ones nestled within the first set. I don’t think Excel can do a series of nestled folders such as a main folder called animals, then within that folder a set of folders for domesticated, wild, zoo and aquatic and then within those folders, yet more folders. I’m under the impression that Excel can go down only one layer, namely because Excel is essentially a flat file rather than one built upon relationships.

So keep in mind the requirement of simplicity if you want to convert an Excel file into xml document.

To convert your Excel file of data into xml, you will need a starter file which is a xsd or xml document. The starter file is typically called a schema. You can create one in Notepad. The version I did was a xml file and is shown down below at the end. The first two lines will pretty much state that the file is a xml file. After that, there will be a list of elements, describing the order that they should appear. The element names are enclosed in <> or </> tags. The </> is a closing tag. (See below.) Each record is a row of data. In order to use this starter file as a schema, you will need to fill in the data between the tags for the first 2 rows of records in Excel. Each <> opening tag must have a corresponding closing </> tag somewhere in the document. See how the opening <record> at the start of each record has a closing </record> at the end of the record? Once you’ve written up your xml file in Notepad, save it as xml.

Now you are ready to convert your Excel data into xml, using the xml starter file. This all takes place in the Developer tab. For those of you who do macros, you will already have this tab showing. The rest of you will need to go into your File-Options and Customize your Ribbon. On the right hand side, you will see a layout where you can choose what you want to display on the Ribbon. Developer is one of them.

In the Developer tab, you will see a tool for Source. One you select that, a xml Source box will appear. At the bottom of that Source box, there is a button called XML Map. Click on that button and browse to find your starter file, which should have been saved as a xml document. When you select your xml starter document, Excel will tell you that it could not find the schema and thus will determine the data formats for you. Just click ok. Now you are ready to map the elements from the Source pane onto the field headers in Excel.

When you are done mapping the elements to Excel, you are ready to Export into xml using the Export command found in the ribbon. Of course, make sure you save as xml.

And that is pretty much it.

For the ACA, the government has provided some xsd (the real schemas) and xml but unfortunately, the xml is not simple and gave me problems due to the parent-child relationships. I had to simplify it using the government’s xml as a guideline. The government’s xml combines form 1094C and 1095C into one xml. I focused first on the 1095C form, namely because there are so many forms that there is no way I will be able to hand code the information. So I got rid of the 1094C section and worked on simplifying the 1095C section. The xml that I used as a guideline is called “IRS-Form1094-1095CTransmittalUpstreamMessage”. The version below is close to what I eventually used.

Now I’m working on the macro to add back the things that I had to take out.

Example xml File

[ap_divider color=”#CCCCCC” style=”solid” thickness=”5px” width=”100%” mar_top=”20px” mar_bot=”20px”]

<?xml version=”1.0″ encoding=”UTF-8″ standalone=”yes”?>
<data-set xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
<record>
<Name>Mary Poppin</Name>
<SSN>xxx-xx-xxxx</SSN>
<Street>1500 Wonderland</Street>
<City>Imagination</City>
<State>Fantasyland</State>
<Zip>24680</Zip>
<PlanStartMonth>05</PlanStartMonth>
<OC-All>1E</OC-All>
<OC1></OC1>
<OC2></OC2>
<OC3></OC3>
<OC4></OC4>
<OC5></OC5>
<OC6></OC6>
<OC7></OC7>
<OC8></OC8>
<OC9></OC9>
<OC10></OC10>
<OC11></OC11>
<OC12></OC12>
<LC-All></LC-All>
<LC1>115.00</LC1>
<LC2>115.00</LC2>
<LC3>115.00</LC3>
<LC4>115.00</LC4>
<LC5>100.00</LC5>
<LC6>100.00</LC6>
<LC7>100.00</LC7>
<LC8>100.00</LC8>
<LC9>100.00</LC9>
<LC10>100.00</LC10>
<LC11>100.00</LC11>
<LC12>100.00</LC12>
<SH-All>2H</SH-All>
<SH1></SH1>
<SH2></SH2>
<SH3></SH3>
<SH4></SH4>
<SH5></SH5>
<SH6></SH6>
<SH7></SH7>
<SH8></SH8>
<SH9></SH9>
<SH10></SH10>
<SH11></SH11>
<SH12></SH12>
</record>
<record>
<Name>Jack Heman</Name>
<SSN>xxx-xx-xxx</SSN>
<Street>500 Hero Road</Street>
<City>Fighter</City>
<State>Strongest</State>
<Zip>24680</Zip>
<PlanStartMonth>05</PlanStartMonth>
<OC-All></OC-All>
<OC1>1E</OC1>
<OC2>1E</OC2>
<OC3>1E</OC3>
<OC4>1E</OC4>
<OC5>1E</OC5>
<OC6>1E</OC6>
<OC7>1E</OC7>
<OC8>1E</OC8>
<OC9>1H</OC9>
<OC10>1H</OC10>
<OC11>1H</OC11>
<OC12>1H</OC12>
<LC-All></LC-All>
<LC1>115.00</LC1>
<LC2>115.00</LC2>
<LC3>115.00</LC3>
<LC4>115.00</LC4>
<LC5>100.00</LC5>
<LC6>100.00</LC6>
<LC7>100.00</LC7>
<LC8></LC8>
<LC9></LC9>
<LC10></LC10>
<LC11></LC11>
<LC12></LC12>
<SH-All></SH-All>
<SH1>2H</SH1>
<SH2>2H</SH2>
<SH3>2H</SH3>
<SH4>2H</SH4>
<SH5>2H</SH5>
<SH6>2H</SH6>
<SH7>2B</SH7>
<SH8>2A</SH8>
<SH9>2A</SH9>
<SH10>2A</SH10>
<SH11>2A</SH11>
<SH12>2A</SH12>
</record>
</data-set>