• Home
 · 
• Miscellaneous
 · 
• read xml file and give it result

read xml file and give it result

read xml file and give it result

Rate read xml file and give it result

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

' load xml file into Object

On Error GoTo Errh
    
    If Trim(strXMLFilePath) <> "" Then
        'Set ObjXmlDOM = CreateObject("Microsoft.XMLDOM")
        'load xmlDocument
        Set ObjXmlDOM = New MSXML2.DOMDocument
        
        ObjXmlDOM.Load (strXMLFilePath)

        'MsgBox ObjXmlDOM.xml
        Set rootNode = ObjXmlDOM.documentElement
        
        
        If Not IsObject(rootNode) Then
            MsgBox "No file Loaded", vbCritical, App.Title
            OpenXMLFile = False
            Exit Function
        End If
        OpenXMLFile = True
    End If
    
Exit Function
Errh:
    MsgBox "XML file Opening failed !" + Chr(13) + Err.Description, vbCritical, App.Title
    OpenXMLFile = False
End Function
'========================================================



Public Function booImportPackingDeatilsNew(strXMLFilePath As String) As Boolean
On Error GoTo Errh
    Dim objRoot
    Dim strSql, strSqlDelate As String
    Dim strPLNo As String
    Dim strStyleNo As String
    Dim strNewPlNo As String
    ' open the xml file
    If OpenXMLFile(strXMLFilePath) = False Then
        MsgBox "Data uploading failed!", vbCritical, App.Title
        Exit Function
    End If
        
     Set objRoot = ObjXmlDOM.documentElement
    
   'Set objRoot = ObjXmlDOM.selectSingleNode("List/Header/intListNo")
    
     'ObjXmlDOM
    ' save for later use
   
    strPLNo = objRoot.selectSingleNode("List/Header/intListNo").Text
    strStyleNo = objRoot.selectSingleNode("List/Header/strStyleNo").Text
     
    gConxn.BeginTrans
                
            
'        strSqlDelate = "DELETE FROM Factory_Packing_Head WHERE strPackingListNo = '" & _
'                        objRoot.selectSingleNode("List/Header/intListNo").Text & "'"
        
        'gConxn.Execute strSqlDelate
        
        strNewPlNo = strGetNextPackingListNo
        
        
        strSql = ""
        strSql = "INSERT INTO Factory_Packing_Head(strPackingListNo, intMode, strCompany, " & _
                 "dblQuantity, intNoOfCartons, dblNetMass, dblGrossMass, dblNetNetMass, " & _
                 "strMeasurement, strCTMMeasurement, strCPONo, strBuyerPONo, dtmDate, strUser, " & _
                 "intStatus, intContainerType, strContainerName, strCompanyID, strPL_Type, strStyleNo, " & _
                 "strCATNo, strItemDescription, strFabricContent, strInvoiceNo, strLCNo, strVessel, " & _
                 "strLable, strCTNo, strBStyleNo, strCStyleNo, strReferenceNo, boolOrderCompletelyShip, " & _
                 "strLotNo, strSEALNo, strMainMarks, strSideMarks, strShipper, strConsignee, " & _
                 "strBuyerProgName, strTags, strHangerNo, strKellWoodItem, intRopsPerBar, intKnotsPerRops, " & _
                 "intPcsPerKnot, strTariffNo, dtShipDate, strManeLable, strPaperCap, strColorSize, strPortOfLoading, " & _
                 "strFinlDestination, strUnits, strCBM, dtPickupDate, intpreticketed, intperpack, intFlatpack, intGOH, strCustomerId, " & _
                 "RNNo, strProductionTeam, strBusinessUnit, strBusinessUnitPONo, " & _
                 "strShipToAddress , intStandedCTNQty, dblCTNL, dblCTNW, dblCTNH, strCTNMType, strPackingAccDescription, " & _
                 "strRNNo, strOrderNo, strLineNo, strCATLOG,strRequiredStyleDesc,strLineCode,strItemNo,strOriginalPLNO,intGarmentType) "
                     
        strSql = strSql & _
                "VALUES('" & _
                strNewPlNo & "', '" & _
                objRoot.selectSingleNode("List/Header/intMode").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strCompany").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/dblQuantity").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/intNoOfCartons").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/NetMass").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/GrossMass").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/NetNetMass").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/Measurement").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/CTNMeasurement").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strCPONo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strBuyerPONo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/dtmDate").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strUser").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/intStatus").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strContainerType").Text & "', '"
                
        strSql = strSql & _
                objRoot.selectSingleNode("List/Header/strContainerName").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strCompany").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strPL_Type").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strStyleNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strCATNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strItemDescription").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strFabricContent").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strInvoiceNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strLCNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strVessel").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strLable").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strCTNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strBStyleNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strCStyleNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strReferenceNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/boolOrderCompletelyShip").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strLotNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strSEALNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strMainMarks").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strSideMarks").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strShipper").Text & "', '" & _
                ReplaceComma(objRoot.selectSingleNode("List/Header/strConsignee").Text) & "', '"
                
                
        strSql = strSql & _
                objRoot.selectSingleNode("List/Header/strBuyerProgName").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strTags").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strHangerNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strKellWoodItem").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/intRopsPerBar").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/intKnotsPerRops").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/intPcsPerKnot").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strTariffNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/dtShipDate").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strManeLable").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strPaperCap").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strColorSize").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strPortOfLoading").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strFinlDestination").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strUnits").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strCBM").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/dtPickupDate").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/intpreticketed").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/intperpack").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/intFlatpack").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/intGOH").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strCustomerId").Text & "', '" & _
                "', '" & _
                objRoot.selectSingleNode("List/Header/strProductionTeam").Text & "', '"
        
