Importing XML with a element longer than 255 characters

[bscs1963]

I have a program that imported an XML file and creates the separate tables for each of the elements. The code goes something like this

If UCase(Right(fileI.Name, 3)) = "XML" Then
lbltxt.Caption = "Importing File " & ofName
DoEvents
ImportXML fileI, acStructureAndData
DontImport = False

'Confirm all sections have been downloaded
'***********************************************

Set rsTemp1 = CurrentDb.OpenRecordset("OrderHeader")
If rsTemp1!TradingPartnerID = "5S2ALLMARANDAEN" Then
theVendor = "GSI"
For i = 0 To CurrentDb.TableDefs.Count - 1
If CurrentDb.TableDefs(i).Name = "Address" Then Exit For
Next
If i = CurrentDb.TableDefs.Count Then
MsgBox "One or more files is missing the Address section of the XML file. Contact IT for support."
DontImport = True
End If

For i = 0 To CurrentDb.TableDefs.Count - 1
If CurrentDb.TableDefs(i).Name = "OrderHeader" Then Exit For
Next
If i = CurrentDb.TableDefs.Count Then
MsgBox "One or more files is missing the OrderHeader section of the XML file. Contact IT for support."
DontImport = True
End If

For i = 0 To CurrentDb.TableDefs.Count - 1
If CurrentDb.TableDefs(i).Name = "OrderLine" Then Exit For
Next
If i = CurrentDb.TableDefs.Count Then
MsgBox "One or more files is missing the OrderLine section of the XML file. Contact IT for support."
DontImport = True
End If

For i = 0 To CurrentDb.TableDefs.Count - 1
If CurrentDb.TableDefs(i).Name = "Reference" Then Exit For
Next
If i = CurrentDb.TableDefs.Count Then
MsgBox "One or more files is missing the Reference section of the XML file. Contact IT for support."
DontImport = True
End If
End If
Set rsTemp1 = Nothing

This works real well, bit sometimes, one of the elements contains data that is longer then 255 characters. This will cause an error that as I see it is because of the length ( I shorten the data in the XML and it imports correctly).

Is there a way around this issue that will allow it to accept the additional characters or only import the first 255? I actually do not use the data in my programming. I cannot ask the vendor to not send the data.

 

[lameid]

Is it failing because the target column is text and hence 255 characters long? You could change it to a memo...

If it is an XML limitation, if it is all consistent in which column(s) it is failing, you probably could write something to manipulate the text (xml) file to fix the data preprocessing...

Or you could dig around and find samples for XML and write your own import procedure.

There is also a code way to process an XML style / XLST which might be worth looking into... I am assuming you could fix the data element size / data type before processing. On this one I might target an XML forum.