How to Generate XML from Database
- by Nisarg Mehta
Hi ,
I am fetching data from two tables CARRIER_IFTA ,IFTA_NAME.
My Select Query is like below..
SELECT t1.IFTA_LICENSE_NUMBER,t1.IFTA_BASE_STATE,t2.NAME_TYPE,t2.NAME
from CARRIER_IFTA t1 inner join IFTA_NAME t2
on t1.IFTA_LICENSE_NUMBER=t2.IFTA_LICENSE_NUMBER
My Data is coming in this way...
IFTA_LICENSE_NUMBER IFTA_BASE_STATE NAME_TYPE NAME
--------------------------------------------------------
630908333 US LG XYZ
630908333 US MG PQR
730908344 US LG ABC
Now using XSLT I want to generate XML like this
<T0019>
<IFTA_ACCOUNT>
<IFTA_LICENSE_NUMBER>630908333</IFTA_LICENSE_NUMBER>
<IFTA_BASE_STATE>US</IFTA_BASE_STATE>
<IFTA_NAME>
<NAME_TYPE>LG<NAME_TYPE>
<NAME>XYZ</NAME>
</IFTA_NAME>
<IFTA_NAME>
<NAME_TYPE>MG<NAME_TYPE>
<NAME>PQR</NAME>
<IFTA_NAME>
</IFTA_ACCOUNT>
<IFTA_ACCOUNT>
<IFTA_LICENSE_NUMBER>730908344</IFTA_LICENSE_NUMBER>
<IFTA_BASE_STATE>US</IFTA_BASE_STATE>
<IFTA_NAME>
<NAME_TYPE>LG<NAME_TYPE>
<NAME>ABC</NAME>
</IFTA_NAME>
</IFTA_ACCOUNT>
</T0019>
i have used below xslt but it is not giveng me desire result ...
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
<xsl:template match="/ROWSET">
<xsl:element name="T0019">
<xsl:apply-templates select="IFTAACCOUNT"/>
</xsl:element>
</xsl:template>
<xsl:template match="IFTAACCOUNT">
<xsl:element name="IFTAACCOUNT">
<xsl:apply-templates select="IFTA_CARRIER_ID_NUMBER"/>
</xsl:element>
</xsl:template>
<xsl:template match="IFTA_LICENSE_NUMBER">
<xsl:element name="IFTA_LICENSE_NUMBER">
<xsl:apply-templates />
</xsl:element>
</xsl:template>
<xsl:template match="IFTA_BASE_STATE">
<xsl:element name="IFTA_BASE_STATE">
<xsl:apply-templates />
</xsl:element>
</xsl:template>
<xsl:template match="IRP_NAME">
<IRP_NAME>
<xsl:apply-templates select="NAME"/>
<xsl:apply-templates select="NAME_TYPE"/>
</IRP_NAME>
</xsl:template>
<xsl:template match="NAME">
<xsl:element name="NAME">
<xsl:value-of select="." />
</xsl:element>
</xsl:template>
<xsl:template match="NAME_TYPE">
<xsl:element name="NAME_TYPE">
<xsl:apply-templates />
</xsl:element>
</xsl:template>
</xsl:stylesheet>
but it is not giving desire result ...
Please help me ...
Thanks in Advance...
