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

[Tariq Ahsan <tariqahsan@xxxxxxxxx>]

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





      ____________________________________________________________________________________
Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs