Jump to content

Issues with importing xml into Access 2010

Recommended Posts

Hello,I'm new to this forum, and also new to xml. I appreciate it for any help with a couple of questions I have when trying to import an xml into Access. My xml file looks like:<?xml version='1.0'?><?xml-stylesheet type="text/xsl" href="Sample.xslt"?><myTable>

<entry dbname="myDB1">
<infoGroup id="id1">
</infoGroup><infoGroup id="id2">

</myTable>I am trying to import this xml into Access with a xslt file, so that I can have a table ("myTable") with two fields ("infoGroup_id1" and "infoGroup_id2"), the first field containing the content of all the "name" elements from the "infoGroup" element with id as "id1" and the second containing the content of all the "name" from the "infoGroup" with id as "id2". The other elements - "type", "note" etc would be excluded. I use a xslt file for the import, but end up with two tables (infoGroup_id1 and infoGroup_id2) instead, each has one field as "entry".My xslt file looks like:<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"><myTable>

<xsl:for-each select="myTable/entry">
<xsl:for-each select="infoGroup[@id=id1]/nameGroup/*"><xsl:value-of select="self::name"/></xsl:for-each>
<xsl:for-each select="infoGroup[@id=id2]/nameGroup/*"><xsl:value-of select="self::name"/></xsl:for-each>

</myTable></xsl:template></xsl:stylesheet>What I want is:In Access, a single table:myTablewhich has two fields:infoGroup_id1infoGroup_id2For each of the fields, the values of all "name" elements of each entry would be included:For inforGroup_id1 (the first column), and the first entry (the first row) , I would haveMyName1_id1MyName2_id1MyName3_id1(the number of "name" elements are indefinite)For inforGroup_id2 (the second column), and the first entry (the first row) , I would haveMyName1_id2MyName2_id2(the number of "name" elements are indefinite)My questions are:1. How to import into a single table ("myTable") with two fields ("infoGroup_id1" and "infoGroup_id2")?2. So far I can put the values of all "name" elements into one field, but how to separate them with line breaks?3. The entry has an attribute "myDB1". How can I convert this attribute into a third column in the table?4. As some "name" elements have a text longer than 255, how to specify the type of field as long text so as to avoid truncation?Any help is greatly appreciated.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...