Importing XML questions

[toolUser]

Hi,

I'm trying to import an XML file, snippet attached. It appears I need to create a XSLT file. I haven't messed with XML, so its not clear what I need to do.

Here's the error I get on import:

XML parsing error:

Attribute 'EXPIRED' is not declared for element 'PATCH'

Line Number: 2

Column Number: 405463

I assume that the fact that EXPIRED is not "declared" is due needing a style sheet.

The table I'm trying to import into has fields for each attribute in the XML file.

I've read some about XSLT files and it appears I need to include some formating info; file type etc. What do I need? I expected there might be some info in FM help - virtually nothing.

I haven't been able to find any samples or templates. Are there examples of an XML file to be imported and its correct XSLT file?

Given that the original file exists in a parsable file type, I'd think there would be a program or script that would create the XSLT from an XML. Do you know of any?

Thanks,

Jerry

Sample.txt

[DykstrL]

Your attached file does not look like a standard XML file - it does not follow any of the standard conventions. I'm guessing that it comes from an application or website (probably Microsoft) that does not really speak XML, but is creating a TEXT file in some proprietary format made to "look" like XML. It almost looks like InfoPath "XML" data, which is not. InfoPath XML data must be run through a converter to make a true XML data file, which still is a (Microsoft) proprietary data structure.

If it is not true XML, an XLST file will probably not help because the schema and formatting of XLST is required to be very specific or an error is produced when parsing the XML data, which is what it's supposed to do.

[Fenton]

I tried to get <Installed> and <Applicable> to come in as the name of their element. But I failed. I'm sure someone who "really" knows XSL could get it. So I just did the fields in 2 separate chunks, one for each. Other than that it's pretty straightforward (as these things go):

[ I'm sorry. I attached a file to this post, then I went to switch it for a newer one, but apparently I only managed to delete the first. It was late. So the attached file (FileMaker, XML, XSL) is on the next post. ]

[toolUser]

Thanks, I appreciate that. especially Fenton going to all that trouble.

You are right, DykstrL, it was generated from Microsoft's Office Inventory Convert.exe. Which is their product for interpreting a previous scan for Windows Office products updates. I'm using it to roll my own Office Updater and using FM to join the Updates table to the Computer info table.

Having said that, I have no idea what would make it not a true XML file, aren't they simply text? Why do you think it's not. BTW, I cut and pasted text from the original, is that the problem?

I can import it into Excel and export a text file, but, a. it's kluge and not very scriptable, and b. Importing it into Excel does some odd things, such as; it opens as three files. The file itself, book1, and book2. It looks like I am able to save the data correctly as text though.

So, what the XSLT file is doing is converting the original XML to another XML file with the tags FM expects?

I'll play with the your XSLT, Fenton.

So, no XSLT converters out there?

Jerry

[Fenton]

>So, what the XSLT file is doing is converting the original XML to another XML file with the tags FM expects?

Exactly. XML is short for "extensible markup language." It is cannot be compared to simple "formats" like "comma-separated." It is extremely flexible, and well, extensible :-]

HTML comes from the same parent as XML, which is SGML (too complex for we mere humans). But HTML is so weak and sloppy as to be of little use for organizing information. It is also "fixed"; you can't just make up your own HTML tags (though some companies try to).

XML on the other hand, can label each piece of info, within a hierarchy, so that it can be extracted. XSL (extensible stylesheet language) is how you specify what you want to extract, and what form it should take. It can take only specified parts of the original XML, or can add other things, like HTML tags and text.

Both the form of the XML and XSL are user-defined. So there can not be such a thing as a generic XSL converter. The XSL generally must be custom built to the XML, for what you want. It can even do such things as Finds, Sorts, Ifs, Choose, and several functions; much like FileMaker (though usually clunkier, IMHO).

And this is all just using plain text files; that's what makes it both amazing and useful. We all can read and write text files.

FileMaker requires that you tell it what fields you want to import into. When you import XML, with an XSL file telling FileMaker what's what, you will see the "fields" that you defined in the Import dialog; just like if you were importing from another "intelligent" format such as an Excel file or another FileMaker file. You have full functionality, which you do not have with "dumb" formats like tab or csv.

I left one field out of the XSL, the "DETINFOBUILD" So, I added it. And it was easy to line up the fields in FileMaker; I just used "matching names." Try that with .csv :-/

[toolUser]

Great! I think I see it.

In any case it's happily importing the XML.

Your code really helped me understand.

Actually I think I can write a script that will create the XSLT, I'll pass it along when done.

Jerry

[Fenton]

Yes, an XSLT file is just text. Whitespace (tabs, returns, etc.) don't even matter. (I set the XSL to explicitly create them in the resulting XML, so I could read it, with: indent="yes"; just say "no". FileMaker doesn't care.)

So you can copy the whole XSLT and put it into a text field (probably global storage). Then Export Field Contents to recreate it where it should be, with the right name.

The location would matter if you were on a network; it would have to be a shared folder. Or on an web site (I imagine; haven't done that).

[Fenton]

This is the piece of the puzzle that I couldn't get the first time:

<xsl:value-of select="name(../.)" />

The name() function. That will get the <Installed> or <Applicable> out of the element name. So it can all be done in one block.

That may be what DykstrL meant about it not being standard XML. They've put important data inside the element name itself, with no content. It shows the flexibility of XSL that we can still get it out as data (well, not at first, but eventually :-)

Patches.zip

[DykstrL]

That's exactly what I was talking about. It's the problem with Microsoft-based XML, it's got a lot of XML elements, but they also use non-standard-non-XML stuff in there that works fine with other Microsoft products.

That InfoPath converter I mentioned is what allows MS Office to exchange XML data with other XML-aware applications.

If you've ever looked at the HTML code created when doing a web page in WORD, it's mostly HTML, but there's some really ugly unapproved code in there that will work really well in Internet Explorer, but not any other browser.

The exception to the Microsoft bastardizing of XML is SQL 6.5, which is well defined XML data.

[cjaeger]

Can't see anything special in the XML file. It's perfectly legal XML, it only seems to have to be imported into 2 tables : APPLICABLE and INSTALLED. Of course, settting a status flag is more intelligent than moving records between two tables, but who says Microsoft has to be?

If you want to see strange XML, just try parsing Apple's PLIST format into FileMaker.

As with WORD HTML - it's got a lot better in the recent versions. The MS guys now try to comply with standards. probaby makes development easier. (and "stealing" from open source projects, maybe ....).

... Good Job, fenton.

[Fenton]

I agree that the generating application probably had everything split between Installed and Applicable; so that's the way it spit it out.

You don't happen to have a PLIST XSL example :-? I think it will use something like "following-sibling", or the position() function. It's something like the "dictionary terms" of HTML. It's a very (too) generic simple list. But I've never seen an XSL for it.

Many Mac programs use that PLIST form for exporting, especially book/library apps, so I imagine I'll have to figure it out eventually.