CODEWITHSUNDEEP
xmlpostgresql
Maliari Mohammed
Maliari Mohammed

Reputation: 67

How to use clause GROUP BY xml column in postgresql?

I've created a table in Postgres that contains an XML column and I want use a clause group by. how to use it.

I've created a table in Postgres that contains an XML column:

book_id | interger
bookinfo|xml

I've inserted an XML:

enter image description here

I want to use clause GROUP BY for my query shows the number of books per sex (male, female).

Upvotes: 0

Views: 457

Answers (1)

Jim Jones
Jim Jones

Reputation: 19653

Welcome to SO.

I would suggest you to first extract the info you need using XPATH and either place it in extra columns or to use a CTE (aka WITH clause) to do the job. The latter can be done as follows:

Considering your table structure and the following data:

CREATE TABLE t (book_id INT,bookinfo XML);
INSERT INTO t VALUES 
  (1,'<information><sexe>male</sexe><age>60</age></information>'),
  (2,'<information><sexe>male</sexe><age>50</age></information>'),
  (3,'<information><sexe>female</sexe><age>42</age></information>'), 
  (4,'<information><sexe>male</sexe><age>50</age></information>');

.. perform the following query:

WITH j AS (
SELECT 
  (XPATH('/information/sexe/text()',bookinfo))[1]::TEXT AS sexe,
  (XPATH('/information/age/text()',bookinfo))[1]::TEXT AS age
FROM t)
SELECT j.age,j.sexe,COUNT(*) AS qt FROM j
GROUP BY age,sexe
ORDER BY qt DESC;

 age |  sexe  | qt 
-----+--------+----
 50  | male   |  2
 42  | female |  1
 60  | male   |  1
(3 Zeilen)

Depending on the size of your table a CTE can be quite slow. If so, consider parsing your data and storing it in extra columns.

Upvotes: 1

Related Questions