PostgreSQL :- XPATH Function

Background

We have our XML file imported into the database.

Objective

Let us start to make sense of of it.

XPATH

We can use the XPATH function to query it.

Data

Here is what our XML looks like.

Image

niv.xml.01.20190819.0527PM

Query

Outline

  1. Common Table Expression ( CTE )
    • To compartmentalize things a bit we use a CTE
    • XPATH
      • We issue XPATH using (/v) as the query
    • UNNEST
      • UNNEST converts the array returned by XPATH to a result set
  2. Query
    • To get the verse number we pass the query string //v/@n
    • To get the actual verse we pass along //v/text()

SQL


; with xmlNodeVerse
(
    node
)
as
(

    select 

            unnest
            (
                xpath
                (
                      '//v'
                    , contentxml
                )
            ) as verse

    from   bible.nivtext tblB

)
select
          tblXML
            as xmlFragment

        , (
            xpath
                (
                      '//v/@n'
                    , tblXML.node
                )
          )[1]::text
            as verseNumber

        , (
            xpath
            (
                  '//v/text()'
                , tblXML.node
            )
          )[1]::text
            as verse          

from   xmlNodeVerse tblXML

limit 20
; 

One thought on “PostgreSQL :- XPATH Function

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