XML to Table problems: example 1

[florindaniel]

Hello,

If come back with an example of what happens when I
convert the XML (sorry for posting twice)

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.

Thank you
Daniel

 

[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

 

[florindaniel]

Thank you Cetin,

Unfortunately I don't think it works because that was only
a small part of the XML and XSD. The XML contains much more
tables and it is some 150 MB large.

I also don't have control over the XSD and XML files, they are supplied by the Health Insurance House (they should
have specified some MaxLength parameter but they don't).

Thank you again
Daniel

 

[cbasoz]

You don't think? Did you try at all?
Then another solution might be:

select cast(code as c(20)) as 'code', ;
  cast(description as c(200)) as 'description', ;
  validfrom, validto ;
from HEALTHDEPARTMENTTYPE ;
into cursor crsMyDepartments

Cetin Basoz
MS Foxpro MVP, MCP