apply xsl on xml to generate sql   

Hi All,

I have this XML files here:

<?xml version="1.0" encoding="utf-8">
<?xml-stylesheet type="text/xsl" href="TimesheetSQLScript.xsl"???>

<myfields>
     <timesheetid>
          <companycode>CCCRM</companycode>
          <employee>Adrian</employee>
          <weekpick>2008-09-15</weekpick>
          <startdate>2008-09-15</startdate>
          <note></note>
          <enddate>2008-09-21</enddate>

          <monday>
               <mondate>2008-09-15</mondate>
               <moncheck>
                    <moncheckin>14:10:00</moncheckin>
                    <moncheckout>15:44:00</moncheckout>
               </moncheck>
               <moncheck>
                    <moncheckin>16:44:00</moncheckin>
                    <moncheckout>22:50:00</moncheckout>
               </moncheck>
          </monday>
               </timesheetid>
</myfields>

And I applied this XSL:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/
Transform">
     <xsl:output method="text" encoding="utf-8" />
     <xsl:template match="/">
          Declare @TimeCardID int
          <xsl:apply-templates select="myFields/TimesheetID/Monday" />
     </xsl:template>

     <xsl:template match="Monday">
          <xsl:text>set @TimeCardID = exec spCreateTimeCard '</
xsl:text>
          <xsl:value-of select="MonDate" />
          <xsl:text>', '</xsl:text>
          <xsl:value-of select="/myFields/TimesheetID/Employee" />
          <xsl:text>'</xsl:text>
          <xsl:for-each select="MonCheck">
               exec spCreateTimeCardSession @TimeCardID, '
               <xsl:value-of select="MonCheckIn" />
               <xsl:text>', '</xsl:text>
               <xsl:value-of select="MonCheckOut" />
               <xsl:text>'</xsl:text>
          </xsl:for-each>
     </xsl:template>
 </xsl:stylesheet>


It generate this sql statement:

Declare @TimeCardID int
set @TimeCardID = exec spCreateTimeCard '2008-09-15', 'Adrian'
exec spCreateTimeCardSession @TimeCardID, '
14:10:00', '15:44:00'
exec spCreateTimeCardSession @TimeCardID,
'16:44:00', '22:50:00'

but this is not what i want, is there a way that i can generate this
statement like this:
Declare @TimeCardID int
set @TimeCardID = exec spCreateTimeCard '2008-09-15', 'Adrian'
exec spCreateTimeCardSession @TimeCardID, '14:10:00', '15:44:00'
exec spCreateTimeCardSession @TimeCardID, '16:44:00', '22:50:00'

Thansk in advance

date: Sun, 28 Sep 2008 17:47:39 -0700 (PDT)   author:   unknown

Re: apply xsl on xml to generate sql   

On Sep 29, 10:47 am, andie.du...@gmail.com wrote:
> Hi All,
>
> I have this XML files here:
>
> <?xml version="1.0" encoding="utf-8">
> <?xml-stylesheet type="text/xsl" href="TimesheetSQLScript.xsl"???>
>
> <myfields>
>      <timesheetid>
>           <companycode>CCCRM</companycode>
>           <employee>Adrian</employee>
>           <weekpick>2008-09-15</weekpick>
>           <startdate>2008-09-15</startdate>
>           <note></note>
>           <enddate>2008-09-21</enddate>
>
>           <monday>
>                <mondate>2008-09-15</mondate>
>                <moncheck>
>                     <moncheckin>14:10:00</moncheckin>
>                     <moncheckout>15:44:00</moncheckout>
>                </moncheck>
>                <moncheck>
>                     <moncheckin>16:44:00</moncheckin>
>                     <moncheckout>22:50:00</moncheckout>
>                </moncheck>
>           </monday>
>                </timesheetid>
> </myfields>
>
> And I applied this XSL:
>
> <?xml version="1.0" encoding="utf-8"?>
> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/
> Transform">
>      <xsl:output method="text" encoding="utf-8" />
>      <xsl:template match="/">
>           Declare @TimeCardID int
>           <xsl:apply-templates select="myFields/TimesheetID/Monday" />
>      </xsl:template>
>
>      <xsl:template match="Monday">
>           <xsl:text>set @TimeCardID = exec spCreateTimeCard '> xsl:text>
>           <xsl:value-of select="MonDate" />
>           <xsl:text>', '</xsl:text>
>           <xsl:value-of select="/myFields/TimesheetID/Employee" />
>           <xsl:text>'</xsl:text>
>           <xsl:for-each select="MonCheck">
>                exec spCreateTimeCardSession @TimeCardID, >                <xsl:value-of select="MonCheckIn" />
>                <xsl:text>', '</xsl:text>
>                <xsl:value-of select="MonCheckOut" />
>                <xsl:text>'</xsl:text>
>           </xsl:for-each>
>      </xsl:template>
>  </xsl:stylesheet>
>
> It generate this sql statement:
>
> Declare @TimeCardID int
> set @TimeCardID = exec spCreateTimeCard '2008-09-15', 'Adrian'
> exec spCreateTimeCardSession @TimeCardID, '
> 14:10:00', '15:44:00'
> exec spCreateTimeCardSession @TimeCardID,
> '16:44:00', '22:50:00'
>
> but this is not what i want, is there a way that i can generate this
> statement like this:
> Declare @TimeCardID int
> set @TimeCardID = exec spCreateTimeCard '2008-09-15', 'Adrian'
> exec spCreateTimeCardSession @TimeCardID, '14:10:00', '15:44:00'
> exec spCreateTimeCardSession @TimeCardID, '16:44:00', '22:50:00'
>
> Thansk in advance

 don't worry folks, i fixed by myself. just have to put <xsl:text></
xsl:text> tags like this:
   exec spCreateTimeCardSession @TimeCardID, '<xsl:text></xsl:text>

date: Sun, 28 Sep 2008 22:08:51 -0700 (PDT)   author:   unknown