#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2015
    Posts
    2
    Rep Power
    0

    Question Issues with importing xml into Access 2010


    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:
    Code:
    <?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:

    Code:
    <?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:
    myTable

    which has two fields:
    infoGroup_id1
    infoGroup_id2

    For 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 have
    MyName1_id1
    MyName2_id1
    MyName3_id1
    (the number of "name" elements are indefinite)
    For inforGroup_id2 (the second column), and the first entry (the first row) , I would have
    MyName1_id2
    MyName2_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.
  2. #2
  3. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,459
    Rep Power
    9645
    First thing is figuring out what the XML needs to look like for you to import it.

    Given that XML you posted, what does it need to be transformed into for it to work with Access?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2015
    Posts
    2
    Rep Power
    0
    I am trying to import this xml into Access with a xslt file, so that in the Access file, I can have a single table ("myTable" or any name I choose) with two columns ("infoGroup_id1" and "infoGroup_id2"), the first column containing the content of all the "name" elements from the "infoGroup" element with id of "id1" in the xml file and the second containing the content of all the "name" elements from the "infoGroup" with id of "id2" in the xml file. The other elements - "type", "note" etc in the xml file would be excluded. Additionally, I want the values of each "name" element imported to be separated by line breaks, and specify the type of the two columns of the Access table to be long text so that the content wouldn't be truncated.

    To illustrate:

    In Access file, there would be a single table:
    myTable (or whatever name I choose)

    which breaks down to two columns:
    "infoGroup_id1" and "infoGroup_id2"

    Each "entry" element in the xml file would be converted into a row in the Access table.

    With my xml example, the cell of the first column and the first row contains:
    MyName1_id1
    MyName2_id1
    MyName3_id1
    (the three values would be separated by a line break)

    the cell of the second column and the first row contains:
    MyName1_id2
    MyName2_id2
    (the three values would be separated by a line break)

    Thanks.

IMN logo majestic logo threadwatch logo seochat tools logo