DBI accessing SQLite with XML data

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Perlmonks,

I am querying a sqlite database which contains XML data in a TEXT column using DBI.

Here is a sample of the data contained in that column:

<element xmlnssi="http://www.w3.org/2001/XMLSchema-instance" xmlnssd="http://www.w3.org/2001/XMLSchema"><items><item><data>some text here</data></item></items></element>
Although I can successfully retrieve the content of that column via a SELECT statement and performing some search and replace to get only the content of element/items/item/data, this is unsatisfactory:

my $myQuery = $dbh->prepare( "SELECT source FROM myTable" );  

$source =~ s/<element xmlns:xsi=\"http:\/\/www.w3.org\/2001\/XMLSchema
+-instance\" xmlns:xsd=\"http:\/\/www.w3.org\/2001\/XMLSchema\"><items
+><item><data>//g;

$source =~ s/<\/data><\/item><\/items><\/element>//g;
[download]

I would like to be able to match the content of /element/items/item/data directly against a LIKE expression such as (pseudo-code):

( "SELECT ('/element/items/item/data') as source FROM myTable WHERE so
+urce LIKE '%some string%'" );
[download]

How could I achieve this? What modules would I need?
Thanks for your help.

Comment on DBI accessing SQLite with XML data Select or Download Code

Replies are listed 'Best First'.
Re: DBI accessing SQLite with XML data by bart (Canon) on Dec 31, 2012 at 12:04 UTC
It looks to me like you want something like XPath, and use it on data from the database. I'd use an ordinary "LIKE" SQL expression to weed out most of the uninteresting results in SQL, and then with XPath, refine it, and extract exactly the data you're after. As for modules, I may be biased, but I'd look at XML::LibXML's XML::LibXML::XPathContext and XML::LibXML::XPathExpression first.	[reply]
Re: DBI accessing SQLite with XML data by Jim (Curate) on Jan 01, 2013 at 04:05 UTC
You may be using the wrong database software. Appropriate Uses For SQLite Simplicity in a database engine can be either a strength or a weakness, depending on what you are trying to do. In order to achieve simplicity, SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth. If you need some of these features and do not mind the added complexity that they bring, then SQLite is probably not the database for you. SQLite is not intended to be an enterprise database engine. It is not designed to compete with Oracle or PostgreSQL. Consider using Oracle Berkeley DB XML instead of SQLite. "Oracle Berkeley DB XML is an XML database with support for XQuery designed to store and index XML content for fast, scalable and predictable access." Jim	[reply]
Re^2: DBI accessing SQLite with XML data by vagabonding electron (Curate) on Jan 01, 2013 at 09:37 UTC
BaseX could be another one: http://basex.org/. Perl API: https://github.com/BaseXdb/basex-api/tree/master/src/main/perl.	[reply]
Re: DBI accessing SQLite with XML data by Anonymous Monk on Dec 31, 2012 at 08:55 UTC
How could I achieve this? What modules would I need? ?? You can't :) end of story ?? If you have to ask, you probably can't achieve it :) otherwise you wouldn't need to ask ?? Oracle Berkeley DB XML ?? ?? XML::Twig, XML::Rules, XML::LibXML, SQL::Statement, SQL::Translator, DBD::SQLite	[reply]
Re^2: DBI accessing SQLite with XML data by Anonymous Monk on Dec 31, 2012 at 10:34 UTC
Actually I found a way using regular expressions. You have to create a custom regexp function and then use it to parse the XML (courtesy of http://www.justatheory.com/computers/databases/sqlite/). Not exactly what I was looking for and surely not very efficient performance-wise, but for now it does the trick.	[reply]
Re^3: DBI accessing SQLite with XML data by NetWallah (Canon) on Dec 31, 2012 at 17:17 UTC
Thanks for sharing that link - I believe it WOULD be efficient, and could be extremely powerful, if you embed XML processing into the function. Please post working code back, for lazy bums like me to plagiarize. Happy 2013! "By three methods we may learn wisdom: First, by reflection, which is noblest; Second, by imitation, which is easiest; and third by experience, which is the bitterest." -Confucius	[reply]
Re^4: DBI accessing SQLite with XML data by Anonymous Monk on Jan 01, 2013 at 00:21 UTC
Re: DBI accessing SQLite with XML data by Anonymous Monk on Jan 01, 2013 at 18:17 UTC
I would recommend redesigning your database schema. Parse xml and insert into table. `CREATE TABLE mytable ( id INTEGER PRIMARY KEY, element TEXT, items TEXT, item TEXT, data TEXT, UNIQUE(element, items, item, data) );` [download] Then you can do the following: `sqlite> SELECT ("element" \|\| "/" \|\| "items" \|\| "/" \|\| "item" \|\| "/" \|\| + "data") AS source where source like '%some string%';` [download]	[reply] [d/l] [select]
Re: DBI accessing SQLite with XML data by thargas (Deacon) on Jan 02, 2013 at 13:40 UTC
Another possibility (probably with abysmal performance): DBD::SQLite includes the possibility of writing your own SQL functions in perl. You could write such a function using one of the many perl XML modules. It'd be interesting as an exercise and if you don't use if much, might even perform well enough.	[reply]