ETL and Data base topics

<?xml version="1.0" encoding="utf-8"?><datasetxmlns="http://developer.cognos.com/schemas/xmldata/1/"xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"><metadata><itemname="IP Address"type="xs:string"length="102"/><itemname="Host"type="xs:string"length="512"/><itemname="Port"type="xs:int"precision="1"/><itemname="Instance"type="xs:string"length="512"/><itemname="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><valuexs: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:stylesheetversion="1.0"xmlns:xsl="http://www.w3.org/1999/XSL/Transform"xmlns:cognos="http://developer.cognos.com/schemas/xmldata/1/"><xsl:outputmethod="xml"version="1.0"encoding="UTF-8"standalone="yes"indent="yes"/><xsl:templatematch="/"><xsl:elementname="DataSet"><xsl:for-eachselect="//*[name()='row']"><xsl:variablename="row"select="position()"/><xsl:elementname="Row"><xsl:for-eachselect="//*[name()='item']"><xsl:variablename="elementName"select="@name"/><xsl:variablename="index"select="position()"/><xsl:elementname="{translate($elementName,' ','_')}"><xsl:value-ofselect="//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