RE: [xsl] Best way to substitute a empty child node to the value 'NULL'

Subject: RE: [xsl] Best way to substitute a empty child node to the value 'NULL'
From: "Michael Kay" <mike@xxxxxxxxxxxx>
Date: Thu, 7 Feb 2008 09:37:28 -0000
>    Below is a XSLT 2.0 solution:

That's a possible solution, but I would do it in push style:

     <xsl:stylesheet version="1.0"
       xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
       xmlns:xs="http://www.w3.org/2001/XMLSchema";>
   
     <xsl:output indent="yes" />

     <xsl:template match="/">
       <xsl:for-each select="data/row">
         INSERT INTO DBO.TEST
              (COL1, COL2, COL3, COL4) VALUES(
              <xsl:value-of separator=",">
                <xsl:apply-templates/>
              </xsl:value-of>
          );
       </xsl:for-each>
     </xsl:template>

     <xsl:template match="row/*">
       <xsl:sequence select="concat($apos, normalize-space(.), $apos)"/>
     </xsl:template>

     <xsl:template match="row/*[normalize-space(.) = '']">       
       <xsl:sequence select="'NULL'"/>
     </xsl:template>

    <xsl:variable name="apos" as="xs:string">'</xsl:variable>
  
    </xsl:stylesheet>
> 
> 
> On Feb 7, 2008 10:53 AM, Tariq Ahsan <tariqahsan@xxxxxxxxx> wrote:
> > Hi,
> >
> > I am a newbie to XSLT. I am trying to write a simple xslt script to 
> > transform a xml file with records to a SQL file which will have 
> > separate SQL insert statements. But some of the nodes of this input 
> > xml file will contain empty nodes. I would like to transform all of 
> > the empty nodes to the value of 'NULL'. Here is a sample content of 
> > the input xml file
> > -
> >
> > <data>
> >    <row>
> >      <TAG1>123</TAG1>
> >      <TAG2>ABC</TAG2>
> >      <TAG3 />
> >      <TAG4 />
> >    </row>
> >    <row>
> >      <TAG1>999</TAG1>
> >      <TAG2>XYZ</TAG2>
> >      <TAG3 />
> >      <TAG4 />
> >    </row>
> >
> > </data>
> >
> > Here's the what I have now in the xsl file -
> >
> > <?xml version="1.0" encoding="utf-8"?>
> >
> > <xsl:stylesheet version="1.0"
> >    xmlns:xsl="http://www.w3.org/1999/XSL/Transform";>
> >        <xsl:output indent="yes" />
> >
> > <xsl:template match="/">
> > <xsl:for-each select="data/row">
> > INSERT INTO DBO.TEST
> > (COL1, COL2, COL3, COL4) VALUES(
> > <xsl:value-of select="normalize-space(TAG1)"/>, '<xsl:value-of 
> > select="normalize-space(TAG2)"/>',
> > '<xsl:value-of select="normalize-space(TAG3)"/>',
> > <xsl:value-of select="normalize-space(TAG4)"/>, ); </xsl:for-each> 
> > </xsl:template> </xsl:stylesheet>
> >
> > Here's the output sql file should have
> >
> > insert into DBO.TEST (COL1, COL2, COL3, COL4) VALUES (123, 'ABC', 
> > NULL, NULL); insert into DBO.TEST (COL1, COL2, COL3, COL4) VALUES 
> > (999, 'XYZ', NULL, NULL);
> >
> > Would appreciate if I could get a simple solution for this problem.
> >
> > Thanks
> >
> > Tariq Ahsan
> 
> 
> 
> --
> Regards,
> Mukul Gandhi

Current Thread