Importing Data Into XMod

XMod 4 makes it possible to import data into the XMod databases by creating a specially formatted XML file. This is the same format that is used when exporting data from XMod. The purpose of this topic is to help explain this format and provide help on getting your data into the format.

 

First, here's a look at the basic structure of an import file. This is stripped of its data and additional optional tags so we can focus on the basic components of the file.

 

<?xml version="1.0" encoding="UTF-8"?>

<xmodexport>

  <records>

    <record>

      <instancedata><![CDATA[your data goes here]]></instancedata>

    </record>

    <record>

      <instancedata><![CDATA[your data goes here]]></instancedata>

    </record>

  </records>

</xmodexport>

 

You'll see that it starts with the <?xml version="1.0" encoding="UTF-8"?> declaration tag. This is required and can be copied and pasted as-is into your file if necessary. The next tag is the <xmodexport> tag. This is called the root or parent tag. All other tags must be contained within it. It will also carry a number of important attributes (or properties) that describe the data the file contains. Within <xmodexport> we find the <records> tag. This is a container tag that holds the individual <record> tags we'll be importing. It does not have any attributes.  The <record> tag is where most of the data is contained. Each tag represents a single XMod record. The <record> tag also requires a number of attributes be set with valid values. It contains one tag, the <instancedata>.tag. This tag has no attributes. Its sole purpose is to contain the data for your form. Because XMod record data is in XML format, we need to use the special XML tag <![CDATA[ ]]>. While it looks complex, its only purpose is to prevent the XML processor from trying to interpret (and possibly misinterpret) our data. The data goes between the <![CDATA[ and the ]]>

 

Now, let's take a look at what a full file may look like - complete with tag attributes and data...

 

<?xml version="1.0" encoding="UTF-8"?>

<xmodexport version="4.0" generationdate="2006-02-27 22:52:04.984" portalid="0">

  <records>

    <record id="-1" formid="34" portalid="0" adduser="2" updateuser="2" approved="true" dateadded="2006-04-18 00:00:00.000" datemodified="2006-04-18 00:00:00.000" displaydate="2006-04-17 00:00:00.000" expirydate="9999-12-31 23:59:59.000">

      <instancedata><![CDATA[<instance>

        <FirstName>Abraham</FirstName>

        <LastName>Lincoln</LastName>

        <Address>123 Main St</Address>

        <City>Springfield</City>

        <State>IL</State>

        <Zip>62650</Zip>

        <FavoriteColor>

          <items>

            <item>

              <label>Red</label>

              <value>#FF0000</value>

            </item>

          </items>

        </FavoriteColor>

        </instance>]]>

      </instancedata>

    </record>

    <record id="-1" formid="34" portalid="0" adduser="2" updateuser="2" approved="true" dateadded="2006-04-18 00:00:00.000" datemodified="2006-04-18 00:00:00.000" displaydate="2006-04-17 00:00:00.000" expirydate="9999-12-31 23:59:59.000">

      <instancedata><![CDATA[<instance>

        <FirstName>John</FirstName>

        <LastName>Adams</LastName>

        <Address>234 Main St</Address>

        <City>Nothing</City>

        <State>AZ</State>

        <Zip>12345</Zip>

        <FavoriteColor>

          <items>

            <item>

              <label>Green</label>

              <value>#00FF00</value>

            </item>

          </items>

        </FavoriteColor>

        </instance>]]>

      </instancedata>

    </record>

    <record id="-1" formid="34" portalid="0" adduser="2" updateuser="2" approved="true" dateadded="2006-04-18 00:00:00.000" datemodified="2006-04-18 00:00:00.000" displaydate="2006-04-17 00:00:00.000" expirydate="9999-12-31 23:59:59.000">

      <instancedata><![CDATA[<instance>

        <FirstName>George</FirstName>

        <LastName>Washington</LastName>

        <Address>345 Main St</Address>

        <City>MyCity</City>

        <State>WA</State>

        <Zip>12345</Zip>

        <FavoriteColor>

          <items>

            <item>

              <label>Blue</label>

              <value>#0000FF</value>

            </item>

          </items>

        </FavoriteColor>

        </instance>]]>

      </instancedata>

    </record>

  </records>

</xmodexport>

 

This file contains 3 records from a form that collects name and address information. We'll break it down, now, into smaller, more manageable chunks...

 

<?xml version="1.0" encoding="UTF-8"?>

 

