CODEWITHSUNDEEP
xmlcsvxslt
Prasad Kamdi
Prasad Kamdi

Reputation: 184

XML to CSV using XSLT help required

I am trying to convert xml to csv. Please help me to get correct XSL file

Output I am looking as (csv)==>

GAS_DAY     CRT_DT      CRT_TM     TOT_FCAST    CNG_SNCE_LST_NM  NA_RNM_UPDTD
10/02/2017  09/02/2017  11:01:21    13847690    99                 Y   
10/02/2017  09/02/2017  13:01:59    13780670    -0.5               Y   
10/02/2017  09/02/2017  16:01:03    13889719    0.8                Y   
10/02/2017  10/02/2017  00:01:07    13862376    -0.2               Y   
10/02/2017  10/02/2017  10:01:01    13679133    -1.3               Y   
11/02/2017  10/02/2017  11:01:21    338473240   99                 Y   
11/02/2017  10/02/2017  13:01:59    13780670    10.5               Y   
11/02/2017  10/02/2017  16:01:03    23889719    3.8                Y   
11/02/2017  11/02/2017  00:01:07    13432376    10.2               Y   
11/02/2017  11/02/2017  10:01:01    13432133    -1.3               Y

Sample XML ==>

<root>
    <SHPR_TOT_NRG_FCAST_OUT>
        <METADATA>
            <TMSTMP>2017-02-10T10:15:44</TMSTMP>
        </METADATA>
        <SHPR_TOT_NRG_FCAST_QRY>
            <GAS_DAY>2017-02-10</GAS_DAY>
        </SHPR_TOT_NRG_FCAST_QRY>
        <DTL>
            <NRG_FCAST_DTL>
                <CRT_DT>2017-02-09</CRT_DT>
                <CRT_TM>11:01:21</CRT_TM>
                <TOT_FCAST>13847690</TOT_FCAST>
                <CNG_SNCE_LST_NM>99.0</CNG_SNCE_LST_NM>
                <NA_RNM_UPDTD>Y</NA_RNM_UPDTD>
            </NRG_FCAST_DTL>
            <NRG_FCAST_DTL>
                <CRT_DT>2017-02-09</CRT_DT>
                <CRT_TM>13:01:59</CRT_TM>
                <TOT_FCAST>13780670</TOT_FCAST>
                <CNG_SNCE_LST_NM>-0.5</CNG_SNCE_LST_NM>
                <NA_RNM_UPDTD>Y</NA_RNM_UPDTD>
            </NRG_FCAST_DTL>
            <NRG_FCAST_DTL>
                <CRT_DT>2017-02-09</CRT_DT>
                <CRT_TM>16:01:03</CRT_TM>
                <TOT_FCAST>13889719</TOT_FCAST>
                <CNG_SNCE_LST_NM>0.8</CNG_SNCE_LST_NM>
                <NA_RNM_UPDTD>Y</NA_RNM_UPDTD>
            </NRG_FCAST_DTL>
            <NRG_FCAST_DTL>
                <CRT_DT>2017-02-10</CRT_DT>
                <CRT_TM>00:01:07</CRT_TM>
                <TOT_FCAST>13862376</TOT_FCAST>
                <CNG_SNCE_LST_NM>-0.2</CNG_SNCE_LST_NM>
                <NA_RNM_UPDTD>Y</NA_RNM_UPDTD>
            </NRG_FCAST_DTL>
            <NRG_FCAST_DTL>
                <CRT_DT>2017-02-10</CRT_DT>
                <CRT_TM>10:01:01</CRT_TM>
                <TOT_FCAST>13679133</TOT_FCAST>
                <CNG_SNCE_LST_NM>-1.3</CNG_SNCE_LST_NM>
                <NA_RNM_UPDTD>Y</NA_RNM_UPDTD>
            </NRG_FCAST_DTL>
        </DTL>
    </SHPR_TOT_NRG_FCAST_OUT>
    <SHPR_TOT_NRG_FCAST_OUT>
        <METADATA>
            <TMSTMP>2017-02-10T10:15:44</TMSTMP>
        </METADATA>
        <SHPR_TOT_NRG_FCAST_QRY>
            <GAS_DAY>2017-02-11</GAS_DAY>
        </SHPR_TOT_NRG_FCAST_QRY>
        <DTL>
            <NRG_FCAST_DTL>
                <CRT_DT>2017-02-10</CRT_DT>
                <CRT_TM>11:01:21</CRT_TM>
                <TOT_FCAST>338473240</TOT_FCAST>
                <CNG_SNCE_LST_NM>99.0</CNG_SNCE_LST_NM>
                <NA_RNM_UPDTD>Y</NA_RNM_UPDTD>
            </NRG_FCAST_DTL>
            <NRG_FCAST_DTL>
                <CRT_DT>2017-02-10</CRT_DT>
                <CRT_TM>13:01:59</CRT_TM>
                <TOT_FCAST>13780670</TOT_FCAST>
                <CNG_SNCE_LST_NM>10.5</CNG_SNCE_LST_NM>
                <NA_RNM_UPDTD>Y</NA_RNM_UPDTD>
            </NRG_FCAST_DTL>
            <NRG_FCAST_DTL>
                <CRT_DT>2017-02-10</CRT_DT>
                <CRT_TM>16:01:03</CRT_TM>
                <TOT_FCAST>23889719</TOT_FCAST>
                <CNG_SNCE_LST_NM>3.8</CNG_SNCE_LST_NM>
                <NA_RNM_UPDTD>Y</NA_RNM_UPDTD>
            </NRG_FCAST_DTL>
            <NRG_FCAST_DTL>
                <CRT_DT>2017-02-11</CRT_DT>
                <CRT_TM>00:01:07</CRT_TM>
                <TOT_FCAST>13432376</TOT_FCAST>
                <CNG_SNCE_LST_NM>10.2</CNG_SNCE_LST_NM>
                <NA_RNM_UPDTD>Y</NA_RNM_UPDTD>
            </NRG_FCAST_DTL>
            <NRG_FCAST_DTL>
                <CRT_DT>2017-02-11</CRT_DT>
                <CRT_TM>10:01:01</CRT_TM>
                <TOT_FCAST>13432133</TOT_FCAST>
                <CNG_SNCE_LST_NM>-1.3</CNG_SNCE_LST_NM>
                <NA_RNM_UPDTD>Y</NA_RNM_UPDTD>
            </NRG_FCAST_DTL>
        </DTL>
    </SHPR_TOT_NRG_FCAST_OUT>
</root>

XSL I could progress is ==

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                              xmlns:fo="http://www.w3.org/1999/XSL/Format" >
<xsl:output method="text" omit-xml-declaration="yes" indent="no"/>
<xsl:template match="/">GAS_DAY,CRT_DT,CRT_TM,TOT_FCAST,CNG_SNCE_LST_NM,NA_RNM_UPDTD
<xsl:for-each select="//DTL//NRG_FCAST_DTL">
     <xsl:value-of select="concat(SHPR_TOT_NRG_FCAST_QRY//GAS_DAY,',',CRT_DT, ',' ,CRT_TM,',' ,TOT_FCAST,',' ,CNG_SNCE_LST_NM,',' ,NA_RNM_UPDTD,'
     ')"/>
        <xsl:text>&#xa;</xsl:text> <!-- Add a newline at the end of the record -->
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

Upvotes: 2

Views: 79

Answers (2)

miken32
miken32

Reputation: 42715

Because your for-each loop is staring inside the <NRG_FCAST_DTL> element, you need to select the sibling element using ... This works for me:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
    <xsl:output method="text" omit-xml-declaration="yes" indent="no"/>
    <xsl:template match="/">
        <xsl:text>GAS_DAY,CRT_DT,CRT_TM,TOT_FCAST,CNG_SNCE_LST_NM,NA_RNM_UPDTD&#xa;</xsl:text>
        <xsl:for-each select="//DTL/NRG_FCAST_DTL">
            <xsl:value-of select="concat(../../SHPR_TOT_NRG_FCAST_QRY/GAS_DAY,',',CRT_DT, ',' ,CRT_TM,',' ,TOT_FCAST,',' ,CNG_SNCE_LST_NM,',' ,NA_RNM_UPDTD, '&#xa;')"/>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

Upvotes: 2

michael.hor257k
michael.hor257k

Reputation: 117043

AFAICT, you need to change:

SHPR_TOT_NRG_FCAST_QRY//GAS_DAY

to:

../../SHPR_TOT_NRG_FCAST_QRY/GAS_DAY

Not sure what other changes you want to make. If you need to reformat the dates, then you'll want have a named template you can call for that.

Upvotes: 3

Related Questions