XML Conversion

[Alodar]

So I've been dealing with a lot of xml files lately, and I end up converting them to csvs and the like, and then putting them into a database.  Which is fine, but honestly, the process bothers me.  I'm not quite up to writing my own xml parser, so obviously I'd want to use MSXML or something like that, but I'd like to get some advice on structure.

Obviously, if this is something I should be picking up a book on, please tell me and if you're feeling generous, even tell me which book I should be reading, but what I'd like to get to is how to parse out an xml file into separate structures; i.e. the parent items and child items into different arrays (or variables?).  This is the part I'm not sure about; how I should be parsing it out.  I feel like the obvious answer is to parse it into a set of arrays with one being the 'parent' and the other(s?) being the child elements, which I would know ahead of time from the schema, but is there a better method that I'm just not aware of?

[mLipok]

Did you check this following thread:

 

?

 

[Alodar]

Yeah.  I was more asking for what 'should' I do as a concept, rather than a 'how'.

Dealing with an XML file in and of itself is ok, and the UDF listed would work great for it, I was just wondering if there was a better method than my original thought of creating different arrays for 'parent' and 'child' items.

Eventually, the xml data I deal with will be going in to tables, so the most effective way to deal with it is kind of what I'm after.

[TheSaint]

Is the XML a source you have no control over?

Personally, I dislike XML, and have recently come to grips with SQL which is far superior ... including great helper programs.

Any XML I come across these days,I just read into an array and deal with it much faster that way ... but of course you need to know your source, and your needs be simplistic.

 

[Alodar]

Mostly it's that.  I have control over it once I download it etc, but as for creating it from the source data or anything else, no, I have no control.  It's a file sent to me that I then have to manipulate.

[mLipok]

What a specyfic format you have ?

 

[jchd]

Since you convert XML into CVS I understand your format is fixed and doesn't rely on variable structure XML allows.

Maybe you could just parse the XML source using a regexp then directly populate your DB from the resulting array, all within AutoIt.
Another avenue: eqsily convert fixed-format XML inJSON then directly import it into DB; SQLite handles that for instance. The drawback is then that the data is still encapsulated in JSON, but specific SQL[ite]functions permit selecting stuff in there as well, albeit not as easily compared to full-fledged SQL tables.

[TheDcoder]

4 hours ago, jchd said:

eqsily convert fixed-format XML inJSON then directly import it into DB; SQLite handles that for instance.

Does SQLite handle the converstion from XML to JSON as well? If not, how would we convert fixed-format XML to JSON?

[jchd]

"No" and "StringRegExp" resp.

[TheDcoder]

@jchd I'd not classify writing RegEx for converting XML to JSON "easy", I recently tried to do something similar (stripping elements from XML) using RegEx and even it was not too easy with some trivial bug . Unless someone has already written RegEx for converting XML -> JSON?

[jchd]

Remember we're talking of a (presumably) fixed-format XML source, convertible to pure CSV as a routine task. That means an array of series of similar entities.

I'd wouldn't call "difficult" the task to convert from the first form to any of the next:

XML
<people>
    <firstname>Jimmy</firstname><lastname>Hendrix</lastname>
    <firstname>Jim</firstname><lastname>Morrison</lastname>
    <firstname>Frank</firstname><lastname>Zappa</lastname>
    <firstname>Robert</firstname><lastname>Wyatt</lastname>
    <firstname>John</firstname><lastname>Coltrane</lastname>
</people>

JSON
{
    "people": [
        { "firstname":"Jimmy", "lastname":"Hendrix" },
        { "firstname":"Jim", "lastname":"Morrison" },
        { "firstname":"Frank", "lastname":"Zappa" },
        { "firstname":"Robert", "lastname":"Wyatt" },
        { "firstname":"John", "lastname":"Coltrane" }
    ]
}

CSV
"firstname","lastname"
"Jimmy","Hendrix"
"Jim","Morrison"
"Frank","Zappa"
"Robert","Wyatt"
"John","Coltrane"

SQL
insert into People ("firstname", "lastname") values
  ('Jimmy', 'Hendrix'),
  ('Jim', 'Morrison'),
  ('Frank', 'Zappa'),
  ('Robert', 'Wyatt'),
  ('John', 'Coltrane')
;

Very basic string manipulation indeed.

[TheDcoder]

@jchd I see, it would be easy enough to process fixed-format XML I guess... Thanks for example

[jchd]

It's still plain that arbitrarily nested XML or JSON isn't a suitable source for CVS nor SQL table (both being equivalent to 2D arrays), but as far as I correctly understood OP's context, this isn't the situation he has to handle.

Edited July 25, 2018 by jchd

[Alodar]

Indded.  What I have is a context with mutiple (variable number) of subset items within it that i need to split.

e.g.

<projects>
    <projectlist>
        <projectid>Project1</projectid>
        <tasks>
          <task>Code1</task>
              <taskinfo>blah blah</taskinfo>
              <taskinfo2>blah blah</taskinfo2>
              <taskinfo3>blah blah</taskinfo3>
          <task>Code2</task>
              <taskinfo>blah blah</taskinfo>
              <taskinfo2>blah blah</taskinfo2>
              <taskinfo3>blah blah</taskinfo3>
        </tasks>
    </projectlist>
    <projectlist>
        <projectid>Project2</projectid>
        <tasks>
          <task>Code1</task>
              <taskinfo>blah blah</taskinfo>
              <taskinfo2>blah blah</taskinfo2>
              <taskinfo3>blah blah</taskinfo3>
          <task>Code2</task>
              <taskinfo>blah blah</taskinfo>
              <taskinfo2>blah blah</taskinfo2>
              <taskinfo3>blah blah</taskinfo3>
        </tasks>
    </projectlist>
</projects>

 

And it's a huge list; 1000 items+.  My current plan of using XML.au3 and then looping through each 'set' and placing it in arrays and then writing it to separate csvs (one for tasks and their info, and one for project id's and it's info - it has info the same way the tasks do) works, but it's a bit slow.  Perhaps clunky?  Dunno, but i can't think of a faster / smoother way to do it?

[jchd]

I see; the structure you get isn't as "flat" as you would want for a simple conversion.

If your RDBMS offers support for JSON with functions to search the JSON tree, you could import the source convertedto JSON. Then use DB functions to populate regular tables from there.

Alternatively, searching the XML by regexp to first get an array of projects, then for each project an array of tasks then for each task an array of steps. From there you can directly populate the SQL DB tables with their foreign keys to link data between entities. That would be my own choice.

[Alodar]

I'd love to do that, but unfortunately I'm only allowed to submit csv files to it.

Unless there's a faster way to read the xml (300mb), then I don't know what to do.  I like the idea of using regex to read it line by line, but will that be qualitatively faster than using the XML dom?

[jchd]

Only real-world tests will say.

[kammses9]

You may also use plugins for XML Conversion.