XML to Table problems

[florindaniel]

Hello,

I'm using VFP9 and try to convert a XML file to a database.
I have the associated XSD file and I am using XMLAdapter.

What happens is some fields, even if they are defined as "string" in the XSD file, are converted onto Memo fields in the resulting tables. It's odd, because not all the string fields suffer such conversion.

Am I not configuring something well or any other idea?

Thank you
Daniel

 

[Mike Lewis]

Daniel,

Could this be because the fields in questions contain strings that are more than 254 characters long? As you probably know, 254 is the maximum length of a character field. If just one of the values in a field exceeds that length, I'd guess that the XML Adapter would make it a memo.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[florindaniel]

Hi Mike,

Thank you for help but the strings are not long. Look:

A piece of XSD
==============
<xs:element name="HealthDepartmentTypes" minOccurs="0">
<xs:annotation>
<xs:documentation xml:lang="ro">Catalogul de tipuri de departamente de sanatate</xs:documentation>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element name="HealthDepartmentType" minOccurs="0" maxOccurs="unbounded">
<xs:annotation>
<xs:documentation xml:lang="ro">Definitia tipului de departament de sanatate</xs:documentation>
</xs:annotation>
<xs:complexType>
<xs:sequence></xs:sequence>
<xs:attribute name="code" type="xs:string" use="required">
<xs:annotation>
<xs:documentation xml:lang="ro">Codul tipului de departament de sanatate</xs:documentation>
</xs:annotation>
</xs:attribute>
<xs:attribute name="description" type="xs:string" use="required">
<xs:annotation>
<xs:documentation xml:lang="ro">Descrierea tipului de departament de sanatate</xs:documentation>
</xs:annotation>
</xs:attribute>
<xs:attribute name="validFrom" type="xs:date" use="required">
<xs:annotation>
<xs:documentation xml:lang="ro">Data de inceput a valabilitatii</xs:documentation>
</xs:annotation>
</xs:attribute>
<xs:attribute name="validTo" type="xs:date" use="optional">
<xs:annotation>
<xs:documentation xml:lang="ro">Data de sfârsit a valabilitatii</xs:documentation>
</xs:annotation>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="HealthDepartments" minOccurs="0">

The corresponding piece of XML
==============================
<HealthDepartmentTypes>
<HealthDepartmentType code="SECT" description="Sectie" validFrom="2007-01-01"/>
<HealthDepartmentType code="CAB" description="Cabinet" validFrom="2007-01-01"/>
<HealthDepartmentType code="LAB" description="Laborator" validFrom="2007-01-01"/>
<HealthDepartmentType code="COMP" description="Compartiment" validFrom="2007-01-01"/>
<HealthDepartmentType code="RAD" description="Radiologie" validFrom="2007-01-01"/>
<HealthDepartmentType code="FARM" description="Farmacie" validFrom="2007-01-01"/>
<HealthDepartmentType code="AMB" description="Ambulatoriu" validFrom="2007-01-01"/>
<HealthDepartmentType code="SEDIU" description="Sediu" validFrom="2007-01-01"/>
</HealthDepartmentTypes>

<HealthDepartments>

And the structure of the resulting table
========================================
Structure for table: HEALTHDEPARTMENTTYPE.DBF
Number of data records: 8
Date of last update: 05/10/10
Memo file block size: 64
Code Page: 1252
Field FieldName Type Width Dec Index Collate Nulls Next Step
1 CODE Memo 4 No
2 DESCRIPTION Memo 4 No
3 VALIDFROM Date 8 No
4 VALIDTO Date 8 Yes
**Total** 26

And the resulting table
=======================
CODE DESCRIPTION VALIDFROM VALIDTO
Memo Memo 01/01/07 .NULL.
Memo Memo 01/01/07 .NULL.
Memo Memo 01/01/07 .NULL.
Memo Memo 01/01/07 .NULL.
Memo Memo 01/01/07 .NULL.
Memo Memo 01/01/07 .NULL.
Memo Memo 01/01/07 .NULL.
Memo Memo 01/01/07 .NULL.

 

[cbasoz]

You do not have length limitation in XSD. You could define the cursor beforehand and use XMLToCursor() w/o xsd. ie:

TEXT TO lcXML noshow
<HealthDepartmentTypes>
	<HealthDepartmentType  code="SECT" description="Sectie" validFrom="2007-01-01"/>
	<HealthDepartmentType  code="CAB" description="Cabinet" validFrom="2007-01-01"/>
	<HealthDepartmentType  code="LAB" description="Laborator" validFrom="2007-01-01"/>
	<HealthDepartmentType  code="COMP" description="Compartiment" validFrom="2007-01-01"/>
	<HealthDepartmentType  code="RAD" description="Radiologie" validFrom="2007-01-01"/>
	<HealthDepartmentType  code="FARM" description="Farmacie" validFrom="2007-01-01"/>
	<HealthDepartmentType  code="AMB" description="Ambulatoriu" validFrom="2007-01-01"/>
	<HealthDepartmentType  code="SEDIU" description="Sediu" validFrom="2007-01-01"/>
</HealthDepartmentTypes>
ENDTEXT

CREATE CURSOR healthDep (code c(10), description c(20), validFrom d)
Xmltocursor(m.lcXML, 'healthDep', 8192)

Cetin Basoz
MS Foxpro MVP, MCP