This is our required XML declaration. A standard fixture in most XML files.

 

<xmodexport version="4.0" generationdate="2006-02-27 22:52:04.984" portalid="0">

 

This is the opening tag of the required root tag, <xmodexport>. It contains 3 attributes. The first, version="4.0", indicates the format version. You should keep this at 4.0. In the future, if there are format changes, this version # will help XMod properly identify and process different versions. Next is generationdate="2006-02-27 22:52:04.984". This is the date the file was generated. It is added by XMod when exporting data. You can place whatever date you like in this attribute as it is ignored during import. Finally, you'll see the portal ID from which the data came.

 

Important Information on Date Formats

All dates in the XMod file are formatted in the same way. When you supply date values for tag attributes, your date must EXACTLY match the format you see here:

 

yyyy-MM-dd HH:mm:ss.fff

 

"yyyy" is the 4-digit year (1800 to 9999). This is followed by a dash and a 2-digit month (01 to 12), another dash, and a 2-digit day (01 to 31). This is then followed by the time component of the date. These are specified in 24-hour format with 12am being the "00" hour and 11pm being hour 23. So, we have the 2 digit hour (00 to 23), followed by a colon, the 2-digit minute (00 to 59), another colon, the 2-digit seconds (00 to 59), a decimal point, and then 3 digits (000 to 999) which represent fractions of a second. Obviously, you will probably never need the fractional second portion of this format. You may never need the seconds. In those cases, you can simply set them to 00 (for seconds) and 000 for fractional seconds.

 

Back to the File...

 

  <records>

    <record id="-1" formid="34" portalid="0" adduser="2" updateuser="2" approved="true" dateadded="2006-04-18 00:00:00.000" datemodified="2006-04-18 00:00:00.000" displaydate="2006-04-17 00:00:00.000" expirydate="9999-12-31 23:59:59.000">

 

This section of the file contains the opening <records> tag which signifies the start of a list of <record> tags. It is followed by the first <record> tag. There are two types of data that are stored with your record: the data your users enter via your XMod form and meta-data that provides additional information to XMod, helping it determine how the record should be treated. If you browse through the attributes, you'll see familiar bits of information such as id, formid, approved and others. All of these attributes are required and you should pay careful attention to make sure the values are correct and accurate. Invalid data here could result in data being overwritten in your XMod database when you import the file. Below is a list of the attributes:

 

id

This is the record's numeric identifier, or ID (also referred to as XModID). It is the value XMod uses to uniquely identify this record. In most cases, you'll want to supply the value "-1" to indicate this is a new record that doesn't have an ID and, thus, should be added as a new record. You would only supply another number if you wanted to update a record with new data. When you are updating, be sure you have the correct ID as well as the correct ID's for formid and portalid.

formid

This is the numeric identifier XMod uses internally to link this record with a data-entry form. Ensure this value is the same ID as an already existing form for the portal. Needless to say, the record's fields and data should match that expected by the associated form. If the form id is not found, the record will not be added.

portalid

This is the numeric identifier used by DotNetNuke to identify the portal.

updateuser

The DotNetNuke numeric identifier for the user who last modified the record. You should ensure the value points to a valid user id in the target site.

adduser

The DotNetNuke numeric identifier for the user who created the record. You should ensure the value points to a valid user id in the target site.

approved

If true the record has been approved to be displayed on the site. If false, the record has not been approved for display on the site and will require approval before being displayed.

dateadded

The date the record was added to the XMod data table. The date must be in the yyyy-MM-dd HH:mm:ss.fff format (see Date format explanation in this topic).

datemodified

The date the record was last updated in the XMod data table. If the record hasn't yet been modified then dateadded and datemodifed will be the same. The date must be in the yyyy-MM-dd HH:mm:ss.fff format (see Date format explanation in this topic).

displaydate

The first date the record can be displayed on the site (assuming content scheduling is enabled). XMod defaults to 1800-01-01 00:00:00.000 for this value.  The date must be in the yyyy-MM-dd HH:mm:ss.fff format (see Date format explanation in this topic).

expirydate

The first date the record can no longer be displayed on the site (assuming content scheduling is enabled). XMod defaults to 9999-12-31 23:59:59.000 for this value to indicate no expiration date.  The date must be in the yyyy-MM-dd HH:mm:ss.fff format (see Date format explanation in this topic). This value must be greater than the value in displaydate.

 

