Thread: XML document with doctype
Is there a way to persist XML data as a DOCUMENT as opposed to CONTENT (the default) without using SET XML OPTION / XMLPARSE? I'm using Hibernate and don't have control over building of the SQL for the PreparedStatement out of the box or when it executes to wrap calls to SET XML OPTION. For Hibernate, I'm using a UserType I wrote that uses SQLXML. In my case, I usually have XML content so it works fine in those cases; occasionally, I have a document to persist and then it will fail occasionally unless I tell PG that it is a document. I'm trying to find the best way to work with Hibernate when I have a document while minimizing the amount of Hibernate code I have to write. Before I delve into that, I wanted to make sure I wasn't missing any other options for passing along DOCUMENT/CONTENT info for converting from a string to an XML type. So, it looks like my options are: XMLPARSE (custom SQL) and SET XML OPTION. Are there any other options that I'm missing? Thanks for any help. -- Russ Tennant Software Engineer I2RD (Interactive Information R&D) Email: russ@i2rd.com
Russ,
On Wed, Jan 23, 2013 at 7:38 PM, Russ Tennant <russ@i2rd.com> wrote:
Is there a way to persist XML data as a DOCUMENT as opposed to CONTENT
(the default) without using SET XML OPTION / XMLPARSE?
I'm using Hibernate and don't have control over building of the SQL for
the PreparedStatement out of the box or when it executes to wrap calls
to SET XML OPTION. For Hibernate, I'm using a UserType I wrote that uses
SQLXML. In my case, I usually have XML content so it works fine in those
cases; occasionally, I have a document to persist and then it will fail
occasionally unless I tell PG that it is a document. I'm trying to find
the best way to work with Hibernate when I have a document while
minimizing the amount of Hibernate code I have to write. Before I delve
into that, I wanted to make sure I wasn't missing any other options for
passing along DOCUMENT/CONTENT info for converting from a string to an
XML type.
So, it looks like my options are: XMLPARSE (custom SQL) and SET XML
OPTION. Are there any other options that I'm missing? Thanks for any help.
--
Russ Tennant
Software Engineer
I2RD (Interactive Information R&D)
Email: russ@i2rd.com
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Dave,
Thanks for the response. I had reviewed the source code for the version 9.0 driver, org.postgresql.jdbc4.Jdbc4SQLXML & org.postgresql.jdbc4.AbstractJdbc4Statement.setSQLXML(int, SQLXML), and I had already implemented hibernate code, XMLUserType, to add support for SQLXML for the version of hibernate we use. It works great except when we try to store something like "<!DOCTYPE foo><foo/>". The issue I'm having is providing a way to pass along information (JDBC- > PostgreSQL) on whether the XML string passed via SQLXML is a DOCUMENT/CONTENT ( http://www.postgresql.org/docs/9.0/static/datatype-xml.html ) so that it is converted correctly when it hits the server. I was wondering if there is a way to do this other than what I mentioned previously.
Basically, I'm trying to find the best method to pass DOCUMENT/CONTENT info through Hibernate -> JDBC -> PostgreSQL. I am not seeing any way to do it with SQLXML. I wanted to make sure I wasn't missing some easier way of doing this before writing custom Hibernate code to generate the needed SQL.
Example of what I mean by CONTENT vs DOCUMENT:
Default of CONTENT:
Specifying DOCUMENT:
On 1/24/2013 2:20 AM, Dave Cramer wrote:
Thanks for the response. I had reviewed the source code for the version 9.0 driver, org.postgresql.jdbc4.Jdbc4SQLXML & org.postgresql.jdbc4.AbstractJdbc4Statement.setSQLXML(int, SQLXML), and I had already implemented hibernate code, XMLUserType, to add support for SQLXML for the version of hibernate we use. It works great except when we try to store something like "<!DOCTYPE foo><foo/>". The issue I'm having is providing a way to pass along information (JDBC- > PostgreSQL) on whether the XML string passed via SQLXML is a DOCUMENT/CONTENT ( http://www.postgresql.org/docs/9.0/static/datatype-xml.html ) so that it is converted correctly when it hits the server. I was wondering if there is a way to do this other than what I mentioned previously.
Basically, I'm trying to find the best method to pass DOCUMENT/CONTENT info through Hibernate -> JDBC -> PostgreSQL. I am not seeing any way to do it with SQLXML. I wanted to make sure I wasn't missing some easier way of doing this before writing custom Hibernate code to generate the needed SQL.
Example of what I mean by CONTENT vs DOCUMENT:
Default of CONTENT:
(russ@[local]:5432) [russ] > INSERT INTO foo VALUES('<!DOCTYPE foo><foo/>');
ERROR: invalid XML content
LINE 1: INSERT INTO foo VALUES('<!DOCTYPE foo><foo/>');
^
DETAIL: Entity: line 1: parser error : StartTag: invalid element name
<!DOCTYPE foo><foo/>
^
Specifying DOCUMENT:
(russ@[local]:5432) [russ] > INSERT INTO foo VALUES(XMLPARSE(DOCUMENT '<!DOCTYPE foo><foo/>'));
INSERT 0 1
Time: 0.455 ms
On 1/24/2013 2:20 AM, Dave Cramer wrote:
Russ,A cursory glance at JDBC4 suggests that there is support for the SQLXML type in the driver. Does hibernate have any support for it.On Wed, Jan 23, 2013 at 7:38 PM, Russ Tennant <russ@i2rd.com> wrote:Is there a way to persist XML data as a DOCUMENT as opposed to CONTENT
(the default) without using SET XML OPTION / XMLPARSE?
I'm using Hibernate and don't have control over building of the SQL for
the PreparedStatement out of the box or when it executes to wrap calls
to SET XML OPTION. For Hibernate, I'm using a UserType I wrote that uses
SQLXML. In my case, I usually have XML content so it works fine in those
cases; occasionally, I have a document to persist and then it will fail
occasionally unless I tell PG that it is a document. I'm trying to find
the best way to work with Hibernate when I have a document while
minimizing the amount of Hibernate code I have to write. Before I delve
into that, I wanted to make sure I wasn't missing any other options for
passing along DOCUMENT/CONTENT info for converting from a string to an
XML type.
So, it looks like my options are: XMLPARSE (custom SQL) and SET XML
OPTION. Are there any other options that I'm missing? Thanks for any help.
-- Russ Tennant Software Engineer I2RD (Interactive Information R&D) Email: russ@i2rd.com