Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

DBI accessing SQLite with XML data

by Anonymous Monk
on Dec 31, 2012 at 08:33 UTC ( [id://1011021]=perlquestion: print w/replies, xml ) Need Help??

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;

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%'" );

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

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.

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

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

      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.
        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

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) );
    Then you can do the following:
    sqlite> SELECT ("element" || "/" || "items" || "/" || "item" || "/" || + "data") AS source where source like '%some string%';
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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1011021]
Approved by Corion
Front-paged by Jim
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others having a coffee break in the Monastery: (5)
As of 2024-03-28 13:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found