NOTES: updateuser and adduser may be the same value. In fact this is what XMod does normally when a user creates a new record. Additionally, the dateadded, datemodified, and displaydate attributes can contain the same value. However, setting the displaydate equal to the expirydate will result in your record never being displayed since that will tell XMod your record has expired at the very moment it's scheduled to be displayed.

 

      <instancedata><![CDATA[<instance>

        <FirstName>Abraham</FirstName>

        <LastName>Lincoln</LastName>

        <Address>123 Main St</Address>

        <City>Springfield</City>

        <State>IL</State>

        <Zip>62650</Zip>

        <FavoriteColor>

          <items>

            <item>

              <label>Red</label>

              <value>#FF0000</value>

            </item>

          </items>

        </FavoriteColor>

        </instance>]]>

      </instancedata>

 

The next bit of code contains the actual data entered via the XMod form. It begins with the <instancedata> tag and ends with the </instancedata> tag. In between is an XML representation of the data, enclosed in a CDATA construct (highlighted in red). The reason for this was explained earlier in the topic.

 

Formatting Your Form's Data

All XMod form data begins with the <instance> tag and ends with the </instance> tag. Within this, XMod stores data for two general types of fields - single-value fields such as text boxes and textareas, and multi-value fields which include single and multi-select list controls as well as custom XMod form controls. In both cases, the tag which identifies the field is the value of the form control's "ref" attribute. For single-value fields, the value is simply inserted between the opening and closing tags for that field. For multi-value fields, however, we add a few more child tags to define the list of selected items in the field. This always starts with the <items> tag and ends with the </items> tag. If no items have been selected, you would see the following empty tag: <items/>.  If one or more items have been selected, inside the <items> </items> tag pair will be an <item> </item> tag pair for each selected item. If only one item has been selected, then there will be only one <item> </item> tag pair. If two items were selected, there will be two <item> </item> tag pairs and so on. Within each <item> </item> tag pair you'll find a <label> </label> tag pair and a <value> </value> tag pair. The value in the <label> </label> tags is the text displayed to the user for the selected item. The value in the <value> </value> tags is the hidden value for the selected item.  I

 

To give you a better idea of how a form's controls (or fields) are mapped, below we've included a snippet from the XMod form definition. We're showing the definition for the First Name text box and a drop-down list for selecting a Favorite color.

 

<input ref="FirstName"><label>First Name</label></input>

<input ref="LastName"><label>Last Name</label></input>

...

<select1 ref="FavoriteColor" appearance="minimal">

  <label>Favorite Color</label>

  <items>

    <item><label>Red</label><value>#FF0000</value></item>

    <item><label>Green</label><value>#00FF00</value></item>

    <item><label>Blue</label><value>#0000FF</value></item>

  </items>

</select1>

 

 

Converting Your Data to XMod XML

Now that you have a better understanding of what goes into an import file, the question remains - How do you convert your data to XMod's XML format. The answer to that question is largely dependent on what format your data is in now and what conversion tools you have at your disposal. You can, of course, create your import file by hand, though for more than a few records this would be fairly tedious and error-prone. In most cases, you'll want a more automated mechanism.

 

If you are familiar with XSLT, that is usually the best approach. XSLT stands eXtensible Stylesheet Language Transformations. It is simply another type of XML file with some special tags that describe how to convert (or transform) from one XML file format to another. In more advanced usages, you can even transform other file types such as comma-delimited values, but that is beyond our example here.

 

To use XSLT, you'll need an XSLT processor which applies your XSLT file to an incoming data source (usually an XML file) and provides you with the results (typically a reformatted XML file). There are a number of free and for-purchase XSLT processors available on the internet. Some of them are stand-alone applications and others are embedded as tools into another application. Altova (http://www.altova.com) XMLSpy Home Edition is offered for free. It includes an XSLT processor. There are also a few open-source applications available.  If you own Access 2003 or later, you'll find that it also has a built-in processor. We'll use Access 2003 for this example.

 

A Sample Access 2003 XML File

When you export a table to XML using Access and an XSLT file, Access first creates an XML file which represents your data. Before you can create an XSLT file, you need to understand the format of the input data as well as the output. So, let's begin by looking at what our starting data looks like. For your table, you can see this file by performing these steps:

 

    1. Open your database.

    2. From the File menu, select Export...

    3. On the resulting dialog type the name you want to apply to the file

    4. Select XML in the Save as type drop-down list.

    5. Press the Export button. The "Export XML" dialog box appears. Ensure that only "Data (XML)" is checked.

    6. Press the More Options... button

    7. On the Data tab, select the table you wish to export.

    8. Click the OK button to perform the transformation. You can then open the resulting XML file in your XML or text editor or web browser to see its contents.

 

