[AccessD] [ACCESS-L] Importing an XML file

Francisco Tapia fhtapia at gmail.com
Wed Mar 5 15:19:17 CST 2008


Gary,
  That was the answer.  I used your XSL and imported into Access w/o any
issues.  I want to thank you for helping me!  I am using Access2003, and was
able to import directly from the XML w/ The XSL as you provided. :)

--
Francisco

On Wed, Mar 5, 2008 at 12:19 PM, Gary Walter <garylw at wamego.net> wrote:

> ----- Original Message -----
> From: "Francisco Tapia" <fhtapia at GMAIL.COM>
> To: <ACCESS-L at PEACH.EASE.LSOFT.COM>
> Sent: Tuesday, March 04, 2008 6:31 PM
> Subject: Importing an XML file
>
>
> >I have an XML file that looks like this:
> > <Answers>
> >  <AnswerSet>
> >    <Answer questionId="DEALER_NAME">TEST</Answer>
> >    <Answer questionId="PHONE">5551231212</Answer>
> >    <Answer questionId="CALLNO">300122598</Answer>
> >    <Answer questionId="SERVICETECH_NAME">SvcTech1</Answer>
> >    <Answer questionId="Question1">Yes</Answer>
> >    <Answer questionId="question2">Yes</Answer>
> >    <Answer questionId="question3">Yes</Answer>
> >    <Answer questionId="Question4">Test</Answer>
> >  </AnswerSet>
> >  <AnswerSet>
> >    <Answer questionId="DEALER_NAME">Test21</Answer>
> >    <Answer questionId="PHONE">5551231212</Answer>
> >    <Answer questionId="CALLNO">300120902</Answer>
> >    <Answer questionId="SERVICETECH_NAME">SvcTech2</Answer>
> >    <Answer questionId="Question1">Yes</Answer>
> >    <Answer questionId="question2">Yes</Answer>
> >    <Answer questionId="question3">No</Answer>
> >    <Answer questionId="Question4">Test</Answer>
> >  </AnswerSet>
> >  <AnswerSet>
> >    <Answer questionId="DEALER_NAME">Test3</Answer>
> >    <Answer questionId="PHONE">5551231212</Answer>
> >    <Answer questionId="CALLNO">300121480</Answer>
> >    <Answer questionId="SERVICETECH_NAME">SvcTech3</Answer>
> >    <Answer questionId="Question1">Yes</Answer>
> >    <Answer questionId="question2">Yes</Answer>
> >    <Answer questionId="question3">Yes</Answer>
> >    <Answer questionId="Question4">No Comment</Answer>
> >  </AnswerSet>
> > </Answers>
> >
> >
> > I'd like to import this XML to a table in Access table that reads:
> > Dealer_name
> > Phone
> > CallNo
> > ServiceTech_Name
> > Question1
> > Question2
> > Question3
> > Question4
> >
>
> there are so many tangents, but I will try to demonstrate
> a method I have used before..
>
> 1) create a transform file ("Answer2Access.xsl")
>   {copy following into Notepad and save to name above}
>
> <?xml version='1.0' ?>
> <xsl:stylesheet version="1.0" xmlns:xsl="
> http://www.w3.org/1999/XSL/Transform">
>  <xsl:template match="/">
>  <Answers>
>   <xsl:for-each select="Answers/AnswerSet">
>    <AnswerSet>
>     <DEALER_NAME>
>      <xsl:value-of select="Answer[@questionId='DEALER_NAME']"/>
>     </DEALER_NAME>
>     <PHONE>
>      <xsl:value-of select="Answer[@questionId='PHONE']"/>
>     </PHONE>
>     <CALLNO>
>      <xsl:value-of select="Answer[@questionId='CALLNO']"/>
>     </CALLNO>
>     <Question1>
>      <xsl:value-of select="Answer[@questionId='Question1']"/>
>     </Question1>
>     <question2>
>      <xsl:value-of select="Answer[@questionId='question2']"/>
>     </question2>
>     <question3>
>      <xsl:value-of select="Answer[@questionId='question3']"/>
>     </question3>
>     <Question4>
>      <xsl:value-of select="Answer[@questionId='Question4']"/>
>     </Question4>
>    </AnswerSet>
>   </xsl:for-each>
>  </Answers> </xsl:template>
> </xsl:stylesheet>
>
> 2) I created a subroutine which would use transform
>    to create a new xml file (which will then import correctly).
>
> Public Sub XML_TransformFile(pXMLPath As String, _
>                            pXSLPath As String, _
>                            pNewXMLPath As String)
> On Error GoTo Proc_Error
>    'pXMLPath is full path to xml file, i.e.,
>    '   "C:\Answers.xml"
>    '
>    'pXSLPath is full path to xsl file, i.e.,
>    '   "C:\Answer2Access.xsl"
>    '
>    'pNewXMLPath is full path to tranformed xml file, i.e.,
>    '   "C:\NewAnswers.xml"
>    '
>    'so, in Immediate Window, an example call might be
>    '
>    'XML_TransformFile "C:\Answers.xml", "C:\Answer2Access.xsl",
> "C:\NewAnswers.xml"
>    '
>    Dim oXMLDoc As MSXML2.DOMDocument30
>    Dim oXSLT As MSXML2.DOMDocument30
>
>    Dim strXML As String
>    Dim hFile As Long
>
>
>    Set oXMLDoc = New MSXML2.DOMDocument30
>    Set oXSLT = New MSXML2.DOMDocument30
>
>    oXMLDoc.async = False
>    oXSLT.async = False
>
>
>    'Open the xml document
>    If oXMLDoc.Load(pXMLPath) = False Then
>        MsgBox "Could not load '" & pXMLPath & ".'"
>        GoTo Proc_Exit
>    End If
>
>    'Open the xsl document
>    If oXSLT.Load(pXSLPath) = False Then
>        MsgBox "Could not load '" & pXSLPath & ".'"
>        GoTo Proc_Exit
>    End If
>
>    strXML = oXMLDoc.transformNode(oXSLT)
>    strXML = Replace(strXML, "utf-16", "utf-8", 1, -1, vbTextCompare)
>
>    'save to file
>
>    'Get a new file handle
>    hFile = FreeFile
>
>    'Open the pNewXMLPath file to write the transformed xml
>    Open pNewXMLPath For Binary Access Write As #hFile
>
>    'write collected lines to file
>    Put #hFile, 1, strXML
>
>    ' done so close file.
>    Close #hFile
>
>    MsgBox "Successfully converted xml file."
>
> Proc_Exit:
>    Reset
>    Exit Sub
>
> Proc_Error:
>    MsgBox Err.Description
>    Resume Proc_Exit
> End Sub
>
> 3) now just import your new xml file
>
>   File/Get External Data/Import...
>
> NOTE: I know SO LITTLE about all this!
> This is just about all I know -- this was the
> way I did this for a situation like you have.
> There may be (probably are) better way(s).
>
> This will not work in Win2K/Access2K. Does work
> in WinXP/AccessXP though (and I assume later versions).
>
> good luck,
>
> gary
>
> --------------------------------------------------------------------------
> The ACCESS-L list is hosted on a Windows(R) 2000 Server running L-Soft
> international's LISTSERV(R) software.  For subscription/signoff info
> and archives, see http://peach.ease.lsoft.com/archives/access-l.html .
>                             COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...



More information about the AccessD mailing list