I have a table containing fairly large XML documents stored in a CLOB
column. I want to make a specific change to the XML, which I could do
manually through the application which uses this database, if only I had
a few years to spend doing it to each affected entry.
I am trying to find a way to do it using the export/import procedures
described in the Derby Administration Guide. I have successfully
exported the XML to a text file using
CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY(
'SELECT xmldata FROM mytable','C:\mytable.txt',null,null,null);
I have edited it, and now want to replace the XML in the existing rows.
I tried this:
CALL SYSCS_UTIL.SYSCS_IMPORT_DATA
(null, 'MYTABLE', 'XMLDATA', '1',
'C:\mytable.txt', null,null,null, 1);
I had hoped that this would just update the XMLDATA column in each row
using the values from the file, but what I get is an SQL exception:
Import error on line 1 of file C:\mytable.txt: Column 'FOO' cannot
accept a NULL value.
which tells me that it is trying to null out the other columns. I could
do "SELECT *" instead of "SELECT xmldata", but the table has a
generated-always-as-identity ID column which will presumably give me an
error if I try to update it, even if I try to replace it with the same
value.
I thought of exporting ID and XMLDATA and then editing it to produce a
list of UPDATE statements, but the XML data is far too large for a
single string.
How can I just update the XMLDATA column without affecting anything else?
--
John English
--
This email has been checked for viruses by AVG antivirus software.
www.avg.com
