Re: [sqlite] XML into sqlite

[Petite Abeille]

On Sep 11, 2014, at 5:45 PM, Carlos A. Gorricho <cgorri...@heptagongroup.co> 
wrote:

> Next step is to venture into XML - sqlite integration...both ways. 

Considering you are on a *nix system, you may find Dan Egnor’s xml2 set of 
command line utilities of interest:

http://www.ofb.net/~egnor/xml2/

Allows for rather straightforward transformation of XML into something more 
palpable, and back.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] XML into sqlite

[Carlos A. Gorricho]

For the time being, it's a one-off thing. In the future it could become regular.

I used to solve all my analytical DB needs with MS Access. Since I migrated to 
a Mac I decided to look for a new DB engine and am exploring sqlite.

I have been running a couple of tests, based on actual data problems I have 
solved in the past with Access. So far, I have been able to replicate 
everything.

Next step is to venture into XML - sqlite integration...both ways. With some 
restrictions, it worked well between Access and XML, via Excel.

Cheers,

CARLOS A.

Sent from my iPad

> On 11/09/2014, at 6:04, Simon Slavin <slav...@bigfraud.org> wrote:
> 
> 
>> On 11 Sep 2014, at 6:04am, Carlos A. Gorricho <cgorri...@heptagongroup.co> 
>> wrote:
>> 
>> How would you recommend to drop XML data into an sqlite DB? I am guessing 
>> there is more than one way.
> 
> Is this a one-time thing or are you going to have to do it every month/week ?
> 
> Do you have a favourite programming language, or do you want to do this with 
> some combination of spreadsheets, word processing or shell commands ?
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] XML into sqlite

[Simon Slavin]

On 11 Sep 2014, at 6:04am, Carlos A. Gorricho <cgorri...@heptagongroup.co> 
wrote:

> How would you recommend to drop XML data into an sqlite DB? I am guessing 
> there is more than one way.

Is this a one-time thing or are you going to have to do it every month/week ?

Do you have a favourite programming language, or do you want to do this with 
some combination of spreadsheets, word processing or shell commands ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] XML into sqlite

[Gert Van Assche]

I'm using XSLT to create CSV.

gert

2014-09-11 7:04 GMT+02:00 Carlos A. Gorricho <cgorri...@heptagongroup.co>:

>
> How would you recommend to drop XML data into an sqlite DB? I am guessing
> there is more than one way.
>
> I thought of importing XML into Excel, converting to csv, for further
> import into sqlite, now that I know how to import csv into sqlite ...
> almost always! But this seems like a long haul.
>
> Cheers,
>
> CARLOS A.
>
>
>
> Sent from my iPad
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] XML into sqlite

[Carlos A. Gorricho]

How would you recommend to drop XML data into an sqlite DB? I am guessing there 
is more than one way.

I thought of importing XML into Excel, converting to csv, for further import 
into sqlite, now that I know how to import csv into sqlite ... almost always! 
But this seems like a long haul.

Cheers,

CARLOS A.



Sent from my iPad
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] XML to SQLite upload

[A. Pagaltzis]

* Steve O'Hara <[EMAIL PROTECTED]> [2006-05-07 11:20]:
> This is the right approach, when I worked in the SGML world
> with a component versioning system, we called it the
> "non-linear" design.
> 
> By going down this road, your table schema is static and can
> cope with any type of DTD without change.

That depends.

If you want to write a generic XML store, sure, this approach is
really the only way to implement such a thing on top of a
relational database.

However, it’s not really very relational, is it? You end up with
a database that you can’t reasonably query with JOINs and
aggregate functions. And in most cases I’ve seen, when people say
they want to dump XML documents into their database, they don’t
actually want to store an XML infoset in a table. Usually they
either only have one particular XML document structure their code
needs to cope with, ie the XML is just an exchange format (souped
up CSV), and need to scatter this data into an existing schema,
or they just store XML documents wholesale in a TEXT column.

If you really do want to store an XML infoset in a table, then
the outlined approach is fine, though you’re using the database
as a very flat store, running lots of very simple, dynamically
generated queries. The SQL frontend is mostly dead weight and you
might be better off just using some storage engine with a pure
function call API then. (BerkeleyDB’s B-tree API comes to mind,
though I haven’t actually used it.)

> The next thing your tool needs to do, is to determine the
> parent-child relationships between all the rows and express
> this using primary key linking columns.

Or some other mechanism. The self-referential FK approach is only
one of many ways to represent trees in SQL, and wins mainly when
the bulk of your queries are INSERTs; in other scenarios, other
options will likely prevail.

> As you can imagine, rebuilding the relationships isn't a simple
> query - lots of self correlation etc.

Yeah, that’s the problem when retrieving hierarchical data
modelled using self-referrential FKs.

Regards,
-- 
Aristotle Pagaltzis // 

[sqlite] RE: [RBL] Re: [sqlite] XML to SQLite upload

[Steve O'Hara]

This is the right approach, when I worked in the SGML world with a
component versioning system, we called it the "non-linear" design.

By going down this road, your table schema is static and can cope with
any type of DTD without change.  However, you need to create a tool that
will convert your XML document into a list of rows by mapping elements
in the DTD - you do this by allowing the user to define what level of
"componentisation" they want to achieve in the database i.e. the tags
that will be stored in database rows.
You could componentise everything (including ,  for HTML etc) but
this could produce thousands of rows for even quite a small document.
Maybe your XML docs are relatively clean and therefore don't have too
many layers, this would allow you to do the lot.

The next thing your tool needs to do, is to determine the parent-child
relationships between all the rows and express this using primary key
linking columns.  At this stage, you could also think about versioning -
our aim was to create a database that would allow editors to modify a
particular component by creating a copy of the row in the database,
linked to the same parents and children, but with a different version
number.  This means that he could choose to print the document with
certain revisions or at a certain version number.  

The tool also obviously needs to enable you to run queries that produce
"document" results, not just tag results, which would be easy.  As you
can imagine, rebuilding the relationships isn't a simple query - lots of
self correlation etc.

I was working on this SGML stuff in 1994, way before XML came along and
the tools we had to work with were primitive by modern standards
(Avalanche, Omnimark etc) - equally, when we worked with a document that
was moderately large, it required lots of database horsepower to make it
all work.  However, the holy grail of being able to create a true
SGML/XML repository that allows multi-user, concurrent, component level
editing/versioning was just within reach, if we could just get a Cray
(whatever happened to them?) to run it on!

Just in case you're wondering about possible applications for this,
think of people like Boeing and Airbus and how they manage and control
the editing of their sets of flight manuals etc.

Steve



-Original Message-
From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
org] On Behalf Of A. Pagaltzis
Sent: 05 May 2006 06:14
To: sqlite-users@sqlite.org
Subject: [RBL] Re: [sqlite] XML to SQLite upload

Hi Vivek,

* Rajan, Vivek K <[EMAIL PROTECTED]> [2006-05-05 06:40]:
> Sorry, I was not clear in my previous email. I do know the
> structure of the for the XML - I was wondering if there was a
> direct upload capability, once I know the structure of the XML. 

well, you can map generic XML to a relational database by storing
each syntactical element of the file in a row of a table,
together with information about how the elements are nested.
(Various ways to represent trees in a relational database exist.)
However, what you get is nearly useless for the kind of querying
that you typically want to do with a database.

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>

RE: [sqlite] XML to SQLite upload

[Clay Dowling]

Rajan, Vivek K said:
> Thanks for answers.
>
> Sorry, I was not clear in my previous email. I do know the structure of
> the for the XML - I was wondering if there was a direct upload
> capability, once I know the structure of the XML.

I think that the problem is a little too generic for an existing tool. 
However writing a tool for your specific situation should be fairly easy. 
If you're comfortable with C I strongly recommend looking at libxml2
(http://www.xmlsoft.org), which makes XML parsing very easy.

Clay Dowling
-- 
Simple Content Management
http://www.ceamus.com

Re: [sqlite] XML to SQLite upload

[A. Pagaltzis]

Hi Vivek,

* Rajan, Vivek K <[EMAIL PROTECTED]> [2006-05-05 06:40]:
> Sorry, I was not clear in my previous email. I do know the
> structure of the for the XML - I was wondering if there was a
> direct upload capability, once I know the structure of the XML. 

well, you can map generic XML to a relational database by storing
each syntactical element of the file in a row of a table,
together with information about how the elements are nested.
(Various ways to represent trees in a relational database exist.)
However, what you get is nearly useless for the kind of querying
that you typically want to do with a database.

Regards,
-- 
Aristotle Pagaltzis // 

RE: [sqlite] XML to SQLite upload

[Rajan, Vivek K]

Thanks for answers. 

Sorry, I was not clear in my previous email. I do know the structure of
the for the XML - I was wondering if there was a direct upload
capability, once I know the structure of the XML. 

Vivek


>-Original Message-
>From: A. Pagaltzis [mailto:[EMAIL PROTECTED]
>Sent: Thursday, May 04, 2006 8:54 PM
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] XML to SQLite upload
>
>* John Stanton <[EMAIL PROTECTED]> [2006-05-05 05:45]:
>> We feed XML into an SQLITE database, but the XML DTD maps the
>> database in structure and names. To get general XML data and
>> load it into any database requires a program of some
>> description to translate names and structures.
>
>That was the point though. You have to assume some sort of
>convention about the structure of the XML, because there is no
>direct way to map XML into a relational model, and if Vivek Rajan
>does not tell us what he needs, we cannot tell him if such a
>thing exists or how to go about it.
>
>The question does not preclude an answer; it just gives too few
>constraints to answer it usefully.
>
>Regards,
>--
>Aristotle Pagaltzis // <http://plasmasturm.org/>

Re: [sqlite] XML to SQLite upload

[A. Pagaltzis]

* John Stanton <[EMAIL PROTECTED]> [2006-05-05 05:45]:
> We feed XML into an SQLITE database, but the XML DTD maps the
> database in structure and names. To get general XML data and
> load it into any database requires a program of some
> description to translate names and structures.

That was the point though. You have to assume some sort of
convention about the structure of the XML, because there is no
direct way to map XML into a relational model, and if Vivek Rajan
does not tell us what he needs, we cannot tell him if such a
thing exists or how to go about it.

The question does not preclude an answer; it just gives too few
constraints to answer it usefully.

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>

Re: [sqlite] XML to SQLite upload

[John Stanton]

We feed XML into an SQLITE database, but the XML DTD maps the database 
in structure and names.  To get general XML data and load it into any 
database requires a program of some description to translate names and 
structures.

JS

A. Pagaltzis wrote:

* Rajan, Vivek K <[EMAIL PROTECTED]> [2006-05-05 02:20]:

Does someone have XML to SQLite upload utility in perl/C++? 



That’s like asking if someone has an ASCII to CSV “upload
utility”. It doesn’t make any sense.

Regards,

Re: [sqlite] XML to SQLite upload

[James Bailie]

A. Pagaltzis wrote:

> * Rajan, Vivek K <[EMAIL PROTECTED]> [2006-05-05 02:20]:

>> Does someone have XML to SQLite upload utility in perl/C++?
>
> Thats like asking if someone has an ASCII to CSV upload
> utility. It doesnt make any sense.
>

He is probably looking for a utility which will serialize an XML
document into a SQLite database.  The first place to look would
be www.cpan.org.

I have written such a utility, but I wrote it as an example
program for a little lisp dialect I created for text processing,
which only runs on FreeBSD.  A Perl programmer could port it
easily enough:

http://www.jamesbailie.com/munger.html

--
James Bailie <[EMAIL PROTECTED]>
http://www.jamesbailie.com

[sqlite] XML to SQLite upload

[Danilo]

Hi,
you could be you profit this example:

FLTK + SQLite + TinyXML = rubrinive, an alternative rubric that allows
to store for every identity N addresses, N telephone numbers and N e-mails
or Web Links.
http://www.digitazero.org/?p=33
Regards, Danilo.
Home Page: http://www.digitazero.org
venerdì 5 maggio 2006


A. Pagaltzis ha scritto:

* Rajan, Vivek K <[EMAIL PROTECTED]> [2006-05-05 02:20]:
Does someone have XML to SQLite upload utility in perl/C++? 


That’s like asking if someone has an ASCII to CSV “upload
utility”. It doesn’t make any sense.

Regards,

Re: [sqlite] XML to SQLite upload

[A. Pagaltzis]

* Rajan, Vivek K <[EMAIL PROTECTED]> [2006-05-05 02:20]:
> Does someone have XML to SQLite upload utility in perl/C++? 

That’s like asking if someone has an ASCII to CSV “upload
utility”. It doesn’t make any sense.

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>

[sqlite] XML to SQLite upload

[Rajan, Vivek K]

Does someone have XML to SQLite upload utility in perl/C++? 

Vivek

Re: [sqlite] XML and SQLite?

[Jeff]

I think I have a clear idea of what to do now. 

Thanks. 


On Sat, 22 Jan 2005 23:56:48 -0800, Darren Duncan
<[EMAIL PROTECTED]> wrote:
> At 11:13 PM -0800 1/22/05, Jeff wrote:
> >I'm making something that requires XML. (ooh, secretness)
> >It would also be nice to use SQLite with it, but I am confused about
> >how much things I need to put into the database and how much that
> >needs to stay on the file system.
> >You probably didn't understand that. My describing skills suck.
> >Here is the question: Should I store XML files in a database, or
> >should I just put it on the file system. Which would be faster?
> 
> I would say that it depends on their quantity or use.  If you are
> having thousands of little XML files, then store all of them in a
> SQLite database, each one in a SQLite record, where the XML is all in
> a large text field, and other fields in the row contain a few details
> you would look it up by.  If you have just a few large XML files,
> then store them in the file system.  A database is also useful if you
> want to look up your XML document by multiple criteria rather than
> just by one. -- Darren Duncan
> 


-- 
Jeff

Re: [sqlite] XML and SQLite?

[David Wheeler]

On Jan 22, 2005, at 11:13 PM, Jeff wrote:
Here is the question: Should I store XML files in a database, or
should I just put it on the file system. Which would be faster?
The file system would be faster, since SQLite sits on top of it, 
anyway, so you have its overhead no matter what.

Regards,
David

[sqlite] XML and SQLite?

[Jeff]

I'm making something that requires XML. (ooh, secretness)

It would also be nice to use SQLite with it, but I am confused about
how much things I need to put into the database and how much that
needs to stay on the file system.

You probably didn't understand that. My describing skills suck.

Here is the question: Should I store XML files in a database, or
should I just put it on the file system. Which would be faster?

-- 
Jeff