For our example table, the input XML that we'll be working with looks like this:

 

<?xml version="1.0" encoding="UTF-8"?>

<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2006-04-22T08:27:52">

  <Contacts>

    <FirstName>Abraham</FirstName>

    <LastName>Lincoln</LastName>

    <Address>123 Main St</Address>

    <City>Springfield</City>

    <State>IL</State>

    <Zip>62650</Zip>

  </Contacts>

  <Contacts>

    <FirstName>John</FirstName>

    <LastName>Adams</LastName>

    <Address>234 Main St</Address>

    <City>Adamstown</City>

    <State>WA</State>

    <Zip>12345</Zip>

  </Contacts>

  <Contacts>

    <FirstName>George</FirstName>

    <LastName>Washington</LastName>

    <Address>345 Main St</Address>

    <City>Washington</City>

    <State>DC</State>

    <Zip>34578</Zip>

  </Contacts>

</dataroot>

 

This sample data starts with the standard XML declaration:

 

<?xml version="1.0" encoding="UTF-8"?>

 

This is followed by the <dataroot> tag. All XML documents must have a starting tag - also called the root tag. The <dataroot> tag is what Access uses as it's root tag. All other tags will be contained within this tag. In XML parlance, they are children of this tag.

 

<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2006-04-22T08:27:52">

 

The <dataroot> tag also contains an XML "namespace" declaration (xmlns:od="urn:schemas-microsoft-com:officedata"). This is used to help work with Access data types, but we're unconcerned with them for our purposes. Finally, the tag contains  the date the file was generated. Again, this isn't that important for our purposes.

 

Within the <dataroot> tag you'll find a series of <Contacts> and </Contacts> tags. These are what Access uses to signify the start and end of each record. The actual tag name will vary, based on the name of your table. This name will become important to remember as we create our XSLT file. Inside each <Contacts> and </Contacts> tag pair, you'll find a series of tags that contain the data of your record. The tag names here are made from the column (or field) names in your table.

 

Generating An XMod Input File With XSLT