''''        strSQL = strSQL & _
''''                objRoot.selectSingleNode("List/Header/strBusinessUnit").Text & "', '" & _
''''                objRoot.selectSingleNode("List/Header/strBusinessUnitPONo").Text & "', '" & _
''''                objRoot.selectSingleNode("List/Header/strShipToAddress").Text & "', '" & _
''''                objRoot.selectSingleNode("List/Header/intStandedCTNQty").Text & "', '" & _
''''                objRoot.selectSingleNode("List/Header/dblCTNL").Text & "', '" & _
''''                objRoot.selectSingleNode("List/Header/dblCTNW").Text & "', '" & _
''''                objRoot.selectSingleNode("List/Header/dblCTNH").Text & "', '" & _
''''                objRoot.selectSingleNode("List/Header/strCTNMType").Text & "', '" & _
''''                objRoot.selectSingleNode("List/Header/strPackingAccDescription").Text & "', '" & _
''''                objRoot.selectSingleNode("List/Header/strRNNo").Text & "', '" & _
''''                objRoot.selectSingleNode("List/Header/strOrderNo").Text & "', '" & _
''''                objRoot.selectSingleNode("List/Header/strLineNo").Text & "', '" & _
''''                objRoot.selectSingleNode("List/Header/strCATLOG").Text & "' ')"

                
       strSql = strSql & _
                objRoot.selectSingleNode("List/Header/strBusinessUnit").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strBusinessUnitPONo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strShipToAddress").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/intStandedCTNQty").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/dblCTNL").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/dblCTNW").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/dblCTNH").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strCTNMType").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strPackingAccDescription").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strRNNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strOrderNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strLineNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strCATLOG").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strRequiredStyleDesc").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strLineCode").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/strItemNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/intListNo").Text & "', '" & _
                objRoot.selectSingleNode("List/Header/intGarmentType").Text & "')"
                
                
        gConxn.Execute strSql
        
        
        ' **************** Save Color Informations **************************
     
        Set z = objRoot.selectSingleNode("//ColorDetails")
        
        ' delete exsiting data
        strSqlDelate = "DELETE FROM Factory_PackingColors WHERE strPackingListNo = '" & strPLNo & "'"
        gConxn.Execute strSqlDelate
            
        For Each Child In z.childNodes
            If UCase(Child.nodeName) = "CROWS" Then
                strSql = "INSERT INTO Factory_PackingColors(strPackingListNo, strStyleId, intColorId, " & _
                         "strColor, strColorCode) " & _
                         "VALUES('" & _
                         strPLNo & "', '" & _
                         strStyleNo & "', '" & _
                         Child.selectSingleNode("intColorId").Text & "', '" & _
                         Child.selectSingleNode("strColor").Text & "', '" & _
                         Child.selectSingleNode("strColorCode").Text & "')"
                gConxn.Execute strSql
            End If
        Next
        ' *******************************************************************
        
'' ##################   Saving Size Ratio Infromations #####################
        
        Set z = objRoot.selectSingleNode("//SizeRatio")
        
        ' delete exsiting data
        strSqlDelate = "DELETE FROM Factory_Packing_Ratio WHERE strPackingListNo = '" & strPLNo & "' " & _
                       "And strStyleId = '" & strStyleNo & "'"
        gConxn.Execute strSqlDelate
        
        For Each Child In z.childNodes
            If UCase(Child.nodeName) = "CROWS" Then
                strSql = "INSERT INTO Factory_Packing_Ratio(strPackingListNo, strStyleId, strSize, dblRatio) " & _
                                 "VALUES('" & _
                                 strPLNo & "', '" & _
                                 strStyleNo & "', '" & _
                                 Child.selectSingleNode("strSize").Text & "', '" & _
                                 Child.selectSingleNode("strRatio").Text & "')"
                gConxn.Execute strSql
            End If
        Next

'' ##########################################################################



        
        If UCase(objRoot.selectSingleNode("List/Header/strPL_Type").Text) = "CTRN" Then
            Set z = objRoot.selectSingleNode("//CartonDetails")
            
            ' delete exsiting data
