Mapping Your Source Data to XML

Wisconsin Department of Health Services 1

The XML template file is essentially the XML upload layout file which is saved as a .xml file.

Here are some other conventions to remember:

• no column labels are permitted in the Excel record spreadsheet

• all cells in the Excel spreadsheet are text-formatted (not numeric or date)

• the first several cells (columns A, B, C, D and E) of the first row in the Excel spreadsheet

are reserved for values for submission type, submitter organization code, submission

date, MCI flag, and # of records to be transmitted; records should be batched as either

needing an MCI# “Y” or not needing an MCI# “N” - do not mix records

• the client data begins in the F column cell of the first and subsequent r ows

• the data columns in the Excel spreadsheet should be in the same order as the XML file

fields are ordered

There are 3 steps to this process:

1. Preparing Excel

2. Mapping and Exporting your Excel data

3. Final edits in Notepad

1. Preparing Excel in Office 2007 or above

The goal is to add a “Developer” tab in your menu ribbon.

In your menu ribbon, select File  Options

• Custom Ribbon. On the far right, check the box for Developer  OK

You will now have the De v eloper tab in your menu ribbon:

Mapping Your Source Data to XML

Wisconsin Department of Health Services 2

2. Mapping and Exporting your Excel data

Save the XML Sample File (Core_XML_Template.XML, AODA_XML_Template.XML,

MH_XML_Template.XML) sent to you with these instructions.

Create and format spreadsheet so that it contains the records you want to upload.

• Import or open the text or database file containing the records to upload in Excel and

use the file import wizard or copy and paste as appropriate. If you are using ‘Import

External Data’ wizard then do not forgot to uncheck checkbox for ‘Save Query definition’

in the External Data Range Properties box.

Open your edited Excel data sheet.

In Excel select the Developer tab  Source button. This will open an XML Source pane on the

right.

Select the XML Maps… button  Add button.

Mapping Your Source Data to XML

Wisconsin Department of Health Services 3

Locate the appropriate XML Sample File

(Core_XML_Template.XML,

AODA_XML_Template.XML,

MH_XML_Template.XML) you just saved,

highlight it, and select Open (located at bottom

of page). You will see a message about a

problem with the source. Select OK to have

Excel create a schema based on the source

data. You now have a map. Select OK.

Select the Options button and verify the two lower boxes

are checked for Automatically Merge Elements When

Mapping.

Click on the heading ns1:detail_record. It should highlight the details.

Then right-click on the heading and choose Map Element.

Map all the cells that contain data (detail only) e.g (F2:CFxx where xx

is the row number of your last record). Make sure to update the row to

match the last row containing data.

Your data sheet will change color. It will still be editable in case you need to do any last minute

edits. When you are ready, you will export the file. Select OK. You will see the following:

Mapping Your Source Data to XML

Wisconsin Department of Health Services 4

Choose Match element data type.

Choose the Developer tab  Export button.

Choose a file name and folder location for your export file (Program_AgencyID_Date_Time.xml) e.g.

(MH_109_20120615_0900.xml or CORE_109_20120615_0900.xml or AODA_109_20120615_0900.xml)

Select Export.

3. Final Edits

In Windows Explorer, locate your exported file. Right-Click  Open With  Notepad.

Delete the top approximately 2 header and submission rows.

Mapping Your Source Data to XML

Wisconsin Department of Health Services 5

Copy/paste in the header and submission rows from below:

<?xml version="1.0" e ncoding="UTF-8"?>

<submitter_organization_id>xxx</submitter_organization_id>

<submission_date>YYYY-MM-DD</submission_date>

<number_of_records_transmitted>x</number_of_records_transmitted>

<mci_flag>Y</mci_flag>

</header_record>

Header Editing:

In the header section between the begin > and </ end tags for each element fill in your:

• Submitter Organization ID (agency ID)

EX: <submitter_organization_id>fill in here</submitter_organization_id>

• Submission date (date you are submitting the file)

• Number of records transmitted (The number of detail records which is the count of the

number of <detail_record> lines).

• MCI Flag should be ‘Y’ or ‘N’ based on whether or not you are trying to obtain the receipt

id.

Save the Notepad file: File  Save (do not close the file)

Detail Record Editing:

Click on Edit  Replace. You’ll replace all <ns1: with <

then replace all </ns1: with </

Choose Replace All each time. Save the Notepad file: File  Save

Mapping Your Source Data to XML

Wisconsin Department of Health Services 6

You are now ready to log in to PPS FTP site and submit your xml file.

If you have any questions or problems, please contact the DHS SOS Desk at

DHSSOSHelp@dhs.wisconsin.gov

If you need immediate assistance, you can call the SOS Desk at 608-266-9198.