HowTo - How to load XML files into a database with Tabulify

HowTo - How to load XML files into a database with Tabulify

Howto

This database howto will show you how to load Xml file and document into a database.

Steps

Checking that Postgres is up

Normally our standard database howto are created with Sqlite but because Sqlite does not support any SQL XML function, nor native XML data type, we have created this howto with the Postgres database.

The next steps will be using the postgres howto connection.

tabli connection ping postgres
The connection (postgres) has been pinged successfully

The XML file to load

We will load the XML file relation.xml

<?xml version="1.0"?>
<api batchcomplete="">
  <query>
    <normalized>
      <n from="Relation_(database)" to="Relation (database)"/>
    </normalized>
    <pages>
      <page _idx="23720058" pageid="23720058" ns="0" title="Relation (database)" description="The base data structure of relational databases">
        <categories>
          <cl ns="14" title="Category:Articles with example SQL code"/>
          <cl ns="14" title="Category:Database management systems"/>
          <cl ns="14" title="Category:Relational model"/>
        </categories>
      </page>
    </pages>
  </query>
</api>

The structure of an XML file

Default

An XML resource is seen by Tabulify by default as a tabular resource that have:

  • one column with the name xml and the xml data type
  • one row.
tabli data struct xml/[email protected]
Structure of the resource xml\[email protected]
position   name   data_type   precision   scale   primary_key   nullable   autoincrement   comment   
--------   ----   ---------   ---------   -----   -----------   --------   -------------   -------   
1          xml    sqlxml                                        x          false

Changing the XML column name

You can change the name of the xml column with the columnName attribute

For instance, to name the column data, you would set the --attribute option with the value column_name=data

tabli data struct --attribute column_name=data xml/[email protected]
Structure of the resource xml\[email protected]
position   name   data_type   precision   scale   primary_key   nullable   autoincrement   comment   
--------   ----   ---------   ---------   -----   -----------   --------   -------------   -------   
1          data   sqlxml                                        x          false

Adding context with virtual columns

You can add extra information about the XML resource loaded via the virtual column.

For instance, if you want to add the logical name.

tabli data struct --virtual-column filename=logical_name xml/[email protected]
Structure of the resource xml\[email protected]
position   name       data_type   precision   scale   primary_key   nullable   autoincrement   comment   
--------   --------   ---------   ---------   -----   -----------   --------   -------------   -------   
1          xml        sqlxml                                        x          false                     
2          filename   varchar                                       x          false

Loading the XML file into the Postgres database

To load an XML file, you can use any transfer command.

We will be using the insert command that will:

tabli data insert --virtual-column name=logicalName xml/[email protected] [email protected]
Source                   Target               Latency (ms)   Row Count   Error   Message   
----------------------   ------------------   ------------   ---------   -----   -------   
xml\[email protected]   [email protected]   68             1

We can see that there is one row loaded in the wikipedia table with the list command

tabli data list -a count [email protected]
count   
-----   
    1

Loading from a Web API

Tabulify supports also to load XML data (even JSON) from a URL.

Let's add the wikipedia XML data for the page SQL wikipedia page

This command line will work only with a bash based terminal. More … see Why Bash

tabli data insert "https://en.wikipedia.org/w/api.php?action=query&titles=SQL&format=xml&prop=description|categories" [email protected]
Source                                                                                                                           Target               Latency (ms)   Row Count   Error   Message   
------------------------------------------------------------------------------------------------------------------------------   ------------------   ------------   ---------   -----   -------   
https://en.wikipedia.org/w/api.php?action=query&titles=SQL&format=xml&prop=description%[email protected]:--en.wikipedia.org-w   [email protected]   690            1

And we have now 2 rows

tabli data list -a count [email protected]
count   
-----   
    2

Analyzing the XML with the SQL XML functions

Now that our XML have been loaded, we can analyze them with SQL XML function. Postgres documents all this XML function on this page.

For instance, to extract the title and description, we can use the xpath function

SELECT
  CAST((xpath('/api/query/pages/page/@title', xml))[1] AS text) AS title,
  CAST((xpath('/api/query/pages/page/@description', xml))[1] AS text) AS description
FROM wikipedia;

Let's run this Query SQL select

tabli data print (postgres/[email protected])@postgres
title                 description                                               
-------------------   -------------------------------------------------------   
Relation (database)   The base data structure of relational databases           
SQL                   Language for management and use of relational databases

Next

You can explore further, the XML Postgres functions

select 
    xml, -- Because XML is an XML object, you will normally not see the content in a database IDE
    cast(xml as text) -- By casting it as text, you will see the content
    xml is document,  -- You can also check that this is a XML document
    xml_is_well_formed_document(cast(xml as text)), -- And that it's well formed
from 
    wikipedia;

or you can also try to load JSON document.

Powered by ComboStrap