We have our XML file imported into the database.
Let us start to make sense of of it.
We can use the XPATH function to query it.
Here is what our XML looks like.
- Common Table Expression ( CTE )
- To compartmentalize things a bit we use a CTE
- We issue XPATH using (/v) as the query
- UNNEST converts the array returned by XPATH to a result set
- To get the verse number we pass the query string //v/@n
- To get the actual verse we pass along //v/text()
; with xmlNodeVerse ( node ) as ( select unnest ( xpath ( '//v' , contentxml ) ) as verse from bible.nivtext tblB ) select tblXML as xmlFragment , ( xpath ( '//v/@n' , tblXML.node ) )::text as verseNumber , ( xpath ( '//v/text()' , tblXML.node ) )::text as verse from xmlNodeVerse tblXML limit 20 ;