This article is half-done without your Comment! *** Please share your thoughts via Comment ***
PostgreSQL 10 introduced the concept of XML Table where we can store full XML Document.
We can retrieve the XML Document in a tabular format.
In the ETL process, we can extract XML files and directly stores into a relational table format. Later, we can apply any filters on it.
Check the below simple demonstration on this:
Create a sample XML Table:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE tbl_Students AS SELECT xml $$ $$ AS Students; |
Get XML data into Tabular format:
|
1 2 3 4 5 6 7 8 |
SELECT xmltable.* FROM tbl_Students, XMLTABLE ('/students/class/subjects/subject' PASSING Students COLUMNS id FOR ORDINALITY, Student_Name text PATH '../../name' NOT NULL ,Subject_Name text PATH '@name' ); |
Result:
|
1 2 3 4 |
id | student_name | subject_name ______________________________________________ 1 | Anvesh | English 2 | Anvesh | Computer |