''            strSqlDelate = "DELETE FROM Factory_Packing_Cartons WHERE strPackingListNo = '" & strPLNo & "'"
''            gConxn.Execute strSqlDelate
                
            For Each Child In z.childNodes
                If UCase(Child.nodeName) = "ROWS" Then
                    Dim strmes As String
                    
                    strmes = Child.selectSingleNode("strMeasurement").Text
                    
                    strSql = "INSERT INTO Factory_Packing_Cartons(strPackingListNo, strStyleId, strContainerName, " & _
                             "intCTNFrom, intCTNTo, strColor, strSize, dblQty, dblGrossMass, dblNetMass, " & _
                             "dblNetNetMass, strMeasurement, intStatus, dblPrePackA, dblPrePackB, dblPrePackC,strLineCode,intTotQty,strBuyerPONo) " & _
                             "VALUES('" & _
                             strNewPlNo & "', '" & _
                             strStyleNo & "', '" & _
                             Child.selectSingleNode("strContainerName").Text & "', '" & _
                             Child.selectSingleNode("intCTNFrom").Text & "', '" & _
                             Child.selectSingleNode("intCTNTo").Text & "', '" & _
                             Child.selectSingleNode("strColor").Text & "', '" & _
                             Child.selectSingleNode("strSize").Text & "', '" & _
                             Child.selectSingleNode("dblQty").Text & "', '" & _
                             Child.selectSingleNode("dblCGrossMass").Text & "', '" & _
                             Child.selectSingleNode("dblCNetMass").Text & "', '" & _
                             Child.selectSingleNode("dblCNetNetMass").Text & "', '" & _
                             strmes & "', '', '" & _
                             Child.selectSingleNode("dblPrePackA").Text & "', '" & _
                             Child.selectSingleNode("dblPrePackB").Text & "', '" & _
                             Child.selectSingleNode("dblPrePackC").Text & "', '" & _
                             Child.selectSingleNode("strLineCode").Text & "', '" & _
                             Child.selectSingleNode("intTotQty").Text & "', '" & _
                             Child.selectSingleNode("strBuyerPONo").Text & "')"
                             
                    gConxn.Execute strSql
                End If
            Next
        Else    ' packing list type is Hanging
        
            Set z = objRoot.selectSingleNode("//HangerDetails")
            
            ' delete exsiting data
            strSqlDelate = "DELETE FROM Factory_Packing_Hangers WHERE strPackingListNo = '" & strPLNo & "'"
            gConxn.Execute strSqlDelate
            
            For Each Child In z.childNodes
                If UCase(Child.nodeName) = "ROWS" Then
                
                    strSql = "INSERT INTO Factory_Packing_Hangers(strPackingListNo, strStyleId, strContainerName, " & _
                             "intBarNo, intStringFrom, intStringTo, intKnotFrom, intKnotTo, strColor, strSize, " & _
                             "dblQty, dblGrossMass, dblNetMass, dblNetNetMass,strMeasurement,  intStatus, strCutTktNo, strRopeNO, strPCSKnot,strBuyerPONo) " & _
                             "VALUES('" & _
                             strNewPlNo & "', '" & _
                             strStyleNo & "', '" & _
                             "" & "', '" & _
                             Child.selectSingleNode("intBarNo").Text & "', '" & _
                             Child.selectSingleNode("intSFrom").Text & "', '" & _
                             Child.selectSingleNode("intSTo").Text & "', '" & _
                             Child.selectSingleNode("intKnotFrom").Text & "', '" & _
                             Child.selectSingleNode("intKnotTo").Text & "', '" & _
                             Child.selectSingleNode("strColor").Text & "', '" & _
                             Child.selectSingleNode("strSize").Text & "', '" & _
                             Child.selectSingleNode("dblQty").Text & "', '" & _
                             Child.selectSingleNode("dblHGrossMass").Text & "', '" & _
                             Child.selectSingleNode("dblHNetMass").Text & "', '" & _
                             Child.selectSingleNode("dblHNetNetMass").Text & "', '" & _
                             "" & "', '', '" & _
                             Child.selectSingleNode("strCutTktNo").Text & "', '" & _
                             Child.selectSingleNode("strRopeNO").Text & "', '" & _
                             Child.selectSingleNode("strPCSKnot").Text & "', '" & _
                             Child.selectSingleNode("strBuyerPoNo").Text & "')"
                             
                        gConxn.Execute strSql
                End If
            Next
        
        End If

        gConxn.CommitTrans
        booImportPackingDeatilsNew = True
        
Exit Function
Errh:
'    If Err.Number = 91 Then
'       Resume Next
'    End If
    MsgBox "Packing-list retrieving failed!" + Chr(13) + Err.Description, vbCritical, App.Title
    booImportPackingDeatilsNew = False
    gConxn.RollbackTrans
End Function