Re: [xsl] Getting node values from different xpath for creating SQL insert statements

["Mukul Gandhi" <gandhi.mukul@xxxxxxxxx>]

Hi Tariq,
    From your problem description, it seems, you are having problem
considering <StartTime> and <EndTime> in the query.

I think following technique should help ...

<xsl:template match="/">
  <xsl:variable name="startTime" select="Report/Header/Row/StartTime" />
  <xsl:variable name="endTime" select="Report/Header/Row/EndTime" />

  <!--
    Write as it is in your code
  -->

   <xsl:variable name="Columns" select="concat('starttime, endtime, ',
$Columns)" />
   <xsl:variable name="Values" select="concat(&quot;&apos;&quot;,
$startTime, &quot;&apos;&quot;, &quot;&apos;&quot;, $endTime,
&quot;&apos;&quot;, ', $Values)" />

   insert into table
               (<xsl:value-of select="$Columns"/>)
               values
               (<xsl:value-of select="$Values"/>);

  </xsl:for-each>

</xsl:template>

PS: This is not tested. Hope, I have understood your query ...

On Feb 8, 2008 8:01 AM, Tariq Ahsan <tariqahsan@xxxxxxxxx> wrote:
> Hello,
>
> I borrowed a script from one the postings and tweaked
> a little bit to work for my need. But the problem I
> have is I need to grab to separate node values outside
> of the xpath I am getting rest of the data.
>
> Here's the input xml file -
>
> <?xml version="1.0" encoding="utf-8" ?>
> - <Report>
> - <Header>
> - <Row>
>  <StartTime>06/02/2008 11:13:36</StartTime>
>  <EndTime>02/06/2008 11:13:36</EndTime>
>  </Row>
>  </Header>
> - <Data>
> - <Row>
>  <Col1>1</Col1>
>  <Col2>abc</Col2>
>  <Col3>xyz</Col3>
>  </Row>
> - <Row>
>  <Col1>2</Col1>
>  <Col2>def</Col2>
>  <Col3>ijk</Col3>
>  </Row>
> - <Row>
>  <Col1>3</Col1>
>  <Col2>efg</Col2>
>  <Col3>uvw</Col3>
>  </Row>
> -
>
>
>
> Here's the xslt I currently have -
>
> <?xml version="1.0" encoding="UTF-8"?>
> <xsl:stylesheet version="1.0"
> xmlns:xsl="http://www.w3.org/1999/XSL/Transform";>
>    <xsl:output method="xml" version="1.0"
> encoding="UTF-8" indent="yes"/>
>        <xsl:template match="/">
>           <xsl:for-each select="Report/Data/Row">
>                <xsl:variable name="Columns">
>                        <xsl:call-template name="getValues">
>                                <xsl:with-param name="V" select="."/>
>                                <xsl:with-param name="T" select="'Columns'"/>
>                        </xsl:call-template>
>                </xsl:variable>
>                <xsl:variable name="Values">
>                <xsl:call-template name="getValues">
>                        <xsl:with-param name="V" select="."/>
>                        <xsl:with-param name="T" select="'Values'"/>
>                </xsl:call-template>
>                                </xsl:variable>
>
>                insert into table
>                (<xsl:value-of select="$Columns"/>)
>                values
>                (<xsl:value-of select="$Values"/>);
>
>          </xsl:for-each>
>
>        </xsl:template>
>
>        <xsl:template name="getValues"> <xsl:param name="V"/>
> <xsl:param name="T"/>
>                <xsl:variable name="Values">
>                        <xsl:call-template name="getValueString">
>                                <xsl:with-param name="V" select="$V"/>
>                                <xsl:with-param name="T" select="$T"/>
>                        </xsl:call-template>
>                </xsl:variable>
>                <xsl:choose>
>                        <xsl:when
>
> test="normalize-space(substring(normalize-space(string($Values)),string-length(normalize-space(string($Values))),1))=','">
>                                <xsl:value-of
>
> select="substring(normalize-space(string($Values)),1,string-length(normalize-space(string($Values)))-1)"/>
>                        </xsl:when>
>                        <xsl:otherwise>
>                                <xsl:value-of
> select="normalize-space(substring(normalize-space(string($Values)),string-length(normalize-space(string($Values))),1))"/>
>                        </xsl:otherwise>
>                </xsl:choose>
>        </xsl:template>
>
>        <xsl:template name="getValueString">
>                <xsl:param name="V"/>
>                <xsl:param name="T"/>
>                <xsl:for-each select="$V/*[name(.) != not(node())]">
>                <!-- <xsl:for-each select="$V/*[name(.)]"> -->
>                        <xsl:choose>
>                                <xsl:when test="$T='Columns'">
>                                        <xsl:value-of select="name(.)"/>,
>                                </xsl:when>
>                                <xsl:otherwise>
>                        '<xsl:value-of select="."/>',
>                        </xsl:otherwise>
>                        </xsl:choose>
>                </xsl:for-each>
>                <xsl:for-each select="$V/*[name(.) = not(node())]">
>                                <xsl:choose>
>                                        <xsl:when test="$T='Columns'">
>                                                <xsl:value-of select="name(.)"/>,
>
>                                        </xsl:when>
>                                        <xsl:otherwise>
>                                                '<xsl:value-of select="."/>',
>                                                <!-- '<xsl:value-of select="NULL"/>',-->
>                                        </xsl:otherwise>
>                                </xsl:choose>
>
>
>                </xsl:for-each>
>        </xsl:template>
>
> </xsl:stylesheet>
>
>
> Need to have a file output that would contain -
>
> insert into table (starttime, endtime, col1, col2,
> col3) values ('06/02/2008 11:13:36', '06/02/2008
> 11:13:36', 1, 'abc', 'xyz');
>
> and so forth.
>
> Would appreciate greatly if I could get some tips to
> solving this problem.
>
> Thanks
>
> Tariq


-- 
Regards,
Mukul Gandhi