XML import

[Bill Parker]

I will be getting a data file as XML. It has multiple 1-many sturctures for each of a few thousnad "records". Of the 100 or so "fields" for each record I will use about 15. This then needs to be converted into a table (currently dbf) or CSV. So I am deciding what tools to use. Before starting to explore all the options I would welcome input.

The Xbasic XML parser from v9 is cumbersome with only a brief tutorial for documentation. No easy way to dump many data fields out of the XML, but I could probably make it work. Both this and string scanning would be tedious to use and not produce very readable code.

MS .net XmlDataDocument class, LINQ, etc. Although I need to do this before v11, are there any MS tools that would be a better long term solution to importing various XML data table structures into dbf or sql?

Bill.

[Steve Workings]

Re: XML import

Bill -

You might look into extract_all_strings()

Here's an example:

Items = extract_all_strings(e.checkboxFilterArgumentXML,"<Data Type=\"C\">","</Data>",crlf(),.f.)

A couple notes about this example:

1. I only have one piece of data I want from this XML -- the Items. You'll have to see if this makes for a good starting point for you or not.

2. Because this is a desktop forum, I'll explain that the e.checkboxFilterArgumentXML is just a variable value in XML format.

[Bill Parker]

Re: XML import

My problem is that I do need several fields from both parent and child in a 1-many structure. Alpha's XML parser is geared to extracting a single field, but has the capability to refrence additional fields by relative position. So I will explore it more to see if I can get there. But I am not encouraged after finding methods such as
KludgeAroundPreprocessorBug as N() 'Added to fix MARKED properties eating the next definition.

Bill.

[martinwcole]

Re: XML import

Bill, I don't read FROM an xml file, but I do create one and send it to TXDOT every day

If you study the xml file, which is just a text file with an xml extension, via note pad, there will be a beginning and ending point for the entire file, and a beginning and ending point for each "parent" and for each child for that parent.

I would think that it would be easy to parse out what you want with simple xbasic.

[Bill Parker]

Re: XML import

Martin, I want to come up with a more generic way to deal with XML files since I know this type of thing will come up again. String manipulation or the xbasic xml parser are good for individual values, but a pain for as much as I need. I played with the xml parser some more and had trouble navigating between parent and child nodes when I need to output parent, child and grandchild values.

I hear that PowerShell has some good XML capabilities, so I'll take a look at that.

Bill.

[aburningflame]

Re: XML import

http://wiki.alphasoftware.com/Parsin...with+Xbasic+V9

Generic ways to deal with XML are included in a lot of DEV environments.
See if the above will work for you.

[Bill Parker]

Re: XML import

Scott, that documentation is what I was referring to in the first post. For a multi-level XML I am finding the xbasic xml parsing to be quite cunbersome. Requires multiple extractions and then merging each together to get a "record". I don't think it will work for me.

If this were the xml structure, 2 extractions and a merge would get a person record.

Employee
  employee_id
  Name
    First
    Last
  Address
    address1
    city
    state

If this were the xml structure, the employee_id could not be output with the name or address.

Employee
  IDs
    employee_id
  Name
    First
    Last
  Address
    address1
    city
    state

Bill.

[aburningflame]

Re: XML import

Your 2nd xml seems illogical - unless 1 employee could have multiple ids.

Your 1st structure is what it should be.

In the second structure how do you even know what id's are mapped to which employee?
1 Employee object would not have multiple ids (in most real life situations).

YOur other option is to change your XML.
Otherwise - part of XML parsing is to do the cumbersome extract/parse - I dont think theres any way around it.

Just write a custom import/parser for that XML file.

[Bill Parker]

Re: XML import

In the second structure how do you even know what id's are mapped to which employee?
1 Employee object would not have multiple ids (in most real life situations).

This file has some 1-1 structures mapped like that. (There would be SSN in the IDs section, etc.) So some levels are used for grouping, and you just need to know what is 1-1 vs. 1-many.

I have looked briefly at PowerShell/Xpath and have tested a commercial program, Advanced XML Converter. No silver bullets so far. I like the object references that P.S. uses. Would be great if Alpha's xml parser did something similar vs. using relative references.

Bill.

[Bill Parker]

Re: XML import

This is the jist of the script I ended up with. It uses pointers vs. positional navigation to get values from the XML file. This can be helpful if your XML is more complex. Mine had 5 levels, multiple 1-many at some levels, etc. (It would be very painful if you built a script based on positional references in a complex schema and then a field was added.)

Cian saved my bacon on this with an example that showed the pointer syntax. The wiki does not show that (or the *child navigation directive which I needed in my first approach). I think Alpha is going to update the wiki with another example? Also note that autocomplete of the XML methods works great in the interactive window, but not in the script editor. Since the wiki currently does not document most of the methods, start your work in the interactive window in order to see what is available. There are still a number of interesting looking methods that are a mystery to me.

'Read XML file and convert to CSV.
Dim vTopCount as N
Dim appoint as P	'complete appointment record. parent
Dim patient as P	'patient_info child node (1-1)
Dim billing as P	'billing_info child (1-1). query for values in carrier grandchild node (1-many)
Dim transport as P	'from_and_to_info child (1-1). query for values in transport grandchild node (1-many)
Dim qry as P
Dim str as C
Dim sum_chrg as N
Dim trip as P
Dim vOutput as C = ""
debug(1)

sm = xmlSchemaManager.Get()
xml = get_from_file("D:\File Formats\CO00224MultiClaim.xml")
dom = sm.LoadXML(xml)

vTopCount = dom.top.children.size()

for n = 1 to vTopCount -2	'layout and file summary nodes are included at end.  exclude them.
	'get pointers to levels of the record
	appoint = dom.top.children[n]	'all xml for a single appointment
	patient = appoint.ChildGet("patient_info")
	billing = appoint.ChildGet("billing_info")	'2 1-many at this level, but not using <procs>
	transport = appoint.ChildGet("from_and_to_info")
	
	'get 1-1 values
	appoint_appno = appoint.ChildGet("appoint.appno").content
	patient_last = patient.ChildGet("patient.last").content
	patient_first = patient.ChildGet("patient.first").content
	'... more fields
	
	'summarize amount.  might be multiple carriers.
	qry = billing.QueryElement("*tag= 'PROCS.SUM_CHRG'")
	str = qry.Output("*value +crlf()")
	sum_chrg = 0
	cmd = *for_each(chrg, "sum_chrg = sum_chrg +" +val(chrg), str)
	evaluate_template(cmd)
	
	'get 1-many trips
	tripcount = transport.children.size()
	for m = 1 to tripcount
		trip = transport.children[m]
		toadd_num = trip.ChildGet("toadd.num").content
		toadd_tostr = trip.ChildGet("toadd.tostr").content
		'...more fields
		
		'CSV output
		vOutput = vOutput +appoint_appno -"-" +toadd_num +"," +patient_last +"," +patient_first +"," +quote(toadd_tostr) +crlf()
	next m
	
next n
end

A sample data file is attached. Remove the ".txt" from the filename You will also need to modify the path in the script.

This script takes a top down approach to getting pointers. The example in the wiki takes a bottom up approach. (The current wiki example does not use pointers.) You can use either depending on your need.

Bill.