How to insert <![CDATA[]]> tags into final XML output

I have a stylesheet that transforms XML from one format to another and works perfectly except that I want my style sheet to place the data within the resulting XML tags within a
<![CDATA[]]> tag so that if the tag is empty my SQLXMLBulkLoad object won't evaluate this tag
value as NULL when it comes to insert the XML into my DB table.

My stylesheet looks like this:

<?xml version='1.0' encoding='ISO-8859-1'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="yes"/>

<xsl:param name="location" select="'GPOLTD'"></xsl:param>
<xsl:param name="transcode" select="'GPO'"></xsl:param>
<xsl:param name="padzeroes" select ="'00000000000000000000'"></xsl:param>

<xsl:template match="Table">
    <Contributions>
      <xsl:apply-templates select="Row[position()&lt;(last()-1)]"/>
    </Contributions>
</xsl:template>  
 
<xsl:template match="Row">
    <Contribution>
      <xsl:attribute name="num"><xsl:value-of select="position()"/></xsl:attribute>
      <ACHolder>
        <xsl:value-of select="concat($location, substring($padzeroes, 1, 20 - string-length($location) - string-length(Cell[4]/Data)), Cell[4]/Data)"/>
      </ACHolder>
      <EmployeeNo>
        <xsl:value-of select="Cell[1]/Data"/>
      </EmployeeNo>
      <EmployeeName>
        <xsl:value-of select="concat(Cell[2]/Data, ' ', Cell[3]/Data)"/>
      </EmployeeName>
      <EmployeeSurname>
        <xsl:value-of select="Cell[3]/Data"/>
      </EmployeeSurname>      
      <Total>
        <xsl:value-of select="Cell[5]/Data + Cell[6]/Data + Cell[7]/Data"/>       
      </Total>
      <PayDate>
        <xsl:value-of select="Cell[8]/Data"/>             
      </PayDate>
      <TransCode>
        <xsl:value-of select="$transcode"/>
      </TransCode>
      <Comments></Comments>
      <BTA></BTA>
      <Currency></Currency>
      <Field1>
        <xsl:value-of select="Cell[5]/Data"/>      
      </Field1>
      <Field2>
        <xsl:value-of select="Cell[6]/Data"/>      
      </Field2>
      <Field3>
        <xsl:value-of select="Cell[7]/Data"/>      
      </Field3>
      <Field4>0</Field4>       
      <Field5>0</Field5>
    </Contribution>
</xsl:template>  
</xsl:stylesheet>

So what I want to happen is that if any of the values inserted into the atgs above evaluate to empty that the tag ends up looking like
<tag><![CDATA[]]></tag>
and not
<tag></tag>

Many thanks
Sam

Samm1502Asked:
Who is Participating?
 
Geert BormansConnect With a Mentor Information ArchitectCommented:
There is no solution to do this in a fine-grained way.
So you can't say: "on this condition make this element a CDATA section"
You can only pick an element and make it CDATA allover the output tree

<xsl:output cdata-section-elements="tag" indent="yes"/>
makes all the tag elements CDATA sections, also when they have content

This should not be a problem. CDATA sections are regarded as any other text nodes during processing.
It strikes me as an odd requirement though.
There is no use in having CDATA if an element is empty... because there is no implicit escaping to be done on an empty string

cheers

Geert

0
 
BobSiemensCommented:
<tag><![CDATA[]]></tag>
and not
<tag></tag>
==========

Just escape the output characters.
<![CDATA[]]>

IS THE SAME AS

&lt;![CDATA[]]&gt;

(or something very much like that)
0
 
BobSiemensCommented:
By the way, I agree you should revisit using CDATA.  It's usually a sign of doing things the wrong way.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Vivek ThangaswamyCommented:
0
 
Samm1502Author Commented:
The reason for this is that I am bulk loading my data into a DB table straight from the XML file and the bulkload facility treats empty tags as NULLS when my DB table disallows NULLS on those columns.  I got around this by allowing NULLS on those columns but providing default values so that when the tags are empty the DB inserts the default value and not NULL.

Many thanks and sorry for delayed reply have been unwell.

Sam
0
 
Geert BormansInformation ArchitectCommented:
welcome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.