XML Format Import

dalvarezpenaranda

Hi! I want to import a XML into Navision 4.0, but its schema is not the same that Navision uses. I post here the field that I wanna import. I search information about it but I don´t view what can I do, I dont know If I want to convert XML to another version otherwise I wanna create a codeunit that imports the data into the database.

This is the XML field:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:AttributeType name='strPersonnelNo' rs:number='1' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='9'/>
</s:AttributeType>
<s:AttributeType name='strTESSId' rs:number='2' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='20'/>
</s:AttributeType>
<s:AttributeType name='strVersion' rs:number='3' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='10'/>
</s:AttributeType>
<s:AttributeType name='strPersGMU' rs:number='4' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='4'/>
</s:AttributeType>
<s:AttributeType name='strPersLMU' rs:number='5' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='3'/>
</s:AttributeType>
<s:AttributeType name='dtmSubmitDate' rs:number='6' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='dtmChgDate' rs:number='7' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='dtmDayDate' rs:number='8' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='dtmPrdEnd' rs:number='9' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='strUpperChargeCode' rs:number='10' rs:nullable='true' rs:maydefer='true'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='strGMU' rs:number='11' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='4'/>
</s:AttributeType>
<s:AttributeType name='strRate' rs:number='12' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='1'/>
</s:AttributeType>
<s:AttributeType name='dtmHours' rs:number='13' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='r4' dt:maxLength='4' rs:precision='7' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='strPFAFlag' rs:number='14' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='1'/>
</s:AttributeType>
<s:AttributeType name='strActivityCode' rs:number='15' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='5'/>
</s:AttributeType>
<s:AttributeType name='strActivityDesc' rs:number='16' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='20'/>
</s:AttributeType>
<s:AttributeType name='strTOWCode' rs:number='17' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='5'/>
</s:AttributeType>
<s:AttributeType name='strTOWDesc' rs:number='18' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='20'/>
</s:AttributeType>
<s:AttributeType name='strCountryCode' rs:number='19' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='3'/>
</s:AttributeType>
<s:AttributeType name='strError' rs:number='20' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='8'/>
</s:AttributeType>
<s:AttributeType name='dtmOvt1' rs:number='21' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='r4' dt:maxLength='4' rs:precision='7' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='dtmOvt2' rs:number='22' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='r4' dt:maxLength='4' rs:precision='7' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='strNarrative' rs:number='23' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='536870910' rs:long='true'/>
</s:AttributeType>
<s:AttributeType name='strValidateVersion' rs:number='24' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='10'/>
</s:AttributeType>
<s:AttributeType name='dValidateBuildDate' rs:number='25' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16' rs:fixedlength='true'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row strPersonnelNo='999795968' strTESSId='393256029691' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-16T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='ECH00300' dtmHours='20' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0' strNarrative='MS'
strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='3932560296912' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-27T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='ECH00300' dtmHours='40' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0' strNarrative='MS'
strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='3932560296913' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-28T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='ECH00300' dtmHours='40' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0' strNarrative='MS'
strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='3932560296914' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-29T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='ECH00300' dtmHours='40' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0' strNarrative='MS'
strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='3932560296915' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-30T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='ECH00300' dtmHours='40' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0' strNarrative='MS'
strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='3932560296916' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-31T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='ECH00300' dtmHours='40' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0' strNarrative='MS'
strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='393256029692' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-17T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='ECH00300' dtmHours='30' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0' strNarrative='MS'
strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='393256029695' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-20T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='ECH00300' dtmHours='40' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0' strNarrative='MS'
strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='393256029696' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-21T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='ECH00300' dtmHours='40' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0' strNarrative='MS'
strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='393256029697' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-22T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='ECH00300' dtmHours='40' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0' strNarrative='MS'
strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='393256029698' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-23T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='ECH00300' dtmHours='40' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0' strNarrative='MS'
strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='393256029699' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-24T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='ECH00300' dtmHours='40' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0' strNarrative='MS'
strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='3932560364101' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-16T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='711F02' strRate='' dtmHours='50' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0'
strNarrative='Autoformación' strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='39325603641012' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-27T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='711F02' strRate='' dtmHours='30' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0'
strNarrative='Autoformación' strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='39325603641013' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-28T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='711F02' strRate='' dtmHours='30' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0'
strNarrative='Autoformación' strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='39325603641014' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-29T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='711F02' strRate='' dtmHours='30' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0'
strNarrative='Autoformación' strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='39325603641015' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-30T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='711F02' strRate='' dtmHours='30' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0'
strNarrative='Autoformación' strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='39325603641016' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-31T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='711F02' strRate='' dtmHours='30' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0'
strNarrative='Autoformación' strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='3932560364102' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-17T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='711F02' strRate='' dtmHours='40' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0'
strNarrative='Autoformación' strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='3932560364105' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-20T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='711F02' strRate='' dtmHours='30' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0'
strNarrative='Autoformación' strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='3932560364106' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-21T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='711F02' strRate='' dtmHours='30' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0'
strNarrative='Autoformación' strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='3932560364107' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-22T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='711F02' strRate='' dtmHours='30' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0'
strNarrative='Autoformación' strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='3932560364108' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-23T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='711F02' strRate='' dtmHours='30' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0'
strNarrative='Autoformación' strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
<z:row strPersonnelNo='999795968' strTESSId='3932560364109' strVersion='2.7.5' strPersGMU='0584' strPersLMU='004'
dtmSubmitDate='2007-08-31T00:00:00' dtmChgDate='2007-08-24T00:00:00' dtmDayDate='2007-08-31T00:00:00' dtmPrdEnd='2007-08-31T00:00:00'
strUpperChargeCode='711F02' strRate='' dtmHours='30' strCountryCode='791' strError='NN' dtmOvt1='0' dtmOvt2='0'
strNarrative='Autoformación' strValidateVersion='2.0' dValidateBuildDate='2002-04-22T14:44:44'/>
</rs:data>
</xml>

jlandeen

I think there are a number of issues with the XML data that you are trying to pass through to Navision.

Here are a few recommendations:
- remove the schema from the XML data
- remove any namespaces in the XML data (Navision XML Ports don't handle namespaces nicely - and there are posts on MIBUSO and I think on Partnersource outlining how to do that) [I have no idea why MS built it this way but I think that's definately something they need to fix]
- once the schema and Namespace is removed it looks like you could just import it into Navision using an XML port with a Top level element of Row that has all the elements.

An alternative is to use a codeunit and the XML Document Object Model (DOM) to manually consume the XML data. Here you would have to use Automation variables and these definately aren't the cleanest in Navision but it can be done. You could look at some of the code in the Biztalk Codeunits from MS for examples.

Miklos_Hollender

And another hint: to avoid a lot of headaches, read non-text, f.e. datetime variables as EVALUATE(DateTimeVar,DateTimeTextVarFromXML,9). The important point is the 9.

jlandeen

And another hint: to avoid a lot of headaches, read non-text, f.e. datetime variables as EVALUATE(DateTimeVar,DateTimeTextVarFromXML,9). The important point is the 9.

Along those same lines you can change how Navision Formats & Evaluates data with the XML Port Property "Format/Evaluate". Simply change it from C/Side [the default] to XML. This should allow you to skip manually evaluating each element or attribute in the XML port. It should also make sure that when you send data out to other applications that it follows more ISO standards for data formating (especially with boolean and date variables).