Export Data in xml - Many Records

[MaWhi]

Hi all
This is my first real post after i joined this forum 2 years ago. i apologies in advance.

I'm having some trouble creating xml files using this function.

Public Sub XMLHandle(TypeExp As AcExportXMLObjectType, xmlOut As String, Path As String, NomeFile As String, ConvertingXSL As String)
Dim PathAndFile As String

PathAndFile = Path & NomeFile & ".xml"

    ' RAW XML EXPORT
    If Dir(PathAndFile) <> "" Then Kill PathAndFile
    Application.ExportXML TypeExp, xmlOut, PathAndFile

    ' TRANSFORM RAW XML (full XSLT processor)
    Dim xmlDoc As Object, xslDoc As Object, newDoc As Object

    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    Set xslDoc = CreateObject("MSXML2.DOMDocument")
    Set newDoc = CreateObject("MSXML2.DOMDocument")

    ' LOAD XML AND XSL FILES
    
    xmlDoc.Load PathAndFile
    xmlDoc.async = False

    xslDoc.Load CurrentProject.Path & "\xml\" & ConvertingXSL
    xslDoc.async = False

    ' TRANSFORM SOURCE TO FINAL
    xmlDoc.transformNodeToObject xslDoc, newDoc
    newDoc.Save PathAndFile

    Set newDoc = Nothing
    Set xslDoc = Nothing
    Set xmlDoc = Nothing

End Sub

in this loop:

    Do While rs.EOF = False

CurrentDb.QueryDefs("Data_Out").SQL= (...) 
' here i update the sql of the query i export using the recordsets values
'in the where clause

FileNameOut=DFirst("NameDataOut", "Tmp_DataOut", "DataName = """ & CStr(rs("DataName")) & """")

        XMLHandle acExportQuery, "Data_Out", "C:\MyData\", FileNameOut, "Transform3.xsl"

        rs.MoveNext
        
    Loop

acExportQuery= because i use a query
"Data_Out" = The Query Name
"C:\MyData" = The path where i save the xml file
FileNameOut= The name of the xml i want to save
"Transform3.xsl"=the name of the xsl because i need to transform the file

I have a table with 20.000 rows and they refer to 6500 types.
In the loop i create an xml for any of those types

The trouble is that this takes 20minutes to create all the files. Is it possible to find a faster way or this is the best i can get?

Thank you

 

[theDBguy]

Hi. Are you saying you're creating 6500 XML files? In computing, file I/O is typically the slowest process. You could try using SSDs for file storage. It might help a little bit.

 

[MaWhi]

Thanks for your reply. Unfortunately i have to create those 6500 files. Maybe i can do it in some other way but i can't figure it out how.

 

[The_Doc_Man]

My question is from ignorance of the XML stuff you are creating.

When you create an object, there is a certain amount of overhead. When you destroy an object, more overhead. Is it possible with this kind of object for you to create it but then once you are finished, to just close the file but not destroy the object? Then next time you want to create a file, can you re-use the object?

In essence, can you create the objects externally to that handler routine, then pass them in (by reference) to your handler? Because every time you create an object and later delete the object, if it is truly an external image, you are involving the Windows process scheduler, memory manager, and security manager. By splitting that out from the handler routine, you might reduce some steps.

If that isn't possible, please forgive my ignorance of making XML files that way.

 

[theDBguy]

Thanks for your reply. Unfortunately i have to create those 6500 files. Maybe i can do it in some other way but i can't figure it out how.

Hi. To do it another way would depend on what you do with those files after you created them. For example, let's say I have two databases where I take data from the first database and create 6000 XML files from that data. Then, I take those 6000 XML files and import the data into the second database. If I can make database 1 talk to database 2 and vice versa, then I may not have to create those XML files at all. Perhaps, I'll be able to export the data from database 1 directly into database 2. That's just an example but may not apply to your situation.

 

[MaWhi]

Hi. To do it another way would depend on what you do with those files after you created them. For example, let's say I have two databases where I take data from the first database and create 6000 XML files from that data. Then, I take those 6000 XML files and import the data into the second database. If I can make database 1 talk to database 2 and vice versa, then I may not have to create those XML files at all. Perhaps, I'll be able to export the data from database 1 directly into database 2. That's just an example but may not apply to your situation.

Unfortunately i can't talk with the other database.

 

[MaWhi]

My question is from ignorance of the XML stuff you are creating.

When you create an object, there is a certain amount of overhead. When you destroy an object, more overhead. Is it possible with this kind of object for you to create it but then once you are finished, to just close the file but not destroy the object? Then next time you want to create a file, can you re-use the object?

In essence, can you create the objects externally to that handler routine, then pass them in (by reference) to your handler? Because every time you create an object and later delete the object, if it is truly an external image, you are involving the Windows process scheduler, memory manager, and security manager. By splitting that out from the handler routine, you might reduce some steps.

If that isn't possible, please forgive my ignorance of making XML files that way.

In the next weeks i'm going to try to find a way to use less resources optimizing the code.

Thanks

 

[theDBguy]

Unfortunately i can't talk with the other database.

Then, I guess you're stuck with the current process and just try to optimize it. Good luck!

 

[MaWhi]

Hi everyone,
i haven't found a way to optimize this in in these few days (i was very busy and the "query is corrupt" problem Yesterday freaked me out quite a lot) but i've understood that if i organize better my code i will probably achieve a better result. So if i find a solution i will post it here.

Thanks