Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Lynn]

Hi,

What tools do I need convert the XML from a library export to a format that Access 2000 can import? What steps are involved.

Also, does someone have  a schema for the XML?

Thanks!

[sraymond]

What "schema" are you looking for?  There are literally millions of them out there!

You want to take the "MC Library.xml" and import it into an Access database table with each item as a record of a table with the "fields" as columns?

XMLSpy will quickly import an XML document into a database, but it gets a little funky with the keys.  I tried this with MC's XML and it created a table with each "field" as a record - though it added a key to tell which "item" the field came from.  I take it that's not what you're looking for.

Give me the empty Access table that you want "filled" and I can write you an XSLT to transform the MC XML into an "import-ready-for-Access" XML.

Scott-

Scott-

[phelt]

Scott - That XSLT would be most welcome to others as well. Hopefully J River could work out the hosting  

[sraymond]

The easiest and most efficient way of getting the XML into Access might be through the use of a CSV transformation.  The only problem would be that "commas" are present in the field values.  So how about "percentsign separated values"?

Using the following XSLT (saved as MCLibrary.xsl):

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xsl:output method="text"/>
     <xsl:template match="/">
           <xsl:call-template name="OutputHeaderRow"/>
           <xsl:for-each select="/MPL/Item">
                 <xsl:for-each select="Field">
                       <xsl:value-of select="."/>
                       <xsl:text>%</xsl:text>
                 </xsl:for-each>
                 <xsl:text>
</xsl:text>
           </xsl:for-each>
     </xsl:template>
     <xsl:template name="OutputHeaderRow">
           <xsl:for-each select="/MPL/Item[1]/*">
                 <xsl:value-of select="@Name"/>
                 <xsl:text>%</xsl:text>
           </xsl:for-each>
           <xsl:text>
</xsl:text>
     </xsl:template>
</xsl:stylesheet>

you can use MSXSL (or any other convenient XSLT processor) by putting the file MCLibary.xml and MCLibrary.xsl in the same directory as the MSXSL.exe and typing:

msxsl MCLibrary.xml MCLibrary.xsl -o MCLibrary.csv

This will create a file (MCLibrary.csv) that is easily imported into Access.  Don't forget to change the delimiter to "%".  Of course, if there are "%" in the field values, you'll need to choose another delimiter.

I hope this is of some help to people.  If I get some free time, I might try to do an XML-XML transformation that easily imports into Access.  The hard part is dealing with the field names - they can't be used as the element names.  But the end product would be better than having to deal with which delimiter works!

Scott-

[phelt]

Sweet. Much appreciated. But also reminds me how much I need to catch up with XML...

[LonWar]

I assume that you also have excel.
Open excel find and open the xml file, save as .txt

Now import it into access like that...

[sraymond]

I assume that you also have excel.  
Open excel find and open the xml file, save as .txt

Now import it into access like that...

Does this create a table of fields with each row as a record?  I tried it and it looked horrible.  Same problem with importing the original XML directly into Access - the schema isn't what I'd want.

Scott-

[LonWar]

For access, you'd just want the data...right?
From here you can create your own front end.
You would however have to remove the 4 blanc lines at the top, before you saved it to txt.

[sraymond]

What I meant was that it didn't seem to create a single "item" table with each MC field name as a database field and each record as an MC item.

Of course, you could manually massage the DB to end up like this, but that's a bit of work.  And it would likely need "redoing" every time you added a new field to MC (which would add a new field to the DB).

Scott-

[Lynn]

Thanks for all of the helpful posts, your help is appreciated. Unfortunately, I have not been able to build the access table.

The single table, with a single entry per item containing all fields is what I would like too.   Given this table, one could then use access to normalize the data, or whatever.

I tried the xsl in the previous post, but I receive an error about an illegal character and processing stops.  Is there something else I need to do?

Thanks again,
Lynn

[sraymond]

Lynn,

If I understand what you want to do (which is what makes sense to me as well), is it true that a delimited text file should have the first row as a header containing all the field names and the remaining rows are one per <item>?

This is what the XSLT I posted does.  I'm not sure what "illegal character" is causing a problem - if you'd like to e-mail me your exported XML, I'll see if I can figure out the problem.  Send it to scottraymond@sbcglobal.net - zip it up if it's more than a couple of megs.

What version of MSXSL are you using?

Scott-