You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cocoon.apache.org by Dan Hertz <da...@danhertz.com> on 2006/11/18 17:09:30 UTC
SQL Transformer Bug?
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.
What am I doing wrong?
Thanks!
RE: SQL Transformer Bug?
Posted by Geurt Wisselink <gw...@home.nl>.
Hi Dan
I am happy it worked.
At this time I don't know where the signature comes from.
What I do know is, that upon reading the data, by the SQLTransformer,
this heading is removed so that the resulting XML is nicely inserted into
the resulting answer.
Let me know if you experience problems with the XML signature.
Kind regards,
Geurt Wisselink
-------------------
Geurt,
Thanks for your update. Both versions work well, but I notice the SQL
Transformer inserts an XML document, rather than an XML fragment into my
database:
<?xml version="1.0" encoding="UTF-8"?>
<root xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<record id="1">
<name>John Smith</name>
<city>London</city>
</record>
</root>
instead of:
<root>
<record id="1">
<name>John Smith</name>
<city>London</city>
</record>
</root>
Is there something I need to do in order to insert the former rather
than the latter?
Great work, and again, much thanks...this is a HUGE help!
Dan
Re: SQL Transformer Bug?
Posted by Dan Hertz <da...@danhertz.com>.
> Is there something I need to do in order to insert the former rather
> than the latter?
Oops...I meant insert the latter. (i.e. the xml fragment)
Dan
Re: SQL Transformer Bug?
Posted by Dan Hertz <da...@danhertz.com>.
Geurt Wisselink wrote:
> Dear Dan,
>
> I ran into the same problem and ended up modifying the transformer.
> I returned the modified version to the cocoon development group.
>
> I added two options to work with xml.
>
> The first was the original foreseen sql:xml construct.
> Basically it works like this:
> <sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
> <sql:query name="nodeset">
> INSERT INTO mytable (nodeset)
> VALUES ('<sql:xml><root><record id="1"><name>John
> Smith</name><city>London<city></record></root></sql:xml>');
> </sql:query>
> </sql:execute-query>
>
> In this way a string is created with the XML content in side.
>
> The second thing I added was the capability to work with xml variables by
> means of the sql:in-xml-parameter.
> It works like the standard in-parameter but is tailored to work with XML
> content.
> This works like:
> <sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
> <sql:in-xml-parameter nr="1"><root><record id="1"><name>John
> Smith</name><city>London<city></record></root></sql:in-xml-parameter>
> <sql:query name="nodeset">
> INSERT INTO mytable (nodeset)
> VALUES (?);
> </sql:query>
> </sql:execute-query>
>
> The modified SQLTransformer is added as attachment to this mail.
> Just put it src\blocks\databases\java\org\apache\cocoon\transformation, and
> rerun build.
>
> This last option is the best for last XML blocks, because the data is
> streamed to the database engine, instead of embedding it into a SQL query.
>
> I hope this helps.
>
>
> Kind regards,
>
> Geurt Wisselink
>
>
>
Geurt,
Thanks for your update. Both versions work well, but I notice the SQL
Transformer inserts an XML document, rather than an XML fragment into my
database:
<?xml version="1.0" encoding="UTF-8"?>
<root xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<record id="1">
<name>John Smith</name>
<city>London</city>
</record>
</root>
instead of:
<root>
<record id="1">
<name>John Smith</name>
<city>London</city>
</record>
</root>
Is there something I need to do in order to insert the former rather
than the latter?
Great work, and again, much thanks...this is a HUGE help!
Dan
RE: SQL Transformer Bug?
Posted by Geurt Wisselink <gw...@home.nl>.
Dear Dan,
I ran into the same problem and ended up modifying the transformer.
I returned the modified version to the cocoon development group.
I added two options to work with xml.
The first was the original foreseen sql:xml construct.
Basically it works like this:
<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<sql:query name="nodeset">
INSERT INTO mytable (nodeset)
VALUES ('<sql:xml><root><record id="1"><name>John
Smith</name><city>London<city></record></root></sql:xml>');
</sql:query>
</sql:execute-query>
In this way a string is created with the XML content in side.
The second thing I added was the capability to work with xml variables by
means of the sql:in-xml-parameter.
It works like the standard in-parameter but is tailored to work with XML
content.
This works like:
<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<sql:in-xml-parameter nr="1"><root><record id="1"><name>John
Smith</name><city>London<city></record></root></sql:in-xml-parameter>
<sql:query name="nodeset">
INSERT INTO mytable (nodeset)
VALUES (?);
</sql:query>
</sql:execute-query>
The modified SQLTransformer is added as attachment to this mail.
Just put it src\blocks\databases\java\org\apache\cocoon\transformation, and
rerun build.
This last option is the best for last XML blocks, because the data is
streamed to the database engine, instead of embedding it into a SQL query.
I hope this helps.
Kind regards,
Geurt Wisselink
-----Original Message-----
From: Dan Hertz [mailto:dan@danhertz.com]
Sent: 18 November 2006 17:10
To: dev@cocoon.apache.org
Subject: SQL Transformer Bug?
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.
What am I doing wrong?
Thanks!
Re: SQL Transformer Bug?
Posted by Dan Hertz <da...@danhertz.com>.
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
Re: SQL Transformer Bug?
Posted by Jason Johnston <co...@lojjic.net>.
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>]]>');
Re: SQL Transformer Bug?
Posted by Joerg Heinicke <jo...@gmx.de>.
On 18.11.2006 17:09, 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.
How does your pipeline look like? Is there a XSLT transformation done
afterwards? Can you just serialize to XML directly after the
SQLTransformer just to see what it spits out?
Jörg