Postgresql Query to select entry keys in bytea field

Question

I'm working with a Postgresql DB. table is ticketlines containes a bytea field called attributes

Sample of field contents in JSON view:

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
    <properties>
    <comment>Ticket Attributes</comment>
    <entry key="product.candiscount">true</entry>
    <entry key="product.categoryid">8410a5e3-fc31-4370-b7da-d8406d80ca97</entry>
    <entry key="product.kitchen">false</entry>
    <entry key="product.com">false</entry>
    <entry key="product.alwaysavailable">false</entry>
    <entry key="product.service">true</entry>
    <entry key="product.warranty">false</entry>
    <entry key="product.taxcategoryid">000</entry>
    <entry key="product.vprice">false</entry>
    <entry key="product.discounted">no</entry>
    <entry key="notes">006942</entry>
    <entry key="product.texttip"/>
    <entry key="product.nosc">0</entry>
    <entry key="product.promotionadded">false</entry>
    <entry key="product.name">Pawn Payout</entry>
    <entry key="product.code">pp</entry>
    <entry key="product.ispack">false</entry>
    <entry key="product.alias"/>
    <entry key="product.verpatrib">false</entry>
    <entry key="product.managestock">true</entry>
    </properties>

Is there a simple way to query this data for reporting in a SELECT statement (ie; SELECT product.name, notes ) ?

I'm close with this: but obviously have the structure wrong: SELECT xpath('//properties:notes/text()', CAST(convert_from(attributes, 'UTF-8') AS XML)) FROM ticketlines where ticket='7abc41d9-0d7f-4187-bb81-e4139d7728bf'

the results are: ERROR: invalid XML content DETAIL: line 2: StartTag: invalid element name ^ *** Error *** ERROR: invalid XML content SQL state: 2200N Detail: line 2: StartTag: invalid element name

It's from pglobeditor.exe, The xml, json, and text modes all look this way. The application runs in Java, and I assume that JSON is how it is done. — Aaron Mills
– Aaron Mills, Commented Jul 7, 2020 at 11:36
This section of text, is simply here to show the contents of the bytea field, with the intent of someone to assist querying the data out of the field, no intention to argue over layout differences between XML, text, Jason, or whatever. — Aaron Mills
– Aaron Mills, Commented Jul 7, 2020 at 23:49
Then the answer is a resounding "no". If the structure of the data is unspecified, you cannot expect to be able to extract elements from it. — Laurenz Albe
– Laurenz Albe, Commented Jul 8, 2020 at 5:25

Alex Funk · Accepted Answer · 2020-07-11 07:51:37Z

0

I did recreate your table from what you wrote and had success with this query:

SELECT xpath('/properties/entry[@key=''notes'']/text()', CAST(convert_from(attributes, 'UTF-8') AS XML)) AS result FROM ticketlines where ticket  =1;

Though I did not reproduce your error message with your query. Please check what the real content of the ticket 7abc41d9-0d7f-4187-bb81-e4139d7728bf in your DB-Table is.

answered Jul 11, 2020 at 7:51

Alex Funk

3562 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aaron Mills Over a year ago

Thanks.Still no go. I ran query as: SELECT xpath('/properties/entry[@key=''notes'']/text()', CAST(convert_from(attributes, 'UTF-8') AS XML)) AS result FROM ticketlines where ticket ='7abc41d9-0d7f-4187-bb81-e4139d7728bf'; RESULT: ERROR: invalid XML content DETAIL: line 2: StartTag: invalid element name <!DOCTYPE properties SYSTEM "java.sun.com/dtd/properties.dtd"> ^ ********** Error ********** ERROR: invalid XML content SQL state: 2200N Detail: line 2: StartTag: invalid element name <!DOCTYPE properties SYSTEM "java.sun.com/dtd/properties.dtd">

Alex Funk Over a year ago

What version of Postgres do you use? Mine was 11.5. You can check by SELECT Version(); in SQL when in doubt. A quick internet search yield a post from 2010 with a similar issue with DOCTYPE in Postgres.

Aaron Mills Over a year ago

Version 9.4.5 also, here is a link to a backup of that table: drive.google.com/file/d/196LfpsClRXL64TXGkOh3F4Dn_7GwIEH1/…

Alex Funk Over a year ago

Your postgres Version 9.4.5 is old. If I downgrade my docker container to a postgres:9.4.5 image I can reproduce your error message otherwise I can not.

Aaron Mills Over a year ago

Thank you.I will work on migrating to a newer version.

|

Laurenz Albe · Accepted Answer · 2020-07-16 15:34:49Z

0

It seems like the contents of the bytea are no proper XML - look at the convert_from result.

With the XML you show in your question, the correct XPath query would be

SELECT xpath('//entry[@key=''notes'']/text()', xml) FROM ...

That would get the contents of the "entry" element where the value of the "key" attribute is notes.

answered Jul 16, 2020 at 15:34

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Collectives™ on Stack Overflow

Postgresql Query to select entry keys in bytea field

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related