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:
I would like to be able to match the content of /element/items/item/data directly against a LIKE expression such as (pseudo-code):
How could I achieve this? What modules would I need?
Thanks for your help.
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 | |
Re: DBI accessing SQLite with XML data
by Jim (Curate) on Jan 01, 2013 at 04:05 UTC | |
by vagabonding electron (Curate) on Jan 01, 2013 at 09:37 UTC | |
Re: DBI accessing SQLite with XML data
by Anonymous Monk on Dec 31, 2012 at 08:55 UTC | |
by Anonymous Monk on Dec 31, 2012 at 10:34 UTC | |
by NetWallah (Canon) on Dec 31, 2012 at 17:17 UTC | |
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 | |
Re: DBI accessing SQLite with XML data
by thargas (Deacon) on Jan 02, 2013 at 13:40 UTC |
Back to
Seekers of Perl Wisdom