Re: large XML data type appears empty when doing a select

El 14/01/2012 09:26 a.m., René Romero Benavides escribió:
> El 12/01/2012 12:58 p.m., Paula Price escribió:
>>
>> 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:
>
>
> 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/multibyte.html>.
> 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!
> --
> http://sharingtechknowledge.blogspot.com/
*You can also try this:*

The inverse operation, producing a character string value from xml, uses
the function |xmlserialize|:

XMLSERIALIZE ( { DOCUMENT | CONTENT }value AStype )

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.

--
http://sharingtechknowledge.blogspot.com/