When working with XSLT, it's important to understand there are two parts to the process: First, you'll need to create the XSLT file. It is this file your XSLT processor will use as a formatting guide to create your XML file (the one you'll use to import your data into XMod). The second part consists of the steps you take within your XSLT processor to perform the transformation/conversion.

 

Part One: Creating the XSLT File

The first step in the process of generating our XMod XML import file is creating the XSLT file. You can create this file by hand in Notepad or other text editor. You may find it more helpful to use an XML editor, though. You should not create this file with a word processor or similar program since they include special hidden formatting codes that will interfere with the proper functioning of the XSLT.

 

Here's our example XSLT file - saved with the filename "ContactsConversion.xslt":

 

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:od="urn:schemas-microsoft-com:officedata">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
 <xsl:template match="/dataroot">
   <xmodexport version="4.0" generationdate="2006-02-27 22:52:04.984" portalid="0">
     <records>
       <xsl:apply-templates select="Contacts"></xsl:apply-templates>
     </records>
   </xmodexport>
 </xsl:template>
<xsl:template match="Contacts">
 <record id="-1" formid="34" portalid="0" adduser="2" updateuser="2" approved="true" dateadded="2006-04-18 00:00:00.000" datemodified="2006-04-18 00:00:00.000" displaydate="2006-04-17 00:00:00.000" expirydate="9999-12-31 23:59:59.000">
   <InstanceData><xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>
     <instance>
       <xsl:copy-of select="./*"></xsl:copy-of>
     </instance><xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>
   </InstanceData>
 </record>
</xsl:template>
</xsl:stylesheet>

 

You can copy and paste this into your editor to provide a starting point for your XSLT file. There are five sections of this code to which we'll draw your attention. Understanding what each part of the file does will increase your understanding and enable you to modify our sample XSLT to work with your Access table:

 

Section 1: The XSL Stylesheet Declaration

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:od="urn:schemas-microsoft-com:officedata">

 

The XML namespace highlighted above is Microsoft's namespace for describing Office data. Since our files aren't concerned with data types, we will be ignoring them. However, this namespace declaration should be included. This prevents the processor from adding the declaration to each of our form field tags (which we would then have to go back and delete).

 

Section 2: Matching Access' Starting Element

<xsl:template match="/dataroot">

 

This tells the processor to find the <dataroot> tag. This is the starting tag or root tag Access uses - much like our import file uses <xmodexport> as its root tag. All other tags will be children of this tag.

 

Section 3: Generating the Root Tag in the XMod XML Input File

<xmodexport version="4.0" generationdate="2006-02-27 22:52:04.984" portalid="0">

 

Although XMod doesn't use the Generation Date attribute on import, you can change it to a more appropriate date. You should ensure that the Portal ID attribute is a valid portal ID on the target machine.

 

Section 4: Selecting and Looping Through Each Record

<xsl:apply-templates select="Contacts"></xsl:apply-templates>

...

  <xsl:template match="Contacts">

 

As describe earlier in this topic, Access uses the name of your table as the name of the tag which delineates each record. In our case, the table name is Contacts. You'll need to change this to your table's name in two places - the <xsl:apply-templates> line and the <xsl:template match> line. Remember that XML is case-sensitive. If your table name is "contacts" then you should use "contact". If it's "Contacts", you should use "Contacts". Otherwise, your XSLT processor won't find a match and no records will be converted.

 

Section 5: Generating the Record

<record id="-1" formid="34" portalid="0" adduser="2" updateuser="2" approved="true" dateadded="2006-04-18 00:00:00.000" datemodified="2006-04-18 00:00:00.000" displaydate="2006-04-17 00:00:00.000" expirydate="9999-12-31 23:59:59.000">

 

The information above will be copied over directly to the resulting XML file. You should ensure that all the values are correct. If your table has additional fields that track the data added, date modified, etc., When you get more comfortable with XSLT you can use XSLT expressions such as <xsl:element> and <xsl:attribute> to gain greater and more automated control over grabbing data from your input XML file and placing that data in its proper place in the output XML file. If you don't have values, put some reasonable defaults it. All the attributes must be filled with valid values.

 

TIP: If you don't know good values for your formid, portalid, adduser, or updateuser, you can find this information fairly easily. Simply log into your DNN website as Host (SuperUser account) and add a few sample records via the target XMod form. Then, go to the XMod Administration page, click the Export tab and export the data for that form to an XML file. Open the XML file in your text or XML editor and look for the <record> tags like the one displayed above. You can use the values you find there. This will give you the correct formid and portalid. It will also give you the values for udateuser and adduser. These will be the user id of the Host account.

 

In most cases, everything else in our example XSLT code can be left as-is. Be sure to save your edits. The next step is to run Access and perform the XML export process.

 

Part 2: Creating the XML Import File Using Access' Export to XML Tool

As we discussed earlier, our sample data happens to be in Access 2003. Again, if you don't have Access, you can use one of the XSLT processors available for free or purchase on the internet.

 

In Access 2003, you can initiate this conversion as follows:

 

  1. Open your database and click on the table you want to export.

  2. From the File menu, select Export...

  3. On the resulting "Export Table" dialog type the name you want to apply to the XML file

  4. Select XML in the Save as type drop-down list.

  5. Press the Export button. The "Export XML" dialog box appears. Ensure that only the Data (XML) checkbox is checked. Everything else should be unchecked.

  6. Press the More Options... button

  7. On the Data tab, the table you selected earlier should be visible with a checked checkbox next to it. If not, select the table you wish to export by checking its checkbox.

  8. Click the Transforms... button. This is the step where we will integrate the XSLT file you created in the above process.

  9. On the "Export Transforms" dialog, press the Add... button to make it available to Access.

    Note that if you are actively editing and testing your XSLT transformation, you should remove your old file from the list first, then click the add button. Access seems to cache the XSLT file. This procedure helps ensure you're working with your latest version. If your XSLT file is not shown, click the Add button to make it available to Access.
     

  10. In the "Add New Transform" dialog, browse to and select the XSLT file you created and click the Add button.

  11. Select your XSLT file from list of Transforms in the "Export Transforms" dialog and click the OK button.

  12. Back on the "Export XML" dialog, click the OK button to perform the transformation.

  13. Open the resulting XML file in your text or XML editor and inspect its contents. It's important the information be accurate and the XML matches what XMod expects.

  14. Next,  in your output file, remove xmlns:od="urn:schemas-microsoft-com:officedata" from the <xmodexport> tag.

  15. Finally, your file is ready to import. IMPORTANT: You should backup your database before performing any import. Otherwise there will be no way to restore your original data should a problem occur.

  16. Once you've backed up your database, you can login to your DNN site as Host (SuperUser Account), go to the XMod Administration page and click the Import tab.

  17. Browse to and select your import file

  18. Configure the import options according to your needs

  19. Click the Import link. A progress bar will appear to let you know the status of your import. After the data import has been completed, any errors will be reported to you on the progress bar.

 

 

More Advanced XSLT Techniques

Once you've gotten comfortable with a basic conversion, you may find yourself asking a few questions:

 

  1. How do I remove unwanted fields in my original data source?

  2. How do I change the names of fields to match the field names in my XMod form?

  3. How do I convert an on/off or checkbox type field to XMod's format for multi-value fields so the value will render correctly in a checkbox on the XMod form?

  4. How do I take a series of values from one field and turn them into a list for XMod?

 

These are all good questions that can be resolved with the right XSLT techniques. Now, this topic can't teach you all about XSLT. There are many good books and web resources that can help in that arena. What we'll do here is show you some examples of XSLT that you can use and modify to fit your needs.

 

Question 1: How do I remove unwanted fields in my original data source?

Input XML

Output XML

...

<ProductId>A763jd</ProductId>

<UnwantedField>123</UnwantedField>

<ProductName>Widget Mini</ProductName>

...

...

<ProductId>A763jd</ProductId>

<ProductName>Widget Mini</ProductName>

...

 

 

In our previous XSLT sample we simply copied everything we found for each record with this:

 

    <InstanceData><xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>
    <instance>
      <xsl:copy-of select="./*"></xsl:copy-of>
    </instance><xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>
   </InstanceData>

 

The <xsl:copy-of> tag (highlighted in red above) essentially sucks in everything - tags and attributes and values - and copies them out to our destination file. In the example we've worked with in this topic, that was fine. We simply copied over all the fields that were made available from the Access XML file.

 

Now, though, we'll need to be a bit more picky about what fields we copy over. This means we'll be replacing the <xsl:copy-of> tag with our new, pickier code. For our example, we'll assume we have a field named "UnwantedField" that, unsurprisingly, we don't want to be copied to our destination XML file. But we do want all the other fields:

 

    <InstanceData><xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>
   <instance>
         <xsl:for-each select="node()">

            <xsl:choose>

              <xsl:when test="name()='UnwantedField'"></xsl:when>

              <xsl:otherwise>

                <xsl:copy-of select="."></xsl:copy-of>

              </xsl:otherwise>

            </xsl:choose>

          </xsl:for-each>
   </instance><xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>
  </InstanceData>

 

Here we're using the <xsl:for-each> tag to loop through all the nodes (select="node()") in each record. In our case, the nodes are fields in the original data source. We then perform a test on each node. For that, we use the <xsl:choose> tag (which contains a few child tags. If you're familiar with VB.NET, you can think of this like the Select/Case construct or, in C# and Javascript, the switch construct. Each <xsl:when> tag is a test that is performed on the current node (field).

 

<xsl:when test="name()='UnwantedField'"></xsl:when>

 

This tag tests the name of the node (i.e. the name of our field) with the name() function and tests to see if that name equals UnwantedField. Note, the field name you want should be enclosed in single quotes, as in the example. If that test evaluates to true, then the XSLT processor will output whatever is between the opening <xsl:when> and closing </xsl:when>. In our case we don't want anything copied to the destination file there is nothing between them. This is the same as saying to the XSLT processor, "don't copy the field with the name UnwantedField.

 

You can add more <xsl:when> tags to test and remove other unwanted fields. Simply add them like you see below:

 

...

<xsl:when text="name()='UnwantedField'"></xsl:when>

<xsl:when text="name()='AnotherUnwantedField'"></xsl:when>

<xsl:otherwise> ...

 

The final bit of code that make all this work is the <xsl:otherwise>

 

<xsl:otherwise>

  <xsl:copy-of select="."></xsl:copy-of>

</xsl:otherwise>

 

This bit tells the XSLT processor that if none of the tests (<xsl:when>) on the node (i.e. the field) evaluate to true, then copy the node over using the <xsl:copy-of> tag (which you'll recall from our original example) from our original example.

Question 2: How do I change the names of fields to match the field names in my XMod form?

Input XML

Output XML

...

<Product_x0020_Id>A763jd</Product_x0020_Id>

...

...

<ProductId>A763jd</ProductId>

...

 

If you haven't already done so, work through Question 1 as we'll be using some of the same ideas in this example, plus adding a few more.

 

Many times, the names of the fields in your data source don't match what the field names are in your XMod form. In these cases, you need to modify those field names. For instance, in Access, you're allowed to have a field name with spaces: "Product Id". Well, this is an invalid name in XML (and in XMod), so when Access creates its XML file, it changes the name to "Product_x0020_Id". The "_x0020_" signifies the space character. Let's assume, for this example, that the XMod form is expecting the field "ProductId". Our task then is to convert our field names from "Product_x0020_Id" to "ProductId".

 

For this we'll be using the <xsl:for-each> tag to loop through the fields in the record as we did in Question 1. We'll also be using the <xsl:choose> and its child tags to look for the field in question (like we did in Question 1). What we're doing differently here, though, is using two new tags: <xsl:element> and <xsl:value-of> - which we'll describe below. First, here's what the relevant code snippet looks like:

 

      <InstanceData><xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>

        <instance>

          <xsl:for-each select="node()">

            <xsl:choose>

              <xsl:when test="name()='Product_x0020_Id'">

                <xsl:element name="Product_Id">

                  <xsl:value-of select="."></xsl:value-of>

                </xsl:element>

              </xsl:when>

              <xsl:otherwise>

                <xsl:copy-of select="."></xsl:copy-of>

              </xsl:otherwise>

            </xsl:choose>

          </xsl:for-each>

        </instance><xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>

      </InstanceData>

 

In the highlighted code above, you'll see we're using the <xsl:when> tag to test the name of the node (i.e. the field's name). If it is, we use a new tag: <xsl:element> to create a tag in our output XML file with the name "ProductId". In other words we're saying: If you find this: <Product_x0020_Id>, output this: <ProductId> instead.  

 

Of course, it won't do to just create the tag, we need to copy over its contents. For this, we use another new tag: <xsl:value-of>. It does as its name implies. It copies the value of whatever you select which, in this case, is the current node (signified by the select="." attribute). The end result is that the <Product_x0020_Id> field tag is transformed into <ProductId> and its value is copied over as well.

 

Question 3: How do I convert an on/off or checkbox type field to XMod's format for multi-value fields so the value will render correctly in a checkbox on the XMod form?

Input XML

Output XML

...

<IsActive>Y</IsActive>

...

...

<IsActive>

  <items>

    <item>

      <label>Active</label>

      <value>Y</value>

    </item>

  </items>

</IsActive>

...

 

Many data bases have fields which are either on or off, yes or no, active or inactive, etc. In XMod you would represent these as checkboxes. To make a single checkbox control, you would create a checkbox list, but only include one item, like so:

 

...

<select ref="IsActive" appearance="full">

  <label>Is Record Active?</label>

  <items>

    <item>

      <label>Active</label>

      <value>Y</value>

    </item>

  </items>

</select>

...

 

When stored in the database, this field would look like:

 

...

<IsActive>

  <items>

    <item>

      <label>Active</label>

      <value>Y</value>

    </item>

  </items>

</IsActive>

...

 

OR, if the item weren't checked:

 

...

<IsActive>

  <items/>

</IsActive>

...

 

SO, we need to modify our XSLT to output one or the other form, based on the value in our table or input XML file.

 

      <InstanceData><xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>

        <instance>

          <xsl:for-each select="node()">

            <xsl:choose>

              <xsl:when test="name()='IsActive'">

                <xsl:element name="IsActive">

                  <xsl:choose>

                    <xsl:when test="text()='Y'">

                      <items>

                        <item>

                          <label>Active</label>

                          <value>Y</value>

                        </item>

                      </items>

                     </xsl:when>

                     <xsl:otherwise>

                       <items/>

                     </xsl:otherwise>

                  </xsl:choose>

                </xsl:element>

              </xsl:when>

              <xsl:otherwise>

                <xsl:copy-of select="."></xsl:copy-of>

              </xsl:otherwise>

            </xsl:choose>

          </xsl:for-each>

        </instance><xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>

      </InstanceData>

 

What we've done in the code above is to insert an <xsl:when> tag to match the <IsActive> tag in our Input XML. Then, we include another <xsl:choose> construct (and it's child tags). The first <xsl:when> tag tests to see if the value of the <IsActive> tag is "Y", indicating the record is active. It does this by using the text() function, which gets the text of the current node (in this case the <IsActive> tag.

 

If <IsActive> is Y then we create the <items> ... </items> structure you see. It's important that we match the label value ("Active") with the label used in our XMod form for the item - highlighted in red below (not to be confused with the label we use for the control). Most importantly, though, is that we match the value exactly (highlighted in blue below. Because, XMod will look for a value of "Y" and, if it isn't found, it won't place a checkmark in the checkbox.

 

<select ref="IsActive" appearance="full">

  <label>Is Record Active?</label>

  <items>

    <item>

      <label>Active</label>

      <value>Y</value>

    </item>

  </items>

</select>

 

Back to our XSLT. Of equal importance is this bit of code:

 

<xsl:otherwise>

  <items/>

</xsl:otherwise>

 

This tells the XSLT processor that if we don't find a value of "Y" in the <IsActive> tag, then we should output an empty <items/> tag. When XMod sees this, it will indicate no items have been selected, thus ensuring that the checkbox is unchecked.

 

 

Question 4: How do I take a series of values from one field and turn them into a list for XMod?

Input XML

Output XML

 

Record 1

...

<Color>Red</Color>

...

 

Record 2

...

<Color>Green</Color>

...

 

 

Record 1

...

<Color>

  <items>

    <item>

      <label>Red</label>

      <value>#FF0000</value>

    </item>

  </items>

</IsActive>

...

...

Record 2

...

<Color>

  <items>

    <item>

      <label>Green</label>

      <value>#00FF00</value>

    </item>

  </items>

</Color>

...

 

 

 

Database tables are inherently "flat". Meaning you each record has a series of fields, each with a single value. However, XMod allows you to assign multiple values to a single field. This is a bit tricky, but the solution isn't much different than our solution in Question 3.

 

Let's assume that we have a checkbox list in our XMod form like so:

 

<select1 ref="Color" appearance="full">

  <label>Favorite Color</label>

  <items>

    <item>

      <label>Red</label>

      <value>#FF0000</value>

    </item>

    <item>

      <label>Green</label>

      <value>#00FF00</value>

    </item>

    <item>

      <label>Blue</label>

      <value>#0000FF</value>

    </item>

  </items>

</select1>

 

This radio button list defines 3 different colors that can be selected. Each has a human readable display text (Red, Green, Blue) as well as a hexadecimal version of that color, suitable for including in a web page, for instance. Our XSLT will look like this:

 

      <InstanceData><xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>

        <instance>

          <xsl:for-each select="node()">

            <xsl:choose>

              <xsl:when test="name()='Color'">

                <xsl:element name="Color">

                  <xsl:choose>

                    <xsl:when test="text()='Red'">

                      <items>

                        <item>

                          <label>Red</label>

                          <value>#FF0000</value>

                        </item>

                      </items>

                     </xsl:when>

                    <xsl:when test="text()='Green'">

                      <items>

                        <item>

                          <label>Green</label>

                          <value>#00FF00</value>

                        </item>

                      </items>

                     </xsl:when>

                    <xsl:when test="text()='Blue'">

                      <items>

                        <item>

                          <label>Blue</label>

                          <value>#0000FF</value>

                        </item>

                      </items>

                     </xsl:when>

                     <xsl:otherwise>

                       <items/>

                     </xsl:otherwise>

                  </xsl:choose>

                </xsl:element>

              </xsl:when>

              <xsl:otherwise>

                <xsl:copy-of select="."></xsl:copy-of>

              </xsl:otherwise>

            </xsl:choose>

          </xsl:for-each>

        </instance><xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>

      </InstanceData>

 

 

This code shouldn't need much explanation. It is essentially the same as in Question 3. In this case we've used 3 <xsl:when> tags to test for each possible color. When found, the XSLT outputs the tags necessary to indicate the selected color. Since our original data source only stores the color name, we must supply the color value. We're also hard-coding the color name (in the <label> </label> tags of the <item>). You could replace that with text() to avoid hard-coding the value, but this method ensures the <label> and <value> tags contain valid values. If you are familiar with XSLT or start to do some digging in the manuals, you'll discover there are more compact ways of doing this using named templates or other methods. However, for our purposes, it was decided to go with a more verbose and hopefully explanatory solution.