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...