Is there a way to use order by functionality with values from an XML column in PostgreSQL?
If I have this xml as a column in my PostgreSQL database in a XML column, is there a way to write a select statement to order the results by <type> attribute?
<items>
<item>
<id>1</id>
<name>Item #1</name>
<type>Hardware</type>
</item>
<item>
<id>2</id>
<name>Item #2</name>
<type>Software</type>
</item>
</items>
Thank you.
Basically, you need to get necessary XML elements with xpath() function 1st. Then get processed all result values as TEXT type for further ordering (You are not able to apply GROUP BY identifier to Postgres XML type). Actual query may be the following:
WITH extracted_data AS (
SELECT id,unnest(xpath('//type/text()',data))::TEXT AS item_type
FROM xml_test
)
SELECT id,item_type FROM extracted_data
ORDER BY id,item_type DESC;
Example DDL and data:
-- Sample DDL
CREATE TABLE xml_test (
id SERIAL PRIMARY KEY,
data XML
);
-- Sample Data
INSERT INTO xml_test(data) VALUES
('<items>
<item>
<id>1</id>
<name>Item #1</name>
<type>Hardware</type>
</item>
<item>
<id>2</id>
<name>Item #2</name>
<type>Software</type>
</item>
</items>'::XML),
('<items>
<item>
<id>3</id>
<name>Item #3</name>
<type>Middleware</type>
</item>
<item>
<id>4</id>
<name>Item #4</name>
<type>Other</type>
</item>
</items>'::XML);
