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
