将XML文件导入具有多个表的Access DB [英] Importing XML files into an Access DB with multiple tables

查看:149
本文介绍了将XML文件导入具有多个表的Access DB的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一堆(平面)XML文件,例如:

I have a bunch of (flat) XML files such as:

<?xml version="1.0" encoding="UTF-8"?>
<SomeName>   
  <UID>
    ID123
  </UID>
  <Node1> 
    DataA 
 </Node1>   
 <Node2> 
    DataB 
 </Node2>   
  <Node3> 
    DataC 
 </Node3>   
  <AnotherNode1> 
    DataD 
 </AnotherNode1> 
  <AnotherNode2> 
    DataE 
 </AnotherNode2> 
  <AnotherNode3> 
    DataF 
 </AnotherNode3> 
 <SingleNode> 
    DataG 
 </SingleNode> 
</SomeName>   

现在我的实际XML文件有太多节点,因此它们无法导入到单个表中(由于255列限制),所以我需要将数据拆分成多个表。我已经手工创建了表,所以现在所有访问都必须将Node名称与每个表中的列匹配并复制数据。

Now my actual XML files have too many nodes, so they can't be imported into a single table (due to the 255 column limit), so I need to split the data into multiple tables. I already created the tables by hand so now all access would have to do is match the Node names with the columns in each of the tables and copy the data.

它确实所以只对一个名为'SomeName'的表,但保持所有其他表不变。

It does so only for one table named 'SomeName', but leaves all other tables untouched.

我不知道如何获得将我的XML文件正确导入所有表的访问权限。我还尝试在每个表中创建UID字段并将它们链接起来(因为UID对每个XML数据集都是唯一的),但这也使得访问权限不受影响。

I am unsure of how to get access to import my XML files properly into all the tables. I also already tried creating the UID field in each table and linking them (since the UID is unique to each XML dataset), but that left access unimpressed as well.

I我试图找到关于这个问题的任何信息,但迄今为止一无所获。

I've tried to find any sort of info on this problem, but have so far found nothing.

我非常感谢任何帮助或指示。

I would very grateful for any help or pointers.

推荐答案

由于您需要超过255个字段,因此您必须使用代码执行此操作。您可以将XML加载到 MSXML2.DOMDocument 中,收集节点值的子集,构建 INSERT 语句,以及执行它。

Since you require more than 255 fields, you'll have to do this with code. You could load your XML into a MSXML2.DOMDocument, gather a subset of node values, build an INSERT statement, and execute it.

这是我针对您的样本数据测试的程序。这很难看,但它确实有效。修改 strTagList strFieldList CurrentDb.Execute 行$ c>, strTable cintNumTables 并查看 INSERT 陈述。如果要加载的表超过2个,请添加额外的 Case 块。

Here is a procedure I tested against your sample data. It's pretty ugly, but it works. Un-comment the CurrentDb.Execute line after you modify strTagList, strFieldList, strTable, and cintNumTables and review the INSERT statements. Add additional Case blocks if you have more than 2 tables to load.

Public Sub Grinner(ByRef pURL As String)
    Const cintNumTables As Integer = 2
    Dim intInnerLoop As Integer
    Dim intOuterLoop As Integer
    Dim objDoc As Object
    Dim objNode As Object
    Dim strFieldList As String
    Dim strMsg As String
    Dim strSql As String
    Dim strTable As String
    Dim strTag As String
    Dim strTagList As String
    Dim strUID As String
    Dim strValueList As String
    Dim varTags As Variant

On Error GoTo ErrorHandler

    Set objDoc = GetXMLDoc(pURL)
    Set objNode = objDoc.getElementsByTagName("UID").Item(0)
    strUID = objNode.Text

    For intOuterLoop = 1 To cintNumTables
        Select Case intOuterLoop
        Case 1
            strTable = "Table1"
            strTagList = "Node1,Node2,Node3,AnotherNode1"
            strFieldList = "UID, N1, N2, N3, A1"
        Case 2
            strTable = "Table2"
            strTagList = "AnotherNode2,AnotherNode3,SingleNode"
            strFieldList = "UID, A2, A3, SN"
        Case Else
            'oops!
            strTable = vbNullString
        End Select
        If Len(strTable) > 0 Then
            varTags = Split(strTagList, ",")
            strValueList = "'" & strUID & "'"
            For intInnerLoop = 0 To UBound(varTags)
                strTag = varTags(intInnerLoop)
                Set objNode = objDoc.getElementsByTagName(strTag).Item(0)
                strValueList = strValueList & ", '" & _
                    Replace(objNode.Text, "'", "''") & "'"
            Next intInnerLoop
            strSql = "INSERT INTO " & strTable & " (" & _
                strFieldList & ")" & vbNewLine & _
                "VALUES (" & strValueList & ");"
            Debug.Print strSql
            'CurrentDb.Execute strSql, dbFailOnError
        End If
    Next intOuterLoop

ExitHere:
    Set objNode = Nothing
    Set objDoc = Nothing
    On Error GoTo 0
    Exit Sub

ErrorHandler:
    strMsg = "Error " & Err.Number & " (" & Err.Description _
        & ") in procedure Grinner"
    MsgBox strMsg
    GoTo ExitHere
End Sub

Public Function GetXMLDoc(pURL) As Object
    ' early binding requires reference, Microsoft XML
    'Dim objDoc As MSXML2.DOMDocument30
    'Dim objParseErr As MSXML2.IXMLDOMParseError
    'Set objDoc = New MSXML2.DOMDocument30
    ' late binding; reference not required
    Dim objDoc As Object
    Dim objParseErr As Object
    Dim strMsg As String

On Error GoTo ErrorHandler

    Set objDoc = CreateObject("Msxml2.DOMDocument.3.0")
    objDoc.async = False
    objDoc.validateOnParse = True
    objDoc.Load pURL
    If (objDoc.parseError.errorCode <> 0) Then
       Set objParseErr = objDoc.parseError
       MsgBox ("You have error " & objParseErr.reason)
       Set objDoc = Nothing
    End If

ExitHere:
    Set objParseErr = Nothing
    Set GetXMLDoc = objDoc
    On Error GoTo 0
    Exit Function

ErrorHandler:
    strMsg = "Error " & Err.Number & " (" & Err.Description _
        & ") in procedure GetXMLDoc"
    MsgBox strMsg
    Set objDoc = Nothing
    GoTo ExitHere
End Function

以下是我发现对VBA / XML / DOM有用的4个链接:

Here are 4 links I found helpful for VBA/XML/DOM:

  • Google: vba xml dom
  • msdn: A Beginner's Guide to the XML DOM
  • msdn: Use the XML Object Model
  • Stack Overflow: How to parse XML in VBA

这篇关于将XML文件导入具有多个表的Access DB的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