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="http://apache.org/cocoon/SQL/2.0";>
<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
nodeset.
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"?>
<root>
<record id="1">
<name>John Smith</name>
<city>London</city>
</record>
</root>
XSLT (test.xslt)
===========
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
xmlns:sql="http://apache.org/cocoon/SQL/2.0";>
<!-- user session vars from sitemap -->
<xsl:variable name="nodeset">
<xsl:copy-of select="root"/>
</xsl:variable>
<xsl:strip-space elements="*"/>
<xsl:template match="/">
<page>
<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0";>
<sql:query name="nodeset">
INSERT INTO mytable (nodeset)
VALUES ('<xsl:copy-of select="$nodeset"/>');
</sql:query>
</sql:execute-query>
</page>
</xsl:template>
<xsl:template match="@*|node()"/>
</xsl:stylesheet>
SITEMAP
========
<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:transform>
<map:serialize type="xml"/>
</map:match>
I get the same results if I serialize to xhtml (which is what I am
actually using).
Thanks for your help! Dan
