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

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

Hi Tariq,
   Below is a XSLT 2.0 solution:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
	       xmlns:xs="http://www.w3.org/2001/XMLSchema";
	       xmlns:my="http://dummy-ns";
	       version="2.0">

  <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="my:getColValue(TAG1)"/>,
            <xsl:value-of select="my:getColValue(TAG2)"/>,
            <xsl:value-of select="my:getColValue(TAG3)"/>,
            <xsl:value-of select="my:getColValue(TAG4)"/>
        );
     </xsl:for-each>
   </xsl:template>

   <xsl:function name="my:getColValue" as="xs:string">
     <xsl:param name="value" as="xs:string" />

     <xsl:choose>
       <xsl:when test="normalize-space($value) = ''">
         <xsl:sequence select="'NULL'" />
       </xsl:when>
       <xsl:otherwise>
         <xsl:sequence
select="concat(&quot;&apos;&quot;,normalize-space($value),&quot;&apos;&quot;)"
/>
       </xsl:otherwise>
     </xsl:choose>
   </xsl:function>

</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