cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dan Hertz <>
Subject Re: SQL Transformer Bug?
Date Sun, 19 Nov 2006 00:18:11 GMT
Jason Johnston wrote:
> Dan Hertz wrote:
>> I'm hoping someone can help me troubleshoot why I can't insert an xml 
>> nodeset into my database using the SQL Transformer. All I end up with 
>> is the text() values concatenated together -- no elements or 
>> attribute nodes. For example:
>> <sql:execute-query xmlns:sql="">
>> <sql:query name="nodeset">
>> INSERT INTO mytable (nodeset)
>> VALUES ('<root><record id="1"><name>John 
>> Smith</name><city>London<city></record></root>');
>> </sql:query>
>> </sql:execute-query>
>> would insert: 1JohnSmithLondon into my database.
>> If I plug this query into my sql editor, I correctly get:
>> <root><record id="1"><name>John 
>> Smith</name><city>London<city></record></root>
>> inserted into the database.
> My guess is that the XML is getting interpreted and the 
> startElement/endElement SAX events just get ignored.  Maybe if you try 
> wrapping the XML in a CDATA section, that way the whole thing will be 
> treated as a text node:
> INSERT INTO mytable (nodeset)
> VALUES ('<![CDATA[<root><record id="1"><name>John 
> Smith</name><city>London<city></record></root>]]>');

I thought of that, but I'm actually getting the info from a variable..

INSERT INTO mytable (nodeset)
VALUES ('<xsl:copy-of select="$nodeset"/>');

If I put CDATA brackets around the <xsl:copy-of />, I get <xsl:copy-of 
select="$nodeset"/> inserted into my database, instead of the actual 

When I preview the transformation just before the <map:transform 
type="sql"/>, everything is in place as it should be...the nodeset is 
inserted into the VALUES area. I'm stumped as to why, when I do the sql 
transform next, it doesn't actually insert the entire xml block into the 
database, just the text() values.

Here's a sample to try out:

XML (test.xml)
<?xml version="1.0" encoding="UTF-8"?>
  <record id="1">
    <name>John Smith</name>

XSLT (test.xslt)
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" 
<!-- user session vars from sitemap -->
<xsl:variable name="nodeset">
<xsl:copy-of select="root"/>
<xsl:strip-space elements="*"/>

<xsl:template match="/">
<sql:execute-query xmlns:sql="">
<sql:query name="nodeset">
INSERT INTO mytable (nodeset)
VALUES ('<xsl:copy-of select="$nodeset"/>');
<xsl:template match="@*|node()"/>

<map:match pattern="test.xml">
  <map:generate type="file" src="test.xml"/>
  <map:transform type="xslt" src="test.xslt"/>
  <map:transform type="sql">
      <map:parameter name="use-connection" value="tmp"/>
      <map:parameter name="show-nr-of-rows" value="true"/>
      <map:parameter name="clob-encoding" value="UTF-8"/>
   <map:serialize type="xml"/>

I get the same results if I serialize to xhtml (which is what I am 
actually using).

Thanks for your help! Dan

View raw message