Geek Answers Handbook

How to embed an XML document in PostgreSQL in Java?

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 am using JDBC to connect the database. And I want to insert the whole XML document into a table.
How can i do this?

UPDATE

Okay I'm trying to

  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.

What's wrong?

UPDATE 2

When i try to do it

  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"
+4
source share
4 answers

I'm not sure, but try the following:

Convert the XML to a Java string first. Then create an insert statement and use the XMLPARSE method for PostgreSQL to convert your value to the PostgreSQL xml type:

 INSERT INTO xml_docs(id, gkuzu) VALUES (1, XMLPARSE('<foo><bar>Hello</bar></foo>'));

See: http://wiki.postgresql.org/wiki/XML_Support

UPDATE:

Sample Java code:

 String sql = "INSERT INTO xml_docs(id, gkuzu) VALUES (?, XMLPARSE(?))"; [...] stmt.setString(2, "<foo>Hello World!</foo>");

This should create the following statement:

 INSERT INTO xml_docs(id, gkuzu) VALUES (1, XMLPARSE('<foo>Hello World!</foo>'));
+5
source

Updating the accepted answer if you don't have Postgres created with libxml support:

Sample Java code:

 String sql = "INSERT INTO xml_docs(id, gkuzu) VALUES (?, XML(?))"; [...] stmt.setString(2, "<foo>Hello World!</foo>");

This should create the following statement:

 INSERT INTO xml_docs(id, gkuzu) VALUES (1, XML('<foo>Hello World!</foo>'));

Thus, for version 9.0 and higher, you can switch to XMLPARSE ==> XML . Otherwise, you will need special XMLPARSE support XMLPARSE

From the Postgres documentation :

The xmlparse and xmlserialize functional expressions for converting to and from the xml type are not repeated here. Using most of these functions requires that the installation be built using configure --with-libxml.

+3
source

Instead of rewriting the insert statement using PostgreSQL syntax, you can use JDBC 4 SQLXML :

 String xml = xml_gkuzu.toString(); SQLXML sqlxml = connection.createSQLXML(); sqlxml.setString(xml); stmt.setSQLXML(3, sqlxml);
+3
source

Although postgres has its own XML Data type , from java end, you can handle Plain strings.
You can convert your XML document to String and paste it should work.

UPDATE:

After viewing your error, you need to pass an additional variable to the server through the driver URL.

 jdbc:postgresql://localhost/test?stringtype=unspecified or jdbc:postgresql://localhost/test?user=user&password=pass&stringtype=unspecified

The optional parameter stringtype=unspecified will remove the type check for input strings.

+1
source

More articles:


All Articles