Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Importing XML with XSLT to multiple tables without unique key

    Hello,

    I am currently trying to import an XML file into two tables in a database. One table is a listing of an event; the other table is listing actions that happened during that event. However, in the XML file, there is nothing that uniquely identifies the event from any other. So, there is also nothing I can use to link the actions back to a specific event.

    I am wondering if there is anyway to generate a unique ID for an event to at least link it to its actions. At the moment, I am using only the XML Import feature and an XSLT stylesheet to do the import and am unfamiliar with any other features of Access to do this.



    Thanks!

    -Belt

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,422
    Is the XML file exclusive for a single event? If so, then should not be difficult to assign pk/fk values to the new records.

    If the XML has multiple events, must be tags in the XML that distinguishes start/end of event records.

    I have addressed other threads on this topic. The conclusion has been will require complex VBA code to parse out the XML file and assign pk/fk values to the related records as they are saved to tables.

    Want to provide file for analysis?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jun 2012
    Posts
    2
    Yes, there is one (and only one) event with many actions in a single XML file.

    Unfortunately, I cannot provide the files. But, they basically looks like this (stripped down and simplified):
    <event>
    <name>...</name>
    <category type="...">...</id>
    <action>
    <field1>...</field1>
    <field2>...</field2>
    ...
    </action>
    <action>...</action>
    ...
    </event>


    The XSLT looks something like this:
    <xsl:template match="/">
    <event_table>
    <name><xsl:value-of select="name" /></name>
    <type><xsl:value-of select="category/@type" /></type>
    <category><xsl:value-of select="category" /></category>
    <xsl:for-each select="action">
    <event_table>
    <field1><xsl:value-of select="field1" /></field1>
    <field2>
    <xsl:value-of select="field2" /></field2>
    </event_table>
    </xsl:for-each>
    </event_table>
    </xsl:template>

    The problem is that none of the fields are unique within the event to be able to distinguish events from one another or create a reference between the event and its actions. I've looked for a way of generating a surrogate key between the two, but I'm not sure how to do that using only the XSLT. I'd also be completely fine if the solution required some sort of scripting, but I'm not at all familiar with Access scripting capabilities on how to accomplish the task.

    Thanks.
    Last edited by ObstructedBelt; 06-27-2012 at 06:01 AM. Reason: Format XML

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,422
    I don't know anything about XSLT file but I can see your dilemma.

    Have you tried the import wizard?

    An XML can be opened by a text editor so therefore VBA code can open the xml file and read each line and process it.
    Google: VBA xml import
    Here is one http://www.access-programmers.co.uk/...d.php?t=159743
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Unique ID across all tables
    By neo651 in forum Database Design
    Replies: 24
    Last Post: 02-20-2012, 09:44 PM
  2. Replies: 4
    Last Post: 01-31-2012, 12:42 PM
  3. Replies: 4
    Last Post: 08-01-2011, 04:24 PM
  4. Importing Excel datasheets into multiple tables
    By FishMT in forum Import/Export Data
    Replies: 3
    Last Post: 02-08-2011, 07:44 AM
  5. Unique Record Identifier for MS SQL Server Tables
    By MGSP in forum Import/Export Data
    Replies: 2
    Last Post: 01-29-2006, 03:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums