Send entire node as a string to JDBC field

[Former Member]

Hi,

I am working on file to JDBC scenario ,Where I need to send the entire <SRC_DET_XML> node to the JDBC field as a string, to do so  using graphical mapping (return as XML and Replace by string),but not able to generate the target XML in the same as in source <SRC_DET_XML> node.

The problem here reading the CDATA for element in the node ........after converted into target structure ......seeing like &lt;Fields1&gt;INVOICE1&amp;&amp;&amp;&amp;&amp;&amp;&amp;&lt;/Fields1&gt;

but expecting in target structure like <![CDATA[<Fields1>INVOICE1&&&&&&&</Fields1>]]> as shown in the below source message.

Appreciate  inputs...........

Source Structure :

  <SRC_DET_XML>

      <DetailRecord>

         <LineNumber>1</LineNumber>

         <![CDATA[<Fields1>INVOICE1&&&&&&&</Fields1>]]>

         <Fields2>INVOICE2</Fields2>

         <Fields3>INVOICE3</Fields3>

      </DetailRecord>

      <DetailRecord>

         <LineNumber>2</LineNumber>

          <![CDATA[<Fields1>INVOICE1&&&&&&&</Fields1>]]>

         <Fields2>INVOICE5</Fields2>

         <Fields3>INVOICE6</Fields3>

      </DetailRecord>

      <DetailRecord>

         <LineNumber>3</LineNumber>

         <Fields1>INVOICE4</Fields1>

      <![CDATA[<Fields1>INVOICE1&&&&&&&</Fields1>]]>

         <Fields3>INVOICE6</Fields3>

      </DetailRecord>

   </SRC_DET_XML>

Target Structure :

   <SRC_DET_XML><![CDATA[<SRC_DET_XML>

      <DetailRecord>

         <LineNumber>1</LineNumber>

         &lt;Fields1&gt;INVOICE1&amp;&amp;&amp;&amp;&amp;&amp;&amp;&lt;/Fields1&gt;

         <Fields2>INVOICE2</Fields2>

         <Fields3>INVOICE3</Fields3>

      </DetailRecord>

      <DetailRecord>

         <LineNumber>2</LineNumber>

          &lt;Fields1&gt;INVOICE1&amp;&amp;&amp;&amp;&amp;&amp;&amp;&lt;/Fields1&gt;

         <Fields2>INVOICE5</Fields2>

         <Fields3>INVOICE6</Fields3>

      </DetailRecord>

      <DetailRecord>

         <LineNumber>3</LineNumber>

         <Fields1>INVOICE4</Fields1>

       &lt;Fields1&gt;INVOICE1&amp;&amp;&amp;&amp;&amp;&amp;&amp;&lt;/Fields1&gt;

         <Fields3>INVOICE6</Fields3>

      </DetailRecord>

   </SRC_DET_XML>]]></SRC_DET_XML>

[markangelo_dihiansan]

Hello,

The problem here reading the CDATA for element in the node ........after converted into target structure ......seeing like <Fields1>INVOICE1&&&&&&&</Fields1> 
but expecting in target structure like <![CDATA[<Fields1>INVOICE1&&&&&&&</Fields1>]]> as shown in the below source message.

I think you are nesting a CDATA within a CDATA which is not possible. You should split your XML into multiple CDATA sections so that CDATA embedding is avoided:

http://stackoverflow.com/questions/223652/is-there-a-way-to-escape-a-cdata-end-token-in-xml

Hope this helps,

Mark

[iaki_vila]

HI Praven,

At first i think that the tag   <![CDATA[<Fields1>INVOICE1&&&&&&&</Fields1>]]> should be <Fields1><![CDATA[INVOICE1&&&&&&&]]></Fields1> and in this way you respect the XML format.

Then with XSL is possible to put an entire XML like XML tag:

1. XML source:

<?xml version="1.0" encoding="UTF-8"?>
<SRC_DET_XML>
    <DetailRecord>
        <LineNumber>1</LineNumber>
        <Fields1><![CDATA[INVOICE1&&&&&&&]]></Fields1>
        <Fields2>INVOICE2</Fields2>
        <Fields3>INVOICE3</Fields3>
    </DetailRecord>
    <DetailRecord>
        <LineNumber>2</LineNumber>
        <Fields1><![CDATA[INVOICE1&&&&&&&]]></Fields1>
        <Fields2>INVOICE5</Fields2>
        <Fields3>INVOICE6</Fields3>
    </DetailRecord>
    <DetailRecord>
        <LineNumber>3</LineNumber>
        <Fields2>INVOICE4</Fields2>
        <Fields1><![CDATA[INVOICE1&&&&&&&]]></Fields1>
        <Fields3>INVOICE6</Fields3>
    </DetailRecord>
</SRC_DET_XML>

2. XSL mapping.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
    <xsl:template match="/">
        <xsl:element name="SRC_DET_XML">
            <xsl:text disable-output-escaping="yes">  &lt;![CDATA[  </xsl:text>
            <SRC_DET_XML>
                <xsl:for-each select="./SRC_DET_XML/DetailRecord">
                    <xsl:variable name="A" select="."/>
                    <DetailRecord>
                        <LineNumber>
                            <xsl:value-of select="$A/LineNumber/."/>
                        </LineNumber>
                        <Fields2>
                            <xsl:value-of select="$A/Fields2"/>
                        </Fields2>
                        <Fields1>
                            <xsl:value-of select="$A/Fields1" disable-output-escaping="yes"/>
                        </Fields1>
                        <Fields3>
                            <xsl:value-of select="$A/Fields3"/>
                        </Fields3>
                    </DetailRecord>
                </xsl:for-each>
           </SRC_DET_XML>
                <xsl:text disable-output-escaping="yes">  ]]&gt; </xsl:text>
        </xsl:element>
    </xsl:template>
</xsl:stylesheet>

3. The result of the mapping will be:

<?xml version="1.0" encoding="UTF-8"?>
<SRC_DET_XML>  <![CDATA[  <SRC_DET_XML><DetailRecord>
            <LineNumber>1</LineNumber>
            <Fields2>INVOICE2</Fields2>
            <Fields1>INVOICE1&&&&&&&</Fields1>
            <Fields3>INVOICE3</Fields3>
        </DetailRecord><DetailRecord>
            <LineNumber>2</LineNumber>
            <Fields2>INVOICE5</Fields2>
            <Fields1>INVOICE1&&&&&&&</Fields1>
            <Fields3>INVOICE6</Fields3>
        </DetailRecord><DetailRecord>
            <LineNumber>3</LineNumber>
            <Fields2>INVOICE4</Fields2>
            <Fields1>INVOICE1&&&&&&&</Fields1>
            <Fields3>INVOICE6</Fields3>
        </DetailRecord>  </SRC_DET_XML> ]]> </SRC_DET_XML>

I hope this can help you a bit.

Regards.