xml Import Question - V4

I’m afraid I am no expert in xml so my apologies if this is a stupid question [:D][Duh!] I am receiving an xml file and want to import the data within into a table in Navision (v4 using xml ports). However, the xml file is different than most I have seen in any related documentation in that it has a number of different levels, starting with and at the top. And the data itself is not layed out field by field, but more like record by record within the tags. Am I going to be able to get this in - or am I just wasting my time? (I will attach a copy of the file). <?xml version="1.0" standalone="yes" ?> - <DATAPACKET Version="2.0"> - <METADATA> - <FIELDS> - <FIELD attrname="VEHICLEID" fieldtype="i4" required="true"> <PARAM Name="PROVFLAGS" Value="7" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.VEHICLEID" Roundtrip="True" /> </FIELD> - <FIELD attrname="REGO" fieldtype="string" required="true" WIDTH="20"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.REGO" Roundtrip="True" /> </FIELD> - <FIELD attrname="DESCRIPTION" fieldtype="string" WIDTH="100"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.DESCRIPTION" Roundtrip="True" /> </FIELD> - <FIELD attrname="VEHICLETYPEID" fieldtype="i4" required="true"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.VEHICLETYPEID" Roundtrip="True" /> </FIELD> - <FIELD attrname="VEHICLEGROUPID" fieldtype="i4" required="true"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.VEHICLEGROUPID" Roundtrip="True" /> </FIELD> - <FIELD attrname="IPADDRESS" fieldtype="string" required="true" SUBTYPE="FixedChar" WIDTH="20"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.IPADDRESS" Roundtrip="True" /> </FIELD> - <FIELD attrname="VEHICLEREF" fieldtype="string" required="true" WIDTH="20"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.VEHICLEREF" Roundtrip="True" /> </FIELD> - <FIELD attrname="OWNERID" fieldtype="i4" required="true"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.OWNERID" Roundtrip="True" /> </FIELD> - <FIELD attrname="DRIVERID" fieldtype="i4"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.DRIVERID" Roundtrip="True" /> </FIELD> - <FIELD attrname="LATITUDE" fieldtype="r8"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.LATITUDE" Roundtrip="True" /> </FIELD> - <FIELD attrname="LONGITUDE" fieldtype="r8"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.LONGITUDE" Roundtrip="True" /> </FIELD> - <FIELD attrname="ACTIVITYDATETIME" fieldtype="dateTime"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.ACTIVITYDATETIME" Roundtrip="True" /> </FIELD> - <FIELD attrname="LOCATION" fieldtype="string" WIDTH="255"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.LOCATION" Roundtrip="True" /> </FIELD> - <FIELD attrname="ORGANISATIONID" fieldtype="i4"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.ORGANISATIONID" Roundtrip="True" /> </FIELD> - <FIELD attrname="IGNITION" fieldtype="string" SUBTYPE="FixedChar" WIDTH="1"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.IGNITION" Roundtrip="True" /> </FIELD> - <FIELD attrname="GPSVALID" fieldtype="string" SUBTYPE="FixedChar" WIDTH="1"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.GPSVALID" Roundtrip="True" /> </FIELD> - <FIELD attrname="SPEED" fieldtype="fixed" DECIMALS="2" WIDTH="9"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.SPEED" Roundtrip="True" /> </FIELD> - <FIELD attrname="HDG" fieldtype="i4"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.HDG" Roundtrip="True" /> </FIELD> - <FIELD attrname="AVLID" fieldtype="string" required="true" WIDTH="20"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.AVLID" Roundtrip="True" /> </FIELD> - <FIELD attrname="SEQNO" fieldtype="i4" readonly="true"> <PARAM Name="SERVER_CALC" Value="TRUE" Type="boolean" Roundtrip="True" /> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.SEQNO" Roundtrip="True" /> </FIELD> - <FIELD attrname="SIMNUMBER" fieldtype="string" WIDTH="30"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.SIMNUMBER" Roundtrip="True" /> </FIELD> - <FIELD attrname="AVLPHONENUMBER" fieldtype="string" WIDTH="30"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.AVLPHONENUMBER" Roundtrip="True" /> </FIELD> - <FIELD attrname="NOTES" fieldtype="string" WIDTH="254"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.NOTES" Roundtrip="True" /> </FIELD> - <FIELD attrname="SNAPSHOTINTERVAL" fieldtype="i4"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.SNAPSHOTINTERVAL" Roundtrip="True" /> </FIELD> - <FIELD attrname="SLEEPINTERVAL" fieldtype="i4"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.SLEEPINTERVAL" Roundtrip="True" /> </FIELD> - <FIELD attrname="CURRENTODOMETER" fieldtype="fixed" DECIMALS="1" WIDTH="9"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.CURRENTODOMETER" Roundtrip="True" /> </FIELD> - <FIELD attrname="ODOMETERATLASTIGNITIONON" fieldtype="fixed" DECIMALS="1" WIDTH="9"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.ODOMETERATLASTIGNITIONON" Roundtrip="True" /> </FIELD> - <FIELD attrname="SENDGEOFENCENOTIFICATIONTOMDT" fieldtype="string" SUBTYPE="FixedChar" WIDTH="1"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.SENDGEOFENCENOTIFICATIONTOMDT" Roundtrip="True" /> </FIELD> - <FIELD attrname="FULLNAME" fieldtype="string" WIDTH="100"> <PARAM Name="PROVFLAGS" Value="0" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="PERSON.FULLNAME" Roundtrip="True" /> </FIELD> - <FIELD attrname="TYPENAME" fieldtype="string" WIDTH="20"> <PARAM Name="PROVFLAGS" Value="0" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLETYPE.REF" Roundtrip="True" /> </FIELD> - <FIELD attrname="GROUPNAME" fieldtype="string" WIDTH="20"> <PARAM Name="PROVFLAGS" Value="0" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLEGROUP.REF" Roundtrip="True" /> </FIELD> - <FIELD attrname="TXSEQNUM" fieldtype="i4" readonly="true"> <PARAM Name="SERVER_CALC" Value="TRUE" Type="boolean" Roundtrip="True" /> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.TXSEQNUM" Roundtrip="True" /> </FIELD> - <FIELD attrname="PEERPORT" fieldtype="i4"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.PEERPORT" Roundtrip="True" /> </FIELD> - <FIELD attrname="SLEEPTIMEOUT" fieldtype="i4"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.SLEEPTIMEOUT" Roundtrip="True" /> </FIELD> - <FIELD attrname="DISTANCEINTERVAL" fieldtype="fixed" DECIMALS="1" WIDTH="9"> <PARAM Name="PROVFLAGS" Value="1" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="VEHICLE.DISTANCEINTERVAL" Roundtrip="True" /> </FIELD> - <FIELD attrname="CREATIONDATE" fieldtype="dateTime" required="true"> <PARAM Name="ORIGIN" Value="VEHICLE.CREATIONDATE" Roundtrip="True" /> </FIELD> - <FIELD attrname="CREATIONUSER" fieldtype="string" required="true" WIDTH="20"> <PARAM Name="ORIGIN" Value="VEHICLE.CREATIONUSER" Roundtrip="True" /> </FIELD> - <FIELD attrname="CHANGEDATE" fieldtype="dateTime" required="true"> <PARAM Name="ORIGIN" Value="VEHICLE.CHANGEDATE" Roundtrip="True" /> </FIELD> - <FIELD attrname="CHANGEUSER" fieldtype="string" required="true" WIDTH="20"> <PARAM Name="ORIGIN" Value="VEHICLE.CHANGEUSER" Roundtrip="True" /> </FIELD> <FIELD attrname="SUPPORTSDEVICEMESSAGING" fieldtype="string" readonly="true" WIDTH="1" /> - <FIELD attrname="DriverPin" fieldtype="string" WIDTH="20"> <PARAM Name="PROVFLAGS" Value="0" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="PERSON.DRIVERPIN" Roundtrip="True" /> </FIELD> - <FIELD attrname="DriverLogonCount" fieldtype="i4"> <PARAM Name="PROVFLAGS" Value="0" Type="i4" Roundtrip="True" /> <PARAM Name="ORIGIN" Value="PERSON.DRIVERLOGONCOUNT" Roundtrip="True" /> </FIELD> - <FIELD attrname="DriverPinEnabled" fieldtype="string" WIDTH="20"> <PARAM Name="PROVFLAGS" Value="0" Type="i4" Roundtrip="True" /> </FIELD> - <FIELD attrname="DriverIDFeature" fieldtype="string" WIDTH="1"> <PARAM Name="PROVFLAGS" Value="0" Type="i4" Roundtrip="True" /> </FIELD> </FIELDS> <PARAMS PRIMARY_KEY="1" /> </METADATA> - <ROWDATA> <ROW VEHICLEID="162" REGO="CNK463" DESCRIPTION="Corolla" VEHICLETYPEID="100" VEHICLEGROUPID="85" IPADDRESS="10.235.241.51" VEHICLEREF="Steve Wright" OWNERID="44" DRIVERID="1641" LATITUDE="-36.92239" LONGITUDE="174.82724" ACTIVITYDATETIME="20060131T00:40:11000" LOCATION="Auckland Workshop, Unit B, 22 Industry Rd, Penrose, Auckland, P = 09 579 2306 F = 09 579 2307" ORGANISATIONID="142" IGNITION="F" GPSVALID="T" SPEED="0.00" HDG="146" AVLID="350030950267768" SIMNUMBER="6401021101897549" SNAPSHOTINTERVAL="5" SLEEPINTERVAL="240" CURRENTODOMETER="77126.6" ODOMETERATLASTIGNITIONON="77084.0" FULLNAME="Steve Wright" TYPENAME="Toyota" GROUPNAME="Akld" PEERPORT="2000" SLEEPTIMEOUT="60" DISTANCEINTERVAL="2.0" CREATIONDATE="20041103T22:50:13000" CREATIONUSER="SYSDBA" CHANGEDATE="20050510T02:47:17000" CHANGEUSER="jo" SUPPORTSDEVICEMESSAGING="F" DriverLogonCount="1" DriverPinEnabled="F" DriverIDFeature="F" /> <ROW VEHICLEID="163" REGO="ZF3734" DESCRIPTION="Mazda" VEHICLETYPEID="166" VEHICLEGROUPID="85" IPADDRESS="10.235.241.50" VEHICLEREF="Pierre Du Plessis" OWNERID="44" DRIVERID="1987" LATITUDE="-36.92212" LONGITUDE="174.82755" ACTIVITYDATETIME="20060131T00:12:10000" LOCATION="Auckland Workshop, Unit B, 22 Industry Rd, Penrose, Auckland, P = 09 579 2306 F = 09 579 2307" ORGANISATIONID="142" IGNITION="F" GPSVALID="T" SPEED="0.00" HDG="58" AVLID="350030950267776" SIMNUMBER="6401021101897550" SNAPSHOTINTERVAL="5" SLEEPINTERVAL="240" CURRENTODOMETER="18953.3" ODOMETERATLASTIGNITIONON="18953.3" FULLNAME="Pierre Du Plessis" TYPENAME="Mazda" GROUPNAME="Akld" PEERPORT="2000" SLEEPTIMEOUT="60" DISTANCEINTERVAL="2.0" CREATIONDATE="20041103T22:50:13000" CREATIONUSER="SYSDBA" CHANGEDATE="20051204T20:25:04000" CHANGEUSER="pandrew" SUPPORTSDEVICEMESSAGING="F" DriverPinEnabled="F" DriverIDFeature="F" /> <ROW VEHICLEID="237" REGO="CNK462" DESCRIPTION="Corolla" VEHICLETYPEID="100" VEHICLEGROUPID="81" IPADDRESS="10.235.240.93" VEHICLEREF="Bevan Newbold" OWNERID="44" DRIVERID="581" LATITUDE="-37.77999" LONGITUDE="175.29308" ACTIVITYDATETIME="20060131T00:40:20000" LOCATION="Hamilton Cosmopolitan Club, Claudlands Road, Claudlands, Hamilton, 07 855 2001, P = 07 855 2001 F = 854 7775" ORGANISATIONID="112" IGNITION="F" GPSVALID="T" SPEED="0.00" HDG="220" AVLID="350030950328925" SIMNUMBER="6401021120883149" SNAPSHOTINTERVAL="5" SLEEPINTERVAL="240" CURRENTODOMETER="11310.8" ODOMETERATLASTIGNITIONON="11309.0" FULLNAME="Bevan Newbold" TYPENAME="Toyota" GROUPNAME="Waikato" PEERPORT="2000" SLEEPTIMEOUT="60" DISTANCEINTERVAL="2.0" CREATIONDATE="20041103T22:50:13000" CREATIONUSER="SYSDBA" CHANGEDATE="20050510T02:48:11000" CHANGEUSER="jo" SUPPORTSDEVICEMESSAGING="F" DriverLogonCount="1" DriverPinEnabled="F" DriverIDFeature="F" /> <ROW VEHICLEID="1707" REGO="CMA997" VEHICLETYPEID="1322" VEHICLEGROUPID="81" IPADDRESS="10.235.240.120" VEHICLEREF="Errol Edlin" OWNERID="44" DRIVERID="1580" LATITUDE="-37.00847" LONGITUDE="174.90094" ACTIVITYDATETIME="20060131T00:56:08000" LOCATION="SH1, TOTARA HEIGHTS, MANUKAU CITY, AUCKLAND, NEW ZEALAND" ORGANISATIONID="0" IGNITION="T" GPSVALID="T" SPEED="106.00" HDG="308" AVLID="352884003531101" SIMNUMBER="6401040607169809" SNAPSHOTINTERVAL="5" SLEEPINTERVAL="240" CURRENTODOMETER="35647.1" ODOMETERATLASTIGNITIONON="35578.6" SENDGEOFENCENOTIFICATIONTOMDT="F" FULLNAME="Errol Edlin" TYPENAME="Toyota" GROUPNAME="Waikato" PEERPORT="2000" SLEEPTIMEOUT="60" DISTANCEINTERVAL="2.0" CREATIONDATE="20050317T01:12:49000" CREATIONUSER="jo" CHANGEDATE="20050907T01:27:01000" CHANGEUSER="pandrew" SUPPORTSDEVICEMESSAGING="F" DriverLogonCount="1" DriverPinEnabled="F" DriverIDFeature="T" /> </ROWDATA> </DATAPACKET> Thanks in advance. Craig

You can definitely get this in, but I guess you will need to use the XMLDOM Automation objects to do that properly.

You should not have to use the MSDOM if you use an XMLPort. You use either one. The XML has most values in attributes instead of elements. You can set up your xmlport to use attributes, so it should be possible to import this. It looks more like a document definition than an actual document by the way.

You are right Daniel - this does look like a document definition which is why I am not sure if I can get this into a table using xml ports. There is data in there though. If you collapse the METADATA section you will be able to view the ROWDATA section more easily. This is where the data is, however it is in this sort of format… - <ROW VEHICLEID=“162” REGO=“CNK463” DESCRIPTION=“Corolla” VEHICLETYPEID=“100” VEHICLEGROUPID=“85” IPADDRESS=“10.235.241.51” VEHICLEREF=“Steve Wright” OWNERID=“44” DRIVERID=“1641” … …rather than… “162” “CNK463” “Corolla” “100” …and so on, which is more like what I can see in the documentation.

I am in a bit of a time crunch, so I can’t check this right this moment, but I believe you can add attribute type elements to your XMLPort, and that would enable you to import data that way. It’s still well formed XML, and even though attributes are usually used for metadata, it is perfectly legit to put data in attributes. See if you can find it in there (check properties and stuff) and post back if you can’t find it, and I’ll take a stab at it tomorrow.

Thanks mate - I think I have just discoverd the use of attibutes myself (thank God for Google!). The use of attributes is not described at all (at least not that I can find) in the App Desginers Guide or the Development I or II manuals. I will let you know how I get on. Thanks again.

Yes, the answer was the use of attributes. [:D] My only problem now is getting the file in - the size exceeds the size of the string buffer…but that is for a new topic. Thanks.

What do you mean by exceed size of text buffer ?

I was afraid that might happen. I don’t know exactly for a fact how the XMLPort works internally, but I think it reserves one text variable per element. If you have a separate element for each one of your data-attributes you should be fine. Now, with all your data-attributes in XML attributes for one single element, it is just too much information to handle. Is there any way you could get the XML provider to send you the data in elements instead of attributes? That would make this a lot easier. By the way, the attribute part of XMLPort object would make sense to people who know about those, so it’s assumed that a Navision developer that works with XMLPorts has XML knowledge. I read an MSPress book called ‘XML Step by Step’ that was really helpful.