Thread: large XML data type appears empty when doing a select
Postgresql 9.0.5 on Red Hat/Linux
In the table described below, the report xml contains xml (DOCUMENT) that ranges in size from 31KB to 518KB. When I do ‘select reportxml from somereportxml where reportkey = ‘silly’;’ the terminal screen appears blank with ‘more’ displayed on the bottom. The data is in the database – verified using EMS SQL Studio for PostgreSQL – but does not show up using pgadmin3 or typing the Select statement from the terminal screen. Any ideas?
CREATE TABLE somereportxml
(
reportkey character varying(36) NOT NULL DEFAULT uuid_generate_v1(),
objectname character varying(200) NOT NULL,
reportxml xml NOT NULL,
recordtimestamp timestamp(2) with time zone NOT NULL DEFAULT now(),
CONSTRAINT pkey_somereportxml PRIMARY KEY (reportkey )
)
WITH (
OIDS=FALSE
);
Paula Price
Maybe it has to do with this:Postgresql 9.0.5 on Red Hat/Linux
In the table described below, the report xml contains xml (DOCUMENT) that ranges in size from 31KB to 518KB. When I do ‘select reportxml from somereportxml where reportkey = ‘silly’;’ the terminal screen appears blank with ‘more’ displayed on the bottom. The data is in the database – verified using EMS SQL Studio for PostgreSQL – but does not show up using pgadmin3 or typing the Select statement from the terminal screen. Any ideas?
CREATE TABLE somereportxml
(
reportkey character varying(36) NOT NULL DEFAULT uuid_generate_v1(),
objectname character varying(200) NOT NULL,
reportxml xml NOT NULL,
recordtimestamp timestamp(2) with time zone NOT NULL DEFAULT now(),
CONSTRAINT pkey_somereportxml PRIMARY KEY (reportkey )
)
WITH (
OIDS=FALSE
);
Paula Price
8.13.2. Encoding Handling
Care must be taken when dealing with multiple character encodings on the client, server, and in the XML data passed through them. When using the text mode to pass queries to the server and query results to the client (which is the normal mode), PostgreSQL converts all character data passed between the client and the server and vice versa to the character encoding of the respective end; see Section 22.2.http://www.postgresql.org/docs/9.0/static/datatype-xml.html
Or maybe with this
"An alternative solution for comparing XML values is to convert them to character strings first, but note that character string comparison has little to do with a useful XML comparison method."
So try casting reportxml to varchar
Greetings!
El 12/01/2012 12:58 p.m., Paula Price escribió:You can also try this:Maybe it has to do with this:Postgresql 9.0.5 on Red Hat/Linux
In the table described below, the report xml contains xml (DOCUMENT) that ranges in size from 31KB to 518KB. When I do ‘select reportxml from somereportxml where reportkey = ‘silly’;’ the terminal screen appears blank with ‘more’ displayed on the bottom. The data is in the database – verified using EMS SQL Studio for PostgreSQL – but does not show up using pgadmin3 or typing the Select statement from the terminal screen. Any ideas?
CREATE TABLE somereportxml
(
reportkey character varying(36) NOT NULL DEFAULT uuid_generate_v1(),
objectname character varying(200) NOT NULL,
reportxml xml NOT NULL,
recordtimestamp timestamp(2) with time zone NOT NULL DEFAULT now(),
CONSTRAINT pkey_somereportxml PRIMARY KEY (reportkey )
)
WITH (
OIDS=FALSE
);
Paula Price
8.13.2. Encoding Handling
Care must be taken when dealing with multiple character encodings on the client, server, and in the XML data passed through them. When using the text mode to pass queries to the server and query results to the client (which is the normal mode), PostgreSQL converts all character data passed between the client and the server and vice versa to the character encoding of the respective end; see Section 22.2.
http://www.postgresql.org/docs/9.0/static/datatype-xml.html
Or maybe with this
"An alternative solution for comparing XML values is to convert them to character strings first, but note that character string comparison has little to do with a useful XML comparison method."
So try casting reportxml to varchar
Greetings!
The inverse operation, producing a character string value from xml, uses the function xmlserialize
:
XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )
type can be character, character varying, or text (or an alias for one of those). Again, according to the SQL standard, this is the only way to convert between type xml and character types, but PostgreSQL also allows you to simply cast the value.