2

I have a XML file like this:

<Example>
  <A>
    <a1>1</a1>
    <a2>2</a2>
  </A>
  <B>
    <b1>1</b1>
    <b2>2</b2>
    <b3>3</b3>
  </B>
  <B>
  .
  .
  .
  </B>
</Example>

When I use Application.ImportXML Me.XMLpath, acStructureAndData, it will import both table A and B. So how could I just import table B without A? (A could be 10+ different tables). Thanks for your kindly help!

Ibrows
  • 31
  • 1
  • 3
  • 2
    Simplest solution to implement might be to import everything into a scratch table, then append what you want from the scratch table into the final destination table. If you're willing to invest more effort, you can open the XML doc as a `MSXML DOMDocument` and use its methods to extract only the values you want, then store them in your table. You can find some tips for that approach at this SO answer: http://stackoverflow.com/a/3398999/77335 – HansUp Dec 04 '12 at 21:48
  • Thanks for your help! Give me a big hint for this problem! – Ibrows Dec 05 '12 at 20:24

1 Answers1

1

Thanks for the hint, the following is what I done...

Dim db As Database
Set db = CurrentDb()
Dim xDoc As MSXML.DOMDocument
Set xDoc = New MSXML.DOMDocument
Dim xNote As IXMLDOMNode
Dim x As IXMLDOMNodeList
Dim XMLpath, sSQL As String

XMLpath = Me.Text1
xDoc.validateOnParse = False
sSQL = "INSERT INTO Temp VALUES ('"

If xDoc.Load(XMLpath) Then
    Set x = xDoc.getElementsByTagName("_TargetTag")
Else
    MsgBox "XML file ERROR"
    Exit Sub
End If

For j = 0 To x.length - 1
    For i = 0 To lengthOfChildNode
        sSQL = sSQL & x(j).childNodes(i).nodeTypedValue & "','"
    Next i
    sSQL = Left(sSQL, Len(sSQL) - 2) & ");"
    db.Execute sSQL
    sSQL = "INSERT INTO Temp VALUES ('"
Next j

So far I got what I need, but any idea that can improve this code I would be very appreciate. Thanks!

Ibrows
  • 31
  • 1
  • 3