Archive for March, 2017

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/"
  xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">
  <metadata>
    <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"/>
  </metadata>
  <data>
    <row>
      <value>172.16.0.26</value>
      <value>server1</value>
      <value>1433</value>
      <value>MSSQLSERVER</value>
      <value>Microsoft SQL Server 2008</value>
    </row>
    <row>
      <value>172.16.0.31</value>
      <value xs:nil="true" />
      <value>1434</value>
      <value></value>
      <value>Microsoft SQL Server Redirector</value>
    </row>
  </data>
</dataset>

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"
  xmlns:cognos="http://developer.cognos.com/schemas/xmldata/1/">
    <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]" />
            </xsl:element>
          </xsl:for-each>
        </xsl:element>
      </xsl:for-each>
    </xsl:element>
  </xsl:template>
</xsl:stylesheet>
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"?>

<DataSet>
  <Row>
    <IP_Address>172.16.0.26</IP_Address>
    <Host>server1</Host>
    <Port>1433</Port>
    <Instance>MSSQLSERVER</Instance>
    <Database_Type>Microsoft SQL Server 2008</Database_Type>
  </Row>
  <Row>
    <IP_Address>172.16.0.31</IP_Address>
    <Host></Host>
    <Port>1434</Port>
    <Instance></Instance>
    <Database_Type>Microsoft SQL Server Redirector</Database_Type>
  </Row>
</DataSet>

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
http://code.dblock.org/2010/11/10/ibm-cognos-bi-generic-xslt-normalization-for-table-data.html
Advertisements