Show progress information while importimg from XML file

Experts,

I have an application that import data from xml file into SQL Server.
Process involve complex transformations so I cannot use bulk insert or XMLSQL
Instead I open file as MSXML2.DOMDocument40 and loop through all elements.

Now I need to display progress bar while importing.

As Max value I'll take Len(xmlDoc.xml)
Then I tried to compute already processed part as

mlBytesProcessed = mlBytesProcessed + Len(objNodeList.Item(i).xml)

But this approach works only on highest level of elements.
I want to get this info from each child element.

Any ideas?


Michael
LVL 13
Michael_DAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsulkarCommented:
find out how big the child record is compared to the master and use a percentage to move the bar while processing the child record. I am assuming you can read to the next master record and count how many bytes the child record is compared to the current master record.

Hope that helps a little

Diven
0
Michael_DAuthor Commented:
I think I have to explain more:

<root> ( or whole doc is 100k)
  <element1> (20K incuding all child elements)
      <element11>(10K)
      <element12>(10K)
  <element2> (25K incuding all child elements)
      <element21>(15K including childs )
          <element211>(5K)
          <element212>(10K)
      <element22>(10K)
  <element3> (35K incuding all child elements)
....
  <element4> (20K incuding all child elements)
....


So when I use Len(some_current_element.xml) I get like this


<root> ( or whole doc is 100k)                            ---->0%
  <element1> (20K incuding all child elements)     ---->20%
      <element11>(10K)                                       ---->30% ' this part is already counted in parent element
      <element12>(10K)                                       ---->40%
  <element2> (25K incuding all child elements)     ---->65%
      <element21>(15K including childs )               ---->80%
          <element211>(5K)                                   ---->85%
          <element212>(10K)                                 ---->95%
      <element22>(10K)                                       ---->105% :)
  <element3> (35K incuding all child elements)
....
  <element4> (20K incuding all child elements)
....


So I need to know size of xml portion of element Excluding children


Thanks
0
dsulkarCommented:
I am assuming that you are just looping through this xml file
couldent you just use the Len statement in the parent loop outside the child loop and then upgrade the progress percentage in the child loop as a percentage of just the parent. That way you still have the parent length as a percentage of the root file and the individual child lengths as a percentage of the parent loop.



0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Shiju SasidharanAssoc Project ManagerCommented:
hi
  r u processing nodes one by one or as a collection of nodes?
if ur r processing node by node u can use this technique to trace the progress in processing

'-------------------------------------------------
' Place a Progressbar and change its name to pgrStatus
' and set Max Value to 100
' and use the following code
'-----------------------------------------------------------
Private Sub ProcessNodes()

Dim lTotal as Long
Dim objXmlDoc   As New MSXML2.DOMDocument40
Dim objXmlNdList As IXMLDOMNodeList
Dim dblPgrBarIncr  as Double
    '   Place here the code to open ur Xml document
    '         <-------------->
   
        Set objXmlNdList = objXmlDoc.documentElement.selectNodes("//root")
        lTotal = objXmlNdList.Length
        Set objXmlNdList = Nothing
        dblPgrBarIncr = 100 / lTotal    
        pgrStatus.Value=pgrStatus.Min
       
    '   Place here the code to Process a Single Xml Node
    '         <-------------->  

          If pgrStatus.Value + dblPgrBarIncr <= pgrStatus.Max Then
                    pgrStatus.Value = pgrStatus.Value + dblPgrBarIncr
          Else
                   pgrStatus.Value =  pgrStatus.Max
          End If

End Sub

;-)
Shiju








0
Shiju SasidharanAssoc Project ManagerCommented:
hi
place the followng code  in the loop for processing nodes
    'Loop Starts here...        
    '   Place here the code to Process a Single Xml Node
    '         <-------------->  

          If pgrStatus.Value + dblPgrBarIncr <= pgrStatus.Max Then
                    pgrStatus.Value = pgrStatus.Value + dblPgrBarIncr
          Else
                   pgrStatus.Value =  pgrStatus.Max
          End If

    'Endof ur proecssing Loop

i hope u got the idea

;-)
Shiju
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael_DAuthor Commented:
shijusn,

