Jump to content

Issues with importing xml into Access 2010


gn4619

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">
<nameGroup>
<name>MyName1_id1</name><name>MyName2_id1</name><name>MyName3_id1</name>...<status></status><type></type><source></source><note></note>
</nameGroup>
</infoGroup><infoGroup id="id2">
<nameGroup>
<name>MyName1_id2</name><name>MyName2_id2</name>...<status></status><type></type><source></source><note></note>
</nameGroup>
</InfoGroup>
</entry>...

</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">
<infoGroup_id1>
<xsl:copy>
<xsl:for-each select="infoGroup[@id=id1]/nameGroup/*"><xsl:value-of select="self::name"/></xsl:for-each>
</xsl:copy>
</infoGroup_id1>
<infoGroup_id2>
<xsl:copy>
<xsl:for-each select="infoGroup[@id=id2]/nameGroup/*"><xsl:value-of select="self::name"/></xsl:for-each>
</xsl:copy>
</infoGroup_id2>
</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 comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...