Unix:- How to convert IBM Cognos generated XML to a readable XML format for ETL tools

Posted: March 31, 2017 in XML

I have the below XML file, need convert it to a ETL readable format.

<?xml version="1.0" encoding="utf-8"?>
<dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/"
    <item name="IP Address" type="xs:string" length="102"/>
    <item name="Host" type="xs:string" length="512"/>
    <item name="Port" type="xs:int" precision="1"/>
    <item name="Instance" type="xs:string" length="512"/>
    <item name="Database Type" type="xs:string" length="512"/>
      <value>Microsoft SQL Server 2008</value>
      <value xs:nil="true" />
      <value>Microsoft SQL Server Redirector</value>

This file can't be read in informatica ETL tool as such. 
we need to convert this file to a readable format for Informatica or any other ETL tools.
I am using the XSLT script to convert the above xml. 

<?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    <xsl:output method="xml" version="1.0" encoding="UTF-8" standalone="yes" indent="yes"/>
  <xsl:template match="/">
    <xsl:element name="DataSet">
      <xsl:for-each select="//*[name()='row']">
        <xsl:variable name="row" select="position()" />
        <xsl:element name="Row">
          <xsl:for-each select="//*[name()='item']">
            <xsl:variable name="elementName" select="@name" />
            <xsl:variable name="index" select="position()" />
            <xsl:element name="{translate($elementName,' ','_')}">
              <xsl:value-of select="//cognos:row[$row]/cognos:value[$index]" />
Save the above XSLT script file in the same directory where your source xml files are available.
use the below command for the conversion on UNIX machine.
xsltproc Tranform_XSLT.xml OP_JTable_Suppliers-en-us.xml > myresult.xml

Here’s the output.

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

    <Database_Type>Microsoft SQL Server 2008</Database_Type>
    <Database_Type>Microsoft SQL Server Redirector</Database_Type>

if the source XML is not in readable format then use the below command to
prettify the XML first.
xmllint -format OP_JTable_Suppliers-en-us.xml > xmllint_Result.xml
Special thanks Daniel Doubrovkine for the above code.

please refer the original link

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s