I got the idea, but again it works only for one top level
using your code here is how my app is working:

Private Sub ProcessNodes()

Dim lTotal As Long
Dim objXmlDoc   As New MSXML2.DOMDocument40
Dim objXmlNdList As IXMLDOMNodeList
Dim dblPgrBarIncr  As Double
    '   Place here the code to open ur Xml document
    '         <-------------->
        objXmlDoc.Load "C:\test.xml"
        Set objXmlNdList = objXmlDoc.documentElement.selectNodes("//root/*")
        lTotal = objXmlNdList.length
        'Set objXmlNdList = Nothing
        dblPgrBarIncr = 100 / lTotal
        pgrStatus.Value = pgrStatus.Min
       
    '   Place here the code to Process a Single Xml Node
    '         <-------------->
    Dim i As Long
    For i = 0 To objXmlNdList.length - 1
         
          ProcessChild objXmlNdList(i).baseName, objXmlNdList(i).xml                       '<------- Call the recursive function          
   
          If pgrStatus.Value + dblPgrBarIncr <= pgrStatus.Max Then
                    pgrStatus.Value = pgrStatus.Value + dblPgrBarIncr
          Else
                   pgrStatus.Value = pgrStatus.Max
          End If
    Next i
End Sub


---------------------
ProcessChild  is recursive function very similar to ProcessNodes .

Any idea?

0
Michael_DAuthor Commented:
Shiju,

Actually you was right. My fault.

Now its work with recursive function.

Thank you
0
Michael_DAuthor Commented:
Here the whole solution:

'-------------------------------------------------
' Place a Progressbar and change its name to pgrStatus
' Set MAXVALUE constant to whatever you want - progress will run based on percentage of this value
' Place a CommandButton
' Press it for start process
'Dont forget to load the right file (I used "C:\test.xml")
'-----------------------------------------------------------

Option Explicit
Dim lParentValue As Double
Const MAXVALUE = 100

Private Sub ProcessNodes()

'Dim lTotal As Long
Dim objXmlDoc   As New MSXML2.DOMDocument40
Dim objXmlNdList As IXMLDOMNodeList

objXmlDoc.Load "C:\test.xml"
pgrStatus.Max = MAXVALUE
lParentValue = MAXVALUE
ProcessChild objXmlDoc.documentElement.baseName, objXmlDoc.xml

End Sub

Private Sub Command1_Click()
ProcessNodes
End Sub


Sub ProcessChild(ParentPath As String, xmlSource As String)

Dim lTotal As Long
Dim objXmlDoc   As New MSXML2.DOMDocument40
Dim objXmlNdList As IXMLDOMNodeList
Dim dblPgrBarIncr  As Double
Dim i As Long
Dim PrevValue As Double
       
objXmlDoc.loadXML xmlSource
Set objXmlNdList = objXmlDoc.documentElement.selectNodes("//" & ParentPath & "/*")
lTotal = objXmlNdList.length

If objXmlNdList.length = 0 Then
    pgrStatus.Value = pgrStatus.Value + dblPgrBarIncr
    Exit Sub
End If
dblPgrBarIncr = lParentValue / lTotal

For i = 0 To objXmlNdList.length - 1
        PrevValue = pgrStatus.Value
        lParentValue = dblPgrBarIncr
       
        Debug.Print "Child -->" & objXmlNdList(i).baseName & " - " & lParentValue

       
        ProcessChild objXmlNdList(i).baseName, objXmlNdList(i).xml
       
        If PrevValue + dblPgrBarIncr <= pgrStatus.Max Then
            pgrStatus.Value = PrevValue + dblPgrBarIncr
        Else
            pgrStatus.Value = pgrStatus.Max
        End If
        Debug.Print "Progress -->" & pgrStatus.Value

Next i

End Sub

0
Shiju SasidharanAssoc Project ManagerCommented:
Hi
Michael_D

 i am glad that my comment helped u
 if ur XML is huge u can put  a DoEvents statement just before Next i
'---------------------
     DoEvents
  Next i
'---------------------

 Thank u for the points
 regards

;-)
Shiju
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.