Import InforPath XML into Access Using XSL

[ridawg]

Hey,

I have an InfoPath form that I would like to import into an Access database.
The problem I've run into is that when I try to import the form it creates
multiple tables due to the way the form is setup. I would like the import to
work like it does when using Excel (only one row is created). I've tried
writing my own XSL to transform the InfoPath form to the format I want but I
can't seem to get it to work. Here are the problems I'm having.

1. I can't seem to get Access to work with an XSL file. I took the InfoPath
form I have and stripped it down to where I only have the XML structure.
Next, I created an XSL file. I associated the XML file to the XSL file and
displayed it in the browser no problem (just testing to see if everything
appeared to be working. But when I try to import the XML file and select
Transform then select my XSL file in the Access import wizard I receive the
following error message: "The data was transformed in a format that cannot be
imported.".

2. The next problem I've run into when trying to transform my InfoPath form
has to do with declaring namespaces. When I try to import my InfoPath form
into Access using an XSL file I get an error message saying that the "my"
namspace has not been declared. I can't seem to figure out how to declare
this namespace.

I've exported the files that make up my template to try and figure some of
this out but I haven't had any luck. I spent a couple of days looking for
examples but haven't had much luck with that either.

I know I can use InfoPath with Excel and VBA to automate the import of the
data into Access but I was trying to skip the Excel step and import directly
into Access.

Any help is greatly appreciated!

Example of my InfoPath form:

<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution
solutionVersion="1.0.0.88" productVersion="11.0.6357" PIVersion="1.0.0.0"
href="file:///\\fpkeene\sdrive\Data\HOMarket\Reports\Monthly\Call%20Log\Database\Call%20Log.xsn"
name="urn:schemas-microsoft-comffice:infopath:Call-Log:-myXSD-2005-08-15T17-33-47"
?><?mso-application progid="InfoPath.Document"?><my:CallLog
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"
xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-08-15T17:33:47"
xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">
<my:Calls>
<my:Call>
<my:Assigned_To>
<my:Area>Products</my:Area>
<my:Name>John Smith</my:Name>
</my:Assigned_To>
<my:Title>Test the title</my:Title>
<my:ID>id1-9212005</my:ID>
<my:Opened_By>Jay Smith</my:Opened_By>
<my:Subject>Commercial Lines</my:Subject>
<myriority>Medium</myriority>
<my:Status>Completed</my:Status>
<my:Type>Problem</my:Type>
<my:System>Billing</my:System>
<my:Closed>
<my:Closed_By>Jack</my:Closed_By>
<my:Resolution>done</my:Resolution>
</my:Closed>
<my:Origin>Other</my:Origin>
<my:Caller_Name>Tester</my:Caller_Name>
</my:Call>
</my:Calls>
<my:Filter_Calls>Display All</my:Filter_Calls>
</my:CallLog>

Example of my XSL:
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:transform version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<xsl:for-each select="my:CallLog/my:Calls/my:Call">
<ID><xsl:value-of select="my:ID"/></ID>
<Title><xsl:value-of select="my:Title"/></Title>
<Origin><xsl:value-of select="my:Origin"/></Origin>
<Caller_Name><xsl:value-of select="my:Caller_Name"/></Caller_Name>
<Opened_By><xsl:value-of select="my:Opened_By"/></Opened_By>
<Subject><xsl:value-of select="my:Subject"/></Subject>
<Type><xsl:value-of select="my:Type"/></Type>
<System><xsl:value-of select="my:System"/></System>
<Priority><xsl:value-of select="myriority"/></Priority>
<Status><xsl:value-of select="my:Status"/></Status>
<Description><xsl:value-of select="myescription"/></Description>
<Area><xsl:value-of select="my:Assigned_To/my:Area"/></Area>
<Assigned_To_Name><xsl:value-of
select="my:Assigned_To/my:Name"/></Assigned_To_Name>
<Closed_By><xsl:value-of select="my:Closed/my:Closed_By"/></Closed_By>
<Resolution><xsl:value-of select="my:Closed/my:Resolution"/></Resolution>
</xsl:for-each>
</xsl:template>
</xsl:transform>