Using XSL Variable and Axes to format XML data into HTML having elements grouped by a tag

Following XSL converts the xml data into a format where first tag of the element is converted into heading and all elements having the same value for first tag will appear in a table below that heading. The Xml elements must be sorted by first tag.

To understand the transformation, consider the following Xml:

<mydata>
 <contact>
   <name>Mark</name>
   <telephone>34546343</telephone>
   <description>Work</description>
 </contact>
 <contact>
   <name>Mark</name>
   <telephone>98884748</telephone>
   <description>Home</description>
 </contact>=
 <contact>
   <name>Mark</name>
   <telephone>23456123</telephone>
   <description>Mobile</description>
 </contact>
 <contact>
   <name>William</name>
   <telephone>23098764</telephone>
   <description>Mobile</description>
 </contact>
 <contact>
   <name>William</name>
   <telephone>45645356</telephone>
   <description>Work</description>
 </contact>
</mydata>

The XSL generates the following output:

Contact: Mark

Telephone Description
34546343 Work
98884748 Home
23456123 Mobile

Contact: William

Telephone Description
23098764 Mobile
45645356 Work

XSL is following:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
 <xsl:template match="/">
 <html>
   <body>
     <xsl:for-each select="mydata/contact">
       <xsl:if test="name != preceding-sibling::contact&#91;1&#93;/name or position() = 1">

        <H3>
         Contact: <xsl:value-of select="name"/>
         </H3>

         <table border="1">
         <tr bgcolor="#9acd32">
           <th>Telephone</th>
           <th>Description</th>
         </tr>

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

        <xsl:variable name="curObject" select="name"/>
         <xsl:apply-templates select="following-sibling::contact&#91;name = $curObject&#93;"></xsl:apply-templates>


         </table>
       </xsl:if>
     </xsl:for-each>

  </body>
 </html>
 </xsl:template>

 <xsl:template match="contact">
   <tr>
     <td> <xsl:value-of select="telephone"/></td>
     <td> <xsl:value-of select="description"/></td>
   </tr>
 </xsl:template>
</xsl:stylesheet>

Excel Add-in for Exporting data to XML

Introduction

ExcelExportXML is a Microsoft Excel 2010 Add-in that generates XML data from excel sheet. It is developed in Visual Studio 2010 using C#.

It is a very simple add-in. To understand how to works, consider an excel sheet having following data.

Country Capital Continent
France Paris Europe
Germany Berlin Europe
India New Delhi Asia
Indonesia Jakarta Asia

Using the add-in, above can be exported to following Xml.

<sheet1>
    <row>
        <country>France</country>
        <capital>Paris</capital>
        <continent>Europe</continent>
    </row>
    <row>
        <country>Germany</country>
        <capital>Berlin</capital>
        <continent>Europe</continent>
    </row>
    <row>
        <country>India</country>
        <capital>New Delhi</capital>
        <continent>Asia</continent>
    </row>
    <row>
        <country>Indonesia</country>
        <capital>Jakarta</capital>
        <continent>Asia</continent>
    </row>
</sheet1>

Once the Add-in is installed, you will have a ‘Generate XML’ button on Add-ins tab of Excel Ribbon as shown below. Clicking ‘Generate XML’ will pop up the save file dialog which will allow you to save the generated Xml file.

ScreenShot.png

Use Case & Assumptions

ExcelExportXml is useful when you have tabular data in excel and the first row contains the column headings. It works based on following assumptions.

  1. First row is considered as column headers and will be converted to Xml tags.
  2. After encountering the first empty cell in header row, rest of the columns to the right will be ignored.
  3. Supports columns up to ‘Z’ only i.e. maximum of 26 columns.
  4. First row for which all values are empty will be considered the end of sheet.
  5. Sheet name and column names should not have any spaces.

Why not use standard Save As Xml functionality

Excel provides more than one ways to export xml data. One of them is ‘Save As Xml Data’ available in Save As dialog. This requires Xml Mappings to be defined which I believe requires developer Add-in from Microsoft to be installed.

Another option is ‘Save As Xml Spreedsheet 2003’ which generates following Xml.

<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="5" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:Width="51"/>
   <Column ss:Width="54"/>
   <Column ss:Width="51.75"/>
   <Row ss:StyleID="s64">
    <Cell><Data ss:Type="String">Country</Data></Cell>
    <Cell><Data ss:Type="String">Capital</Data></Cell>
    <Cell><Data ss:Type="String">Continent</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">France</Data></Cell>
    <Cell><Data ss:Type="String">Paris</Data></Cell>
    <Cell><Data ss:Type="String">Europe</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Germany</Data></Cell>
    <Cell><Data ss:Type="String">Berlin</Data></Cell>
    <Cell><Data ss:Type="String">Europe</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">India</Data></Cell>
    <Cell><Data ss:Type="String">New Delhi</Data></Cell>
    <Cell><Data ss:Type="String">Asia</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Indonesia</Data></Cell>
    <Cell><Data ss:Type="String">Jakarta</Data></Cell>
    <Cell><Data ss:Type="String">Asia</Data></Cell>
   </Row>
  </Table>
  </Worksheet>

As you can see, the above might not be what you want. It is more a representation of excel sheet than semantics of your data. ExcelExportXML is quite limited but effective in a common scenario where you have tabular data and the first row contains column headers.

Links

Writing Add-in for Microsoft Excel 2010 using Visual Studio 2010

Using Visual Studio 2010 Professional, creating Excel Add-in is pretty simple. Following are the steps I followed for writing an Add-In for exporting Xml:

Step # 1

Create new project of type ‘Excel 2010 Add-in’. The project will have a class file called ThisAddin.cs.

Step # 2

For creating a button on the Excel ribbon, right click on the project and select Add – > New Item.

Step # 3

Select ‘Ribbon (XML)’ from the list of new items and name the ribbon class, say Ribbon1. This will add two files to the project which are Ribbon1.cs and Ribbon1.xml.

Step # 4

Add the following code in ThisAddin class

protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
{
    return new Ribbon1();
}
Step # 5

In Ribbon1.xml define the properties of the button that will be displayed on the Excel ribbon. The onAction attribute specifies the method which will be called from Ribbon1.cs on clicking the button (onAction="OnTextButton").

 <?xml version="1.0" encoding="UTF-8"?>
	<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
	  <ribbon>
	    <tabs>
	      <tab idMso="TabAddIns">
	        <group id="MyGroup"
	               label="Export XML">
	          <button id="textButton" label="Generate XML"
	             screentip="Export to XML" onAction="OnTextButton"
	             supertip="Export excel sheet to XML file."/>
	        </group>
	      </tab>
	    </tabs>
	  </ribbon>
	</customUI>
Step # 6

Add following method declaration to Ribbon1.cs and implement your functionality here.

public void OnTextButton(Office.IRibbonControl control)
{
    //TODO:Add your implementation here
} 

Links:

Passed IBM Exam 000-017: Foundations of Tivoli Process Automation Engine

Today, I took IBM Exam 017 (Tivoli Process Automation Engine). The exam focuses on how to configure IBM products based on Tivoli Process Automation Engine, particularly IBM Maximo EAM.

Mainly, I used IBM Red Book for preparation, it took about a week to go through it. Below are the quizzes, I prepared while going through the certification guide.

IBM Tivoli Process Automation Engine Questions, Part 10 – Reports Administration

Following questions are related to Reports Administration of Tivoli Process Automation Engine. They are prepared to assist in studying for certification test number 000-017 i.e. Foundations of Tivoli Process Automation Engine.

Choose one of the following options and leave your answers in a comment below:

  1. Reports can be previewed from Reports Administration application.
    • A – True
    • B – False
  2. Security settings for reports can be defined in:
    • A – Security Groups application
    • B – Security tab on Reports Administration application
  3. Before you import the report design file, you must import any associated library files. Which action from the Select Action menu should be used to perform this operation.
    • A – Import Library File
    • B – Import Supporting Files
    • C – Import Additional Files
  4. What should be done to prevent a report from fetching a huge number of records from database?
    • A – set the Max Record Limit parameter in Reports Administration
    • B – place the limit in the report code
    • C – set the Max Record Limit parameter in Organizations Application
  5. Is it possible to restrict a report to run as scheduled only?
    • A – Yes
    • B – No

IBM Tivoli Process Automation Engine Questions, Part 9 – Work Management

Following questions are related to Work Management Configuration of Tivoli Process Automation Engine. They are prepared to assist in studying for certification test number 000-017 i.e. Foundations of Tivoli Process Automation Engine.

Choose one of the following options and leave your answers in a comment below:

  1. A new work types can be added in which application.
    • A – Domains application
    • B – Organizations application
  2. Material Reservations are automatically cleared when Work Order status changes to:
    • A – Complete
    • B – Closed
    • C – Based on configuration in Organization application
  3. Report downtime dialog is not available in which application.
    • A – Tasks and Activities
    • B – Locations
    • C – Assets
    • D – Work Order
    • E – Quick Reporting
  4. Job Plans are defined at which level
    • A – System level but Organization and Site can also be specified
    • B – Organization level
    • C – Site level
  5. Which of the following is not a Job Plan status.
    • A – Active
    • B – Pending
    • C – Draft
    • D – Inactive
  6. Activities can be created in which application.
    • A – Incident and Problem
    • B – Work Order
    • C – Activities and Tasks application
  7. Assignment Manager can record the time spent by a labor on a task. This is done using actions:
    • A – Start, Interrupt, Finish
    • B – Start, Pause, Stop
    • C – Start, Complete
  8. Job Plans can be nested to create a hierarchy.
    • A – True
    • B – False

IBM Tivoli Process Automation Engine Questions, Part 8 – Start Center

Following questions are related to Start Center Configuration of Tivoli Process Automation Engine. They are prepared to assist in studying for certification test number 000-017 i.e. Foundations of Tivoli Process Automation Engine.

Choose one of the following options and leave your answers in a comment below:

  1. If more than one Start Centers are displayed to the user as tabs, how a Start Center can be made default.
    • A – Using settings in Security Groups application
    • B – By modifying Start Center template
    • C – Using Display Settings on Start Center
  2. To create a KPI, user must define:
    • A – Select statement, Caution Level, Alert Level
    • B – Select statement, Caution Level, Alert Level, Target
    • C – Caution Level, Alert Level, Target Level
  3. To keep the KPI data currect:
    • A – Out of the box Cron Task called KPICronTask must be scheduled
    • B – No additional configuration is required
  4. Create New Start Center Template option is available in:
    • A – Security Groups application
    • B – Layout and Configuration options in Start Center
    • C – Application Designer
    • D – Database Configuration
  5. Which of the following tabs is not available on Result Set setup.
    • A – Available Queries
    • B – Available Columns
    • C – Column Display
    • D – Display Options
    • E – Chart Options
  6. Color alert can be added to a Result Set portlet using which setup tab.
    • A – Available Queries
    • B – Available Columns
    • C – Column Display
    • D – Display Options
    • E – Chart Options

IBM Tivoli Process Automation Engine Questions, Part 7 – Migration Manager

Following questions are related to Migration Manager of Tivoli Process Automation Engine. They are prepared to assist in studying for certification test number 000-017 i.e. Foundations of Tivoli Process Automation Engine.

Choose one of the following options and leave your answers in a comment below:

  1. The batch size of a package is the number of records that are retrieved at one time from each object in a migration group. The default value is:
    • A – 100
    • B – 1000
    • C – 5000
  2. A Package consists of following
    • A – Package Manifest, Configuration content, Compiled sources
    • B – Package Manifest, Structural Configuration content, Non-structural Configuration content, Compiled sources
    • C – Package Manifest, Structural Configuration content, Non-structural Configuration content, Compiled sources, History Data
    • D – Package Manifest, Package Metadata, Structural Configuration content, Non-structural Configuration content, Compiled sources, History Data
  3. Packages types are:
    • A – Snapshot, History
    • B – Snapshot, Change
    • C – Definition, Change
  4. Migration Manager identifies each environment unique with the help of three parameters which are:
    • A – Database connection string, host name and schema name
    • B – Database host name, database identifier and port number
    • C – Database host name, database identifier and schema name
  5. Content is migrated from source to target in stages which are:
    • A – Create, Approve, Deploy
    • B – Define, Create, Distribute
    • C – Define, Create, Deploy
    • D – Define, Create, Distribute and Deploy
  6. How many packages can be deployed at a time.
    • A – 1
    • B – 2
    • C – 10
    • D – There is no limit

IBM Tivoli Process Automation Engine Questions, Part 6 – Integration Framework

Following questions are related to Integration Framework of Tivoli Process Automation Engine. They are prepared to assist in studying for certification test number 000-017 i.e. Foundations of Tivoli Process Automation Engine.

Choose one of the following options and leave your answers in a comment below:

  1. The building block of Integration Framework is:
    • A – Publish Channels
    • B – Invocation Channels
    • C – Enterprise Services
    • D – End Points
    • E – Object Structures
  2. An Integration Object can include any or all of the following fields:
    • A – Persistent, Non-persistent
    • B – Persistent, Non-persistent and User-defined
    • C – Persistent and User-defined
  3. Which of following is asynchronous channel
    • A – Publish
    • B – Invocation
    • C – Both
  4. Which of the following events doesn’t initiate Publish Channel processing:
    • A – Object events (insert, update and delete)
    • B – Application initiated calls
    • C – Data Import
    • D – Data Export
  5. Publish Channels cannot use which of the following to map XML to external system XML.
    • A – User Exits Class
    • B – Processing Class
    • C – XSL Map
    • D – JMS Queue
  6. Processing rules can be used to filter records from Object Structure
    • A – True
    • B – False
  7. Which of the following can initiate Invocation Channel processing:
    • A – Object events (insert, update and delete)
    • B – Application initiated calls
    • C – Custom Java Action Class
    • D – Data Export
  8. Enterprise services are triggered by which of the following:
    • A – Scheduled Cron Tasks
    • B – Scheduled Cron Tasks, User initiated queries, Data Import
    • C – Scheduled Cron Tasks, Data Import
  9. End Points are required by transactions which are:
    • A – Inbound
    • B – Outbound