Welcome to WebmasterWorld Guest from

Forum Moderators: httpwebwitch

Message Too Old, No Replies

XML Import issue

Using VB6, MSXML4 and ADO2.7



9:09 am on Aug 16, 2006 (gmt 0)

10+ Year Member

I am tryin to import a xml file which has been generated by a java based api.

Example of XML:

<?xml version="1.0" encoding="UTF-8"?>
<BusinessObjects xmlns="http://schemas.primavera.com/2004b/API/MorganXMLExporter" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.primavera.com/2004b/API/prmbo c:\Program Files\Primavera\IntegrationAPI\schema\PRMBO.xsd">

When I am trying to import it using VB6, it seems to not want to process any nodes after the businessobjects node. If I manually remove the businessobjects node and replace with a single "projects" node with no attributes it works fine! Anyone come across this? Do I need to escape the businessobjects node attributes?




3:51 pm on Aug 16, 2006 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member

The input XML appears to be well-formed and the namespace declarations on <BusinessObjects> should not in and of themselves have any impact on the ability to process the nodes.

What is it you want to do with the input, and what code are you using? I'm not familiar with VB myself but another poster might be able to shed some light.


9:58 am on Aug 17, 2006 (gmt 0)

10+ Year Member

It turrned out to be an issue with name space!

Originally I had been selecting the nodes with plain old "/BusinessObjects", I had to set a property which pointed to the namespace within the file: objXMLDOM.setProperty "SelectionNamespaces" ..

I then had to use the associated namespace variable within my node selection: "/xsi:BusinessObjects".

This now works fine :)

Private Sub Form_Load()

' Author: Chris Belfield
' Description: Morgan EST API Import routine for MSSQL 2000 using MSXML 4.0, ADO 2.7 and VBSendMail

Dim objProjects

Dim sProjList
Dim sFileName
Dim sResult
Dim sProjID

sResult = "Import was completed successfully."

Const adOpenKeyset = 1
Const adLockOptimistic = 3

Dim objADORS As New ADODB.Recordset
Dim objADOCnn As New ADODB.Connection


sSql = "Delete FROM t_import2"
Set objADODel = objADOCnn.Execute(sSql)

objADORS.Open "SELECT * FROM t_import2 WHERE 1 = 2", objADOCnn, adOpenKeyset, adLockOptimistic

Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False

' Set to export folder of API
objXMLDOM.setProperty "SelectionNamespaces", "xmlns:xsi='http://schemas.primavera.com/2004b/API/MorganXMLExporter'"
sFileName = "c:\xml\export6.xml"

objXMLDOM.Load sFileName

If Not objXMLDOM.Load(sFileName) Then
sResult = "Error: " & objXMLDOM.parseError.reason
End If

Set objProjects = objXMLDOM.selectNodes("/xsi:BusinessObjects/xsi:Project")

For Each objProjIDNodes In objProjects

sProjID = objProjIDNodes.selectSingleNode("xsi:Id").nodeTypedValue

Set objActivities = objXMLDOM.selectNodes("/xsi:BusinessObjects/xsi:Project[xsi:Id[text()='" & sProjID & "']]/xsi:Activity")

' Selecting each Activity

For Each objActivityNode In objActivities

sActivityID = objActivityNode.selectSingleNode("xsi:Id").nodeTypedValue

' Selecting each Resource
Set objResources = objXMLDOM.selectNodes("/xsi:BusinessObjects/xsi:Project[xsi:Id[text()='" & sProjID & "']]/xsi:Activity[xsi:Id[text()='" & sActivityID & "']]/xsi:ResourceAssignment")

For Each objResourceNode In objResources

sResourceID = objResourceNode.selectSingleNode("xsi:ResourceId").nodeTypedValue
lPlannedUnits = objResourceNode.selectSingleNode("xsi:PlannedUnits").nodeTypedValue
lActualUnits = objResourceNode.selectSingleNode("xsi:ActualUnits").nodeTypedValue

' Update Table
With objADORS
.Fields("ProjectID") = sProjID
.Fields("ActivityID") = sActivityID
.Fields("ResourceID") = sResourceID
.Fields("PlannedUnits") = lPlannedUnits
.Fields("ActualUnits") = lActualUnits
End With





Set objProjects = Nothing
Set objActivities = Nothing
Set objResources = Nothing
Set objADORS = Nothing
Set objADOCnn = Nothing

End Sub


Featured Threads

Hot Threads This Week

Hot Threads This Month