PostgreSQL :- XMLTABLE Function

Background

A far more easier choice to parse XML data is to use the XMLTable function.

Lineage

Here are past postings on XML parsing options in PostgreSQL :-

  1. XPath

 

Data

Here is a sample of the XML file.

Image

niv.xml.01.20190819.0527PM

XMLTABLE

Query

Outline

  1. XMLTable
    • Path
      • /bible/b/c/v
    • Passing
      • Column Name
        • contextxml
    • Columns
      • book
        • path ‘../../@n’
      • chapter
        • path ‘../@n’
      • verse
        • path ‘@n’
      • content
        • path ‘.’

SQL


select
        tblXML.*

from   bible.nivtext tbl

        , xmltable
            (
                -- path
                '/bible/b/c/v'

                -- column
                passing tbl.contentxml

                -- column verse Number
                columns 

                     -- book Name
                     book varchar(60)
                        path '../../@n'

                     -- chapter Number
                   , chapter int
                        path '../@n'

                    -- verse Number
                   , verse int
                        path '@n'

                    -- verse
                    , "content" varchar(4000)
                        path '.'

            )
            tblXML

limit 10 

Output

sql.xmltable.01.20190819.0557PM

Summary

XMLTable is quite promising.

One has to be on version 10 and above of PostgreSQL.

The best thing is that it is ANSI Complaint and it is very well supported across major database platforms such as IBM DB/2 and Oracle.

It is very unfortunate that SQL Server does not support it.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s