Help with Extracting data from XML into Access

[Debbiedo]

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.

<NAIRECTIONS>
<ROUTE id="1" name="BUS9A00 - BUS9A34">
<PATH id="1" fromStop="BUS9A00" toStop="BUS9A01">
<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>
<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>
<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">
<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">
<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>
<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>
</NAIRECTIONS>


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]

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.

<NAIRECTIONS>
<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>
</NAIRECTIONS>

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

 

[Joseph Kesselman]

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.

 

[Joseph Kesselman]

A websearch for "import xml into access" finds several pages discussing
approaches to this. Do you have specific questions those don't answer?

 

[roy axenov]

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.

<NAIRECTIONS>

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">
<xslutput 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">
<xslaram 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">
<xslaram 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>
<xsltherwise>
<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>
</xsltherwise>
</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>
<xsltherwise>
<xsl:text>0</xsl:text>
</xsltherwise>
</xsl:choose>
</xsl:template>
<xsl:template match="ROUTE/PATH/DIRECTION"
mode="itr-calc-max-segments">
<xslaram name="m" select="0"/>
<xsl:variable name="cur-num-segments"
select="count(.|preceding-sibling:IRECTION)"/>
<xsl:variable name="new-m">
<xsl:choose>
<xsl:when test="$cur-num-segments &gt; $m">
<xsl:value-of select="$cur-num-segments"/>
</xsl:when>
<xsltherwise>
<xsl:value-of select="$m"/>
</xsltherwise>
</xsl:choose>
</xsl:variable>
<xsl:variable name="next-candidate"
select=
"
../following-sibling:ATH[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>
<xsltherwise>
<xsl:value-of select="$new-m"/>
</xsltherwise>
</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">
<xslaram 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">
<xslaram 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>
<xsltherwise>
<xsl:apply-templates select="$dirs[1]"
mode="empty"/>
</xsltherwise>
</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.