Importing XML - "DTD prohibited"

[paul_mcc]

I'm using the below code in an Access form to import XML files.

I get an error 31593, "DTD is prohibited".

How can I fix this?

Thanks in advance,
Paul



Private Sub Command0_Click()

Dim StrFileName As String
Dim fd As FileDialog
Dim vrtSelectedItem As Variant
Dim strprop As String
Dim vbool As Boolean


Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = "C:\Users\Toshiba\Documents\STEM\data\*.xml"
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
Application.ImportXML _
DataSource:=vrtSelectedItem, _
ImportOptions:=0

Next vrtSelectedItem
Else
End If
End With
Set fd = Nothing
End Sub

 

[Isskint]

It is possible this is something to so with XMLReader settings, possibly in the source document rather than in your DB. have a look at this article http://www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.xml/2009-01/msg00071.html

 

[gemma-the-husky]

is the error on the first file, or a subsequent one

i would try it explicitly with that file.

maybe the file is not a valid xml file.

 

[paul_mcc]

Thanks folks for your ideas.

It does appear to be the file's settings, in the "DTD" as I understand they're called, and it is common to all the files.

 

[jdraw]

You might try

ImportOptions:=acStructureAndData
and see if that makes a difference.

 

[paul_mcc]

Thanks jdraw,

That was the first option I had, unfortunately it doesn't seem to work. Cheers for the thought though!

:banghead:

 

[jdraw]

Can you post a sample xml file, so we can experiment a bit?

 

[paul_mcc]

thanks for that - I've attached the link as I can't see how to attach a file - just click on the "Download XML file" to get the file.

http://www.imowa.com.au/n117,45.html?link=%2f20121107%2fallmp_EM_STEMSummaryInfo.xml&h=f074bfee94abc5e0de6f288d2e87346f

much appreciated!

 

[jdraw]

I got your file. I have 2003. As a start, I tried importing it through the wizard. It imported correctly. see attached jpg.

It Imports as 2 tables a Header and a Data.

 

[paul_mcc]

Yes, from what I'm reading now it seems to be only a problem post-2007. Strange - I wouldn't have thought that would be the case. I took the offending "DTD" sections out of one of the files just now and that seems to do the trick. But what would be preferable, without having to change each of 700 files, is to know what is causing it and make a small settings change, or something similarly easy.

 

[jdraw]

I set up a proc to do the import programmatically

'---------------------------------------------------------------------------------------
' Procedure : AWFXMLIMport
' Author    : Jack
' Date      : 06-11-2012
' Purpose   : Importing an XML file.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub AWFXMLIMport()
Dim StrFileName As String
   Dim fd As FileDialog
   Dim vrtSelectedItem As Variant
   On Error GoTo AWFXMLIMport_Error

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .InitialFileName = "c:\saample\*.xml"
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
          [B]  Debug.Print "Xml File is " & vrtSelectedItem[/B]
                Application.ImportXML _
                    DataSource:=vrtSelectedItem, _
                    ImportOptions:=acStructureAndData
               
            Next vrtSelectedItem
        Else
        End If
    End With
    Set fd = Nothing

   On Error GoTo 0
   Exit Sub

AWFXMLIMport_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure AWFXMLIMport of Module xmlhttp_Etc"
    End Sub

It worked for me with no errors. I added a line Debug.Print to identify the xml file and it is the correct file.

There is no message that a new Table (actually 2 tables Headrow2, Datarow2) as per attached jpg.


Do you have a reference set to Microsoft XML,v6.0 ???

 

[jdraw]

I haven't seen the discussion re post 2007, but you could import as text, remove the offending lines and import the rest as xml. --- If that will resolve the issue, and it can all be automated.

I have samples of getting xml data from online source,(if that is an option- perhaps another data feed) you can see sample at post #6
http://www.accessforums.net/queries/exchange-rate-table-query-28974.html

 

[paul_mcc]

jdraw, thank you very, very much. it's midnight here in West Oz right now so I'll get onto it first thing in the morning and let you know how it goes. Again, thank you - fantastic effort!

 

[jdraw]

Great stuff -- one of my old work buddies lives in Perth. Good night.

 

[paul_mcc]

jdraw, that worked brilliantly!!! thank you!

i really appreciate all the effort - you've saved us countless hours now.