Issues with XML file import with JSL, using the Parse XML command, and accessing child tags

[#1]

Hello - I'm trying to import a few hundred XML files. These files need to come in as a single row, and some lines need to be split into multiple columns, so as far as I can tell, the XML import wizard is out. I tried using the code from the import wizard as a starting point but A) I couldn't see how to easily put in a lot of nuance (like specifying splitting of text into columns, with one column being up to a 35k entry csv formatted string) and B) I didn't find a lot of documentation on XML Settings. I did find both documentation and community support on Parse XML. However, I've been working for quite a while to adapt code I found on the forums and in the scripting index (and maybe even some rather unhelpful queries to ChatGPT) to no avail. I'm very much a noobie to jsl (and coding in general), so this might be very simple.

This is what I got right now. I had more, but I guess to start, I just want to be able to access the xml data and use the Parse command correctly, but I'm having serious trouble getting anything but a table created, and I don't seem to be grabbing the attribute correctly, as the table shows up unnamed: 

// Specify your preferred default folder
default_folder = "G:\My Drive\Edward H Chandler, Jr\VCU - Research\CERCAS\Project 46\XRD";

Set Default Directory( default_folder );

// Use the default folder to pick a file

file contents = Load Text File( Pick File(default_folder) );

Parse XML( file contents,
	On Element(
		"http://www.xrdml.com/XRDMeasurement/2.1^sample",
		StartTag(
			sheetname = XML Attr(
				"http://www.xrdml.com/XRDMeasurement/2.1^type"
			);
			dt = New Table( sheetname );
			row = 1;
			col = 1;
		)
	)
);

I'm pulling largely from here and here, but I'm noticing some undocumented syntax, like the inclusion of a second term in the XML Attr command with "Untitled" in that first link, so I'm not even sure if this code is generally applicable, though I did get it to work on the BigData file it was made for. I also am unsure about the use of the "^" to indicate children, but I'm kinda lost at this point. I suppose it could also be an issue with the namespace being titled as a webpage, but my understanding is a string is a string is a string. 

Anyway, does anyone have any suggestions or comments? I'm even open to switching back to the XML settings route. 

I've also attached one of the xml files. It was originally an .xrdml file, but my understanding is that that is structured as an xml file, so I just changed the filetype.

Thanks in advance!

[#2]

How would you like the data to look like in the end?

[#3]

Hi @jthi - so the way I was going to do it was just have all text and attributes be in separate columns, one row per file, and then certain parts of the file have text that is really two or more columns (like the 10s of thousands of entry csv formatted string toward the end or like the Lower and Upper PHD levels line in the first 20 rows). But splitting those seems to be pretty easy once I am able to actually get the values.

[#4 · Accepted solution]

Doesn't Wide Guess get you quite far?

[#5]

@jthi Thanks for the response and sorry for the late reply - Welp. Wide guess is definitely the way to go. I thought I had clicked that, but I guess I misclicked or something. Thanks for pointing it out.

Here's a further question that I'm having a little trouble with. I want to import this into an existing sheet, so like match to existing columns, and then maybe throw an error message if there's a column that doesn't match. I also want to split certain cells based on delimiters, so I guess using the Word() function. However, I'm not quite sure how to do this in a quick way. The best I can come up with is create an intermediate table, and then have the script read the table, maybe have a user input window for the row to copy to (there will be >250 rows), copy the info while splitting with Word(), throw an error message if not all column's match, and then delete the dummy intermediate table at the end (maybe with an approval "ok" button). I think I can figure out how to do that, but is there a way to do it directly into the table I'm ultimately getting the info to without that intermediate table? The XML Setting command documentation is minimal, unless I'm missing it somewhere.

Thanks!

[#6]

After you get the data to JMP table (Wide format most likely works) then you start figuring out different methods JMP can further clean/modify the data. I don't think you can directly do everything in XML Import Wizard (Parse XML most likely could do it, but it is fairly complicated to use, in my opinion).

[#7]

Yea, wide was my starting point, and then I just did a ton of editing of column names, splitting of columns, putting data from columns into the unit property of others and deleting them, etc. Final file is attached if anyone finds it useful as a guide for some things to do in cleaning up an XML file. Its not pretty, but its there.