postgres=# SELECT t.id, x.* FROM employees t, XMLTABLE ('/Employees/Employee[age>40]' PASSING t.data COLUMNS firstname VARCHAR(30) PATH 'firstname', lastname VARCHAR(30) PATH 'lastname', age VARCHAR(30) PATH 'age') x WHERE t.id = 1; ┌────┬───────────┬──────────┬─────┐ │ id │ firstname │ lastname │ age │ ╞════╪═══════════╪══════════╪═════╡ │ 1 │ Jim │ Moriarty │ 52 │ │ 1 │ Mycroft │ Holmes │ 41 │ └────┴───────────┴──────────┴─────┘ (2 rows) Time: 1.619 ms postgres=# SELECT t.id, x.* FROM employees t, XMLTABLE ('/Employees/Employee[@emplid=2222]' PASSING t.data COLUMNS firstname VARCHAR(30) PATH 'firstname', lastname VARCHAR(30) PATH 'lastname') x WHERE t.id = 1; ┌────┬───────────┬──────────┐ │ id │ firstname │ lastname │ ╞════╪═══════════╪══════════╡ │ 1 │ Sherlock │ Homes │ └────┴───────────┴──────────┘ (1 row) Time: 1.606 ms postgres=# SELECT emp.id, x.* FROM employees emp, XMLTABLE ('/Employees/Employee' PASSING emp.data COLUMNS firstname VARCHAR(30) PATH 'firstname', type VARCHAR(30) PATH '@type') x; ┌────┬───────────┬───────┐ │ id │ firstname │ type │ ╞════╪═══════════╪═══════╡ │ 1 │ John │ admin │ │ 1 │ Sherlock │ admin │ │ 1 │ Jim │ user │ │ 1 │ Mycroft │ user │ └────┴───────────┴───────┘ (4 rows) Time: 1.556 ms
Please, test it, check it.
nice nice, Good work!!!, thanks!!! :D
ReplyDeleteAwesome!!
ReplyDeleteAwesome!!
ReplyDelete