I have a table in Postgresql
DROP TABLE xml_docs;
CREATE TABLE xml_docs(
id serial PRIMARY KEY,
cad_number character(50),
gkuzu_name character(50),
gkuzu xml,
rreq_name character(50),
rreq xml
)
I use JDBC for data base connection. And i want to insert whole xml document in table.
How can i make this?
UPDATE
Okey. i try
String sql = "INSERT INTO xml_docs(cad_number,gkuzu_name,gkuzu,rreq_name,rreq) VALUES(?,?,?,?,?)";
PreparedStatement stmt = ce.prepareStatement(sql);
stmt.setString(1, "11:33:5464563");
stmt.setString(2, xml_gkuzu.getName());
stmt.setString(3, xml_gkuzu.toString());
stmt.setString(4, xml_rreq.getName());
stmt.setString(5, xml_rreq.toString());
stmt.executeQuery();
ce.close();
se.close();
and get exeption
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "gkuzu" is of type xml but expression is of type character varying
Подсказка: You will need to rewrite or cast the expression.
Whats wrong?
UPDATE 2
When i try do this
String sql1 = "INSERT INTO xml_docs(cad_number,gkuzu_name,gkuzu,rreq_name,rreq) VALUES(11335464563,"+xml_gkuzu.getName()+",XMLPARSE("+xml_gkuzu.toString()+"),"+xml_rreq.getName()+",XMLPARSE("+xml_rreq.toString()+"))";
i get exeption
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at or near "bf48e000b0"
I'm not sure, but try this:
First convert your XML to a Java String. Then create an insert statement und use the XMLPARSE method of PostgreSQL to convert your value to the xml type of PostgreSQL:
INSERT INTO xml_docs(id, gkuzu) VALUES (1, XMLPARSE('<foo><bar>Hello</bar></foo>'));
See: http://wiki.postgresql.org/wiki/XML_Support
UPDATE:
Java code example:
String sql = "INSERT INTO xml_docs(id, gkuzu) VALUES (?, XMLPARSE(?))";
[...]
stmt.setString(2, "<foo>Hello World!</foo>");
This should create this statement:
INSERT INTO xml_docs(id, gkuzu) VALUES (1, XMLPARSE('<foo>Hello World!</foo>'));
Instead of rewriting the insert statement using PostgreSQL-proprietary syntax, you could use JDBC 4 SQLXML:
String xml = xml_gkuzu.toString();
SQLXML sqlxml = connection.createSQLXML();
sqlxml.setString(xml);
stmt.setSQLXML(3, sqlxml);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With