Help with Extracting data from XML into Access

Discussion in 'XML' started by Debbiedo, May 11, 2007.

  1. Debbiedo

    Debbiedo Guest

    My software program outputs an XML Driving Directions file that I need
    to input into an Access table (although if need be I can import a dbf
    or xls) so that I can relate one of the fields (fromStop) and its
    associated driving directions back to a relational database. I have
    asked my software vendor for solutions but thus far they have not come
    up with anything. I am totally unfamiliar with XML so I am struggling
    with how to do this. I have been perusing SAMS "Teach yourself XML in
    24 Hours" and am getting a better idea of what XML is; however, the
    book does not seem to address solving my specific problem.

    Suggestions or solutions anyone?

    Sample of XML file and resulting table velow.

    <NA:DIRECTIONS>
    <ROUTE id="1" name="BUS9A00 - BUS9A34">
    <PATH id="1" fromStop="BUS9A00" toStop="BUS9A01">
    <DIRECTION id="1">
    <POINT name="maneuver" x="2287378.557319" y="14814635.649937"/
    >

    <STRINGS>
    <STRING style="normal" text="East on MAIN"/>
    <STRING style="Cumul_length" text="0 mi"/>
    <STRING style="summary" text="Drive 0.2 mi"/>
    <STRING style="length" text="0.2 mi"/>
    </STRINGS>
    </DIRECTION>
    <DIRECTION id="2">
    <POINT name="maneuver" x="2288590.471560" y="14814681.987473"/
    >

    <STRINGS>
    <STRING style="normal" text="Left on PINE"/>
    <STRING style="Cumul_length" text="0.2 mi"/>
    <STRING style="summary" text="Drive 0.1 mi"/>
    <STRING style="length" text="0.1 mi"/>
    </STRINGS>
    </DIRECTION>
    <DIRECTION id="3">
    <POINT name="maneuver" x="2288625.840520" y="14815306.346898"/
    >

    <STRINGS>
    <STRING style="normal" text="Right on 3RD"/>
    <STRING style="Cumul_length" text="0.3 mi"/>
    <STRING style="summary" text="Drive 0.3 mi"/>
    <STRING style="length" text="0.3 mi"/>
    </STRINGS>
    </DIRECTION>
    </PATH>
    <PATH id="2" fromStop="BUS9A01" toStop="BUS9A02">
    <DIRECTION id="4">
    <POINT name="maneuver" x="2288725.227912" y="14834463.791524"/
    >

    <STRINGS>
    <STRING style="normal" text="Left on 8TH"/>
    <STRING style="Cumul_length" text="0.6 mi"/>
    <STRING style="summary" text="Drive 1.3 mi"/>
    <STRING style="length" text="1.3 mi"/>
    </STRINGS>
    </DIRECTION>
    </PATH>
    <PATH id="3" fromStop="BUS9A02" toStop="BUS9A03">
    <DIRECTION id="5">
    <POINT name="maneuver" x="2288766.227725" y="14834533.791111"/
    >

    <STRINGS>
    <STRING style="normal" text="Left on ELM"/>
    <STRING style="Cumul_length" text="1.9 mi"/>
    <STRING style="summary" text="Drive 0.5 mi"/>
    <STRING style="length" text="0.5 mi"/>
    </STRINGS>
    </DIRECTION>
    <DIRECTION id="6">
    <POINT name="maneuver" x="2288766.227725" y="14834533.791111"/
    >

    <STRINGS>
    <STRING style="normal" text="End at YARD"/>
    <STRING style="Cumul_length" text="2.4 mi"/>
    <STRING style="summary" text="Drive 0.4 mi"/>
    <STRING style="length" text="0.4 mi"/>
    </STRINGS>
    </DIRECTION>
    </PATH>
    </ROUTE>
    </NA:DIRECTIONS>


    Access table needs to look like this

    fromStop toStop normal1 length1
    normal2 length2 normal3 length3
    BUS9A00 BUS9A01 East on MAIN 0.2 mi Left on PINE
    0.1 Right on 3RD 0.3
    BUS9A01 BUS9A02 Left on 8TH 1.3
    BUS9A02 BUS9A03 Left on ELM 0.5 End at YARD 0.4

    Only one route per XML file (although this could change). Routes can
    have as few as 3 stops (PATH) or as many as 50. Each PATH element has
    its own record. PATH attributes fromStop and toStop only appear once
    per record. The number of STRINGS attributes "normal" and "length" are
    variable. There could have as many as 50 driving directions (normal
    and length) per record!

    Cuurently using XMLPad 3.0 and MS Access 2003 for Win XP.

    Any and all help greatly appreciated.
     
    Debbiedo, May 11, 2007
    #1
    1. Advertising

  2. Debbiedo

    Debbiedo Guest

    On May 11, 11:45 am, Debbiedo <> wrote:
    > My software program outputs an XML Driving Directions file that I need
    > to input into an Access table (although if need be I can import a dbf
    > or xls) so that I can relate one of the fields (fromStop) and its
    > associated driving directions back to a relational database. I have
    > asked my software vendor for solutions but thus far they have not come
    > up with anything. I am totally unfamiliar with XML so I am struggling
    > with how to do this. I have been perusing SAMS "Teach yourself XML in
    > 24 Hours" and am getting a better idea of what XML is; however, the
    > book does not seem to address solving my specific problem.
    >
    > Suggestions or solutions anyone?
    >
    > Sample of XML file and resulting table velow.
    >
    > <NA:DIRECTIONS>
    > <ROUTE id="1" name="BUS9A00 - BUS9A34">
    > <PATH id="1" fromStop="BUS9A00" toStop="BUS9A01">
    > <DIRECTION id="1">
    > <POINT name="maneuver" x="2287378.557319" y="14814635.649937"/
    >
    > <STRINGS>
    > <STRING style="normal" text="East on MAIN"/>
    > <STRING style="Cumul_length" text="0 mi"/>
    > <STRING style="summary" text="Drive 0.2 mi"/>
    > <STRING style="length" text="0.2 mi"/>
    > </STRINGS>
    > </DIRECTION>
    > <DIRECTION id="2">
    > <POINT name="maneuver" x="2288590.471560" y="14814681.987473"/
    >
    > <STRINGS>
    > <STRING style="normal" text="Left on PINE"/>
    > <STRING style="Cumul_length" text="0.2 mi"/>
    > <STRING style="summary" text="Drive 0.1 mi"/>
    > <STRING style="length" text="0.1 mi"/>
    > </STRINGS>
    > </DIRECTION>
    > <DIRECTION id="3">
    > <POINT name="maneuver" x="2288625.840520" y="14815306.346898"/
    >
    > <STRINGS>
    > <STRING style="normal" text="Right on 3RD"/>
    > <STRING style="Cumul_length" text="0.3 mi"/>
    > <STRING style="summary" text="Drive 0.3 mi"/>
    > <STRING style="length" text="0.3 mi"/>
    > </STRINGS>
    > </DIRECTION>
    > </PATH>
    > <PATH id="2" fromStop="BUS9A01" toStop="BUS9A02">
    > <DIRECTION id="4">
    > <POINT name="maneuver" x="2288725.227912" y="14834463.791524"/
    >
    > <STRINGS>
    > <STRING style="normal" text="Left on 8TH"/>
    > <STRING style="Cumul_length" text="0.6 mi"/>
    > <STRING style="summary" text="Drive 1.3 mi"/>
    > <STRING style="length" text="1.3 mi"/>
    > </STRINGS>
    > </DIRECTION>
    > </PATH>
    > <PATH id="3" fromStop="BUS9A02" toStop="BUS9A03">
    > <DIRECTION id="5">
    > <POINT name="maneuver" x="2288766.227725" y="14834533.791111"/
    >
    > <STRINGS>
    > <STRING style="normal" text="Left on ELM"/>
    > <STRING style="Cumul_length" text="1.9 mi"/>
    > <STRING style="summary" text="Drive 0.5 mi"/>
    > <STRING style="length" text="0.5 mi"/>
    > </STRINGS>
    > </DIRECTION>
    > <DIRECTION id="6">
    > <POINT name="maneuver" x="2288766.227725" y="14834533.791111"/
    >
    > <STRINGS>
    > <STRING style="normal" text="End at YARD"/>
    > <STRING style="Cumul_length" text="2.4 mi"/>
    > <STRING style="summary" text="Drive 0.4 mi"/>
    > <STRING style="length" text="0.4 mi"/>
    > </STRINGS>
    > </DIRECTION>
    > </PATH>
    > </ROUTE>
    > </NA:DIRECTIONS>
    >
    > Access table needs to look like this
    >
    > fromStop toStop normal1 length1
    > normal2 length2 normal3 length3
    > BUS9A00 BUS9A01 East on MAIN 0.2 mi Left on PINE
    > 0.1 Right on 3RD 0.3
    > BUS9A01 BUS9A02 Left on 8TH 1.3
    > BUS9A02 BUS9A03 Left on ELM 0.5 End at YARD 0.4
    >
    > Only one route per XML file (although this could change). Routes can
    > have as few as 3 stops (PATH) or as many as 50. Each PATH element has
    > its own record. PATH attributes fromStop and toStop only appear once
    > per record. The number of STRINGS attributes "normal" and "length" are
    > variable. There could have as many as 50 driving directions (normal
    > and length) per record!
    >
    > Cuurently using XMLPad 3.0 and MS Access 2003 for Win XP.
    >
    > Any and all help greatly appreciated.


    ****UPDATE****

    Access table needs to look like this

    fromStop toStop normal1 length1 nor2 length2
    nor3 len3
    BUS9A00 9A01 East on MAIN 0.2 mi PINE 0.1 mi 3RD 0.3
    BUS9A01 9A02 Left on 8TH 1.3 mi
    BUS9A02 9A03 Left on ELM 0.5 mi YARD 0.4 mi


    I had to reformat to fit the page. Please note that the data in the
    table
    will be exactly as written in XML file. I just abbreviated so it would
    post
    correctly.

    Deb
     
    Debbiedo, May 11, 2007
    #2
    1. Advertising

  3. Debbiedo wrote:
    >My software program outputs an XML Driving Directions file that I need
    >to input into an Access table (although if need be I can import a dbf
    >or xls)


    I don't use Access, but I suspect that it can also import things like
    CSV format (comma-separated value), which are simpler and thus would be
    easier to write tooling for. Conversion to CSV, for example, could be
    done with an XSLT stylesheet, or using relatively simple SAX or DOM
    programming.

    If you were using IBM's DB2 database rather than Access, I'd suggest you
    look at the XML tools that ship with DB2, which make importing XML into
    database tables straightforward. (Of course DB2 version 9 also
    introduced the PureXML feature, which lets it operate directly on XML,
    which might make conversion unnecessary.)

    >Suggestions or solutions anyone?


    The XML end of this is straightforward, as I mentioned above. The
    complication is the database end of it.

    --
    Joe Kesselman / Beware the fury of a patient man. -- John Dryden
     
    Joseph Kesselman, May 11, 2007
    #3
  4. A websearch for "import xml into access" finds several pages discussing
    approaches to this. Do you have specific questions those don't answer?
     
    Joseph Kesselman, May 11, 2007
    #4
  5. Debbiedo

    roy axenov Guest

    Debbiedo wrote:
    > My software program outputs an XML Driving Directions file
    > that I need to input into an Access table (although if
    > need be I can import a dbf or xls) so that I can relate
    > one of the fields (fromStop) and its associated driving
    > directions back to a relational database. I have asked my
    > software vendor for solutions but thus far they have not
    > come up with anything. I am totally unfamiliar with XML so
    > I am struggling with how to do this.


    This is most likely a coding problem, at least I doubt
    there's a canned solution that would help you with your
    task.

    > Suggestions or solutions anyone?


    You have a number of options. The most straightforward one
    would be to whip out your Language of Choice, suck in the
    XML document using a DOM or SAX XML parser (available for
    pretty much anything by now), process it, then probably
    stuff the results into MS Access using ODBC API (available
    for pretty much anything by now).

    Assuming Access can import CSV files and the result would
    satisfy you, your problem is easily solved using XSLT.

    > <NA:DIRECTIONS>


    Namespace declaration missing.

    > <ROUTE id="1" name="BUS9A00 - BUS9A34">


    I'm not sure what the problem is, but my tools complain that
    your file is not UTF-8, so you're likely missing proper XML
    declaration as well (or you did something unnatural with
    your tabs; well, posting tabs on the Usenet is a rather bad
    idea anyway). Please post well-formed documents when asking
    for help. I had to come up with the following mildly
    disgusting piece of line noise:

    :%s/^.*</</

    ....to convince xmllint to accept your document.

    > Only one route per XML file (although this could change).
    > Routes can have as few as 3 stops (PATH) or as many as 50.
    > Each PATH element has its own record. PATH attributes
    > fromStop and toStop only appear once per record. The
    > number of STRINGS attributes "normal" and "length" are
    > variable. There could have as many as 50 driving
    > directions (normal and length) per record!


    That's fairly vague, and I'm not at all sure I understand
    you. Instead of trying to describe the parameters of your
    problem in writing you should've posted an example that
    *demonstrates* them. (No, I don't mean an example with
    fifty PATH elements--that's largely irrelevant from 'zero,
    one, many' point of view.)

    An XSLT solution just for the heck of it:

    > cat dir.xsl

    <xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">
    <xsl:eek:utput method="text"/>
    <xsl:template name="field-separator">
    <xsl:text>,</xsl:text>
    </xsl:template>
    <xsl:template name="next-row">
    <xsl:text>
    </xsl:text>
    </xsl:template>
    <xsl:template name="csvize-value">
    <xsl:param name="x"/>
    <xsl:text>&quot;</xsl:text>
    <xsl:call-template name="dnc-escape-quotes">
    <xsl:with-param name="x" select="$x"/>
    </xsl:call-template>
    <xsl:text>&quot;</xsl:text>
    </xsl:template>
    <xsl:template name="dnc-escape-quotes">
    <xsl:param name="x"/>
    <xsl:variable name="len" select="string-length($x)"/>
    <xsl:choose>
    <xsl:when test="$len &lt;= 1">
    <xsl:if test="$x='&quot;'">
    <xsl:text>&quot;</xsl:text>
    </xsl:if>
    <xsl:value-of select="$x"/>
    </xsl:when>
    <xsl:eek:therwise>
    <xsl:call-template name="dnc-escape-quotes">
    <xsl:with-param name="x"
    select="substring($x,1,floor($len div 2))"/>
    </xsl:call-template>
    <xsl:call-template name="dnc-escape-quotes">
    <xsl:with-param name="x"
    select="substring($x,floor($len div 2)+1)"/>
    </xsl:call-template>
    </xsl:eek:therwise>
    </xsl:choose>
    </xsl:template>
    <xsl:template name="calc-max-segments">
    <xsl:variable name="first-candidate"
    select="PATH[1]/DIRECTION[last()]"/>
    <xsl:choose>
    <xsl:when test="$first-candidate">
    <xsl:apply-templates select="$first-candidate"
    mode="itr-calc-max-segments"/>
    </xsl:when>
    <xsl:eek:therwise>
    <xsl:text>0</xsl:text>
    </xsl:eek:therwise>
    </xsl:choose>
    </xsl:template>
    <xsl:template match="ROUTE/PATH/DIRECTION"
    mode="itr-calc-max-segments">
    <xsl:param name="m" select="0"/>
    <xsl:variable name="cur-num-segments"
    select="count(.|preceding-sibling::DIRECTION)"/>
    <xsl:variable name="new-m">
    <xsl:choose>
    <xsl:when test="$cur-num-segments &gt; $m">
    <xsl:value-of select="$cur-num-segments"/>
    </xsl:when>
    <xsl:eek:therwise>
    <xsl:value-of select="$m"/>
    </xsl:eek:therwise>
    </xsl:choose>
    </xsl:variable>
    <xsl:variable name="next-candidate"
    select=
    "
    ../following-sibling::pATH[1]/DIRECTION[last()]
    "/>
    <xsl:choose>
    <xsl:when test="$next-candidate">
    <xsl:apply-templates select="$next-candidate"
    mode="itr-calc-max-segments">
    <xsl:with-param name="m" select="$new-m"/>
    </xsl:apply-templates>
    </xsl:when>
    <xsl:eek:therwise>
    <xsl:value-of select="$new-m"/>
    </xsl:eek:therwise>
    </xsl:choose>
    </xsl:template>
    <xsl:template match="*" mode="fields-header">
    <xsl:call-template name="field-separator"/>
    <xsl:call-template name="csvize-value">
    <xsl:with-param name="x"
    select="concat('normal',position())"/>
    </xsl:call-template>
    <xsl:call-template name="field-separator"/>
    <xsl:call-template name="csvize-value">
    <xsl:with-param name="x"
    select="concat('length',position())"/>
    </xsl:call-template>
    </xsl:template>
    <xsl:template match="ROUTE">
    <xsl:variable name="max-segments">
    <xsl:call-template name="calc-max-segments"/>
    </xsl:variable>
    <xsl:call-template name="csvize-value">
    <xsl:with-param name="x" select="'fromStop'"/>
    </xsl:call-template>
    <xsl:call-template name="field-separator"/>
    <xsl:call-template name="csvize-value">
    <xsl:with-param name="x" select="'toStop'"/>
    </xsl:call-template>
    <xsl:apply-templates
    select="(//*)[position() &lt;= $max-segments]"
    mode="fields-header"/>
    <xsl:call-template name="next-row"/>
    <xsl:apply-templates select="PATH">
    <xsl:with-param name="max-segments"
    select="$max-segments"/>
    </xsl:apply-templates>
    </xsl:template>
    <xsl:template match="PATH">
    <xsl:param name="$max-segments"/>
    <xsl:call-template name="csvize-value">
    <xsl:with-param name="x" select="@fromStop"/>
    </xsl:call-template>
    <xsl:call-template name="field-separator"/>
    <xsl:call-template name="csvize-value">
    <xsl:with-param name="x" select="@toStop"/>
    </xsl:call-template>
    <xsl:apply-templates
    select="(//*)[position() &lt;= $max-segments]"
    mode="fields-data">
    <xsl:with-param name="dirs" select="DIRECTION"/>
    </xsl:apply-templates>
    <xsl:call-template name="next-row"/>
    </xsl:template>
    <xsl:template match="*" mode="fields-data">
    <xsl:param name="dirs"/>
    <xsl:variable name="pos" select="position()"/>
    <xsl:variable name="cur-field"
    select="$dirs[position()=$pos]"/>
    <xsl:choose>
    <xsl:when test="$cur-field">
    <xsl:apply-templates select="$cur-field"/>
    </xsl:when>
    <xsl:eek:therwise>
    <xsl:apply-templates select="$dirs[1]"
    mode="empty"/>
    </xsl:eek:therwise>
    </xsl:choose>
    </xsl:template>
    <xsl:template match="DIRECTION">
    <xsl:call-template name="field-separator"/>
    <xsl:call-template name="csvize-value">
    <xsl:with-param name="x"
    select="STRINGS/STRING[@style='normal']/@text"/>
    </xsl:call-template>
    <xsl:call-template name="field-separator"/>
    <xsl:call-template name="csvize-value">
    <xsl:with-param name="x"
    select="STRINGS/STRING[@style='length']/@text"/>
    </xsl:call-template>
    </xsl:template>
    <xsl:template match="DIRECTION" mode="empty">
    <xsl:call-template name="field-separator"/>
    <xsl:call-template name="csvize-value">
    <xsl:with-param name="x" select="''"/>
    </xsl:call-template>
    <xsl:call-template name="field-separator"/>
    <xsl:call-template name="csvize-value">
    <xsl:with-param name="x" select="''"/>
    </xsl:call-template>
    </xsl:template>
    </xsl:stylesheet>

    Naturally, it's kludgy and probably doesn't DWYM, but hey,
    you get what you pay for, and...

    > xsltproc dir.xsl dir.xml


    "fromStop","toStop","normal1","length1","normal2","length2","normal3","length3"
    "BUS9A00","BUS9A01","East on MAIN","0.2 mi","Left on
    PINE","0.1 mi","Right on 3RD","0.3 mi"
    "BUS9A01","BUS9A02","Left on 8TH","1.3 mi","","","",""
    "BUS9A02","BUS9A03","Left on ELM","0.5 mi","End at
    YARD","0.4 mi","",""

    ....it actually seems to work on your sample document.

    Have fun.

    --
    roy axenov
     
    roy axenov, May 12, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. SK
    Replies:
    5
    Views:
    27,019
  2. Harry Zoroc
    Replies:
    1
    Views:
    948
    Gregory Vaughan
    Jul 12, 2004
  3. Toto
    Replies:
    5
    Views:
    652
  4. Rodney
    Replies:
    4
    Views:
    446
    Rodney
    Dec 30, 2005
  5. Savvoulidis Iordanis

    XML data access or DB data access ?

    Savvoulidis Iordanis, Jan 7, 2009, in forum: ASP .Net
    Replies:
    2
    Views:
    416
    Savvoulidis Iordanis
    Jan 7, 2009
Loading...

Share This Page