xsd file 1

[fluppe689]

Hi all experts

I never worked with xml files but I have seen that there is a cursortoxml function which uses xsd files.
I aksed the supllier of the program in which i have to import the xml file if he has an xsd shema file.
He doesn't.
What I like to do is make myself an xsd file.
But how do I fill this up with the cursortoxml function.
This file has a header and different detail lines.
Any suggestions are very welcome because I have no experience with this kind of thing yet and it is urgent because my deadline for this is 15/11/2008.


Filip Merlier

 

[Olaf Doschke]

I don't even understand now, do you need to create the xml or read it? Cursortoxml does not need an xsd, it converts the cursor to xml and the cursor is the scheme, it's structre defines the schema. You may need a schema to use xmltocursor, if that does not parse the xml correctly, not not for the way to create xml.

Xml can represent many differnt things. Not every xml represents a table like structure, like html does also not only render tables. It's a markup language, and it doesn't have a limited set of tags, it's extensible. That's in general waht defines xml. There are several standards, so there is no single correct way to process any xml or create it or convert it.

Without any details nobody is likely to be able to help you with this.

Bye, Olaf.

 

[fluppe689]

Hello Olaf,

An example of the xml i have to make is :
<?xml version="1.0" ?>
- <BRILJANT>
- <INSTELLING>
<ID>LOGRAPPORT</ID>
<ACTIE>FOUT</ACTIE>
</INSTELLING>
- <AFL>
<KLANT>10733</KLANT>
<DATUM>12/05/2006</DATUM>
<AFLEVERBK>AP</AFLEVERBK>
- <AFLD>
<AFLEVERBK>AP</AFLEVERBK>
<ARTIKEL>TAKVI</ARTIKEL>
<BENAMING>TAKELING INGEVOLGE VERKEERSINBREUK</BENAMING>
<OMSCHR>TAKELING INGEVOLGE VERKEERSINBREUK</OMSCHR>
<AANTAL>1,00000000</AANTAL>
<STDPRIJS>105,79000000</STDPRIJS>
<KORTING1>0,00000000</KORTING1>
<KORTING2>0,00000000</KORTING2>
<EENHPR>105,79000000</EENHPR>
<BTWKODE>4</BTWKODE>
<TOTAAL>105,79000000</TOTAAL>
<VERKREK>707000</VERKREK>
<DATUM>12/05/2006</DATUM>
<ORDERBK>TP</ORDERBK>
<ORDERNR>20601058</ORDERNR>
<ORDERTPLN />
<ORDERLIJN>1</ORDERLIJN>
</AFLD>
- <AFLD>
<AFLEVERBK>AP</AFLEVERBK>
<ARTIKEL>TAKVI</ARTIKEL>
<BENAMING>TAKELING INGEVOLGE VERKEERSINBREUK</BENAMING>
<OMSCHR>TAKELING INGEVOLGE VERKEERSINBREUK</OMSCHR>
<AANTAL>1,00000000</AANTAL>
<STDPRIJS>105,79000000</STDPRIJS>
<KORTING1>0,00000000</KORTING1>
<KORTING2>0,00000000</KORTING2>
<EENHPR>105,79000000</EENHPR>
<BTWKODE>4</BTWKODE>
<TOTAAL>105,79000000</TOTAAL>
<VERKREK>707000</VERKREK>
<DATUM>12/05/2006</DATUM>
<ORDERBK>TP</ORDERBK>
<ORDERNR>20601067</ORDERNR>
<ORDERTPLN />
<ORDERLIJN>1</ORDERLIJN>
</AFLD>
- <AFLD>
<AFLEVERBK>AP</AFLEVERBK>
<ARTIKEL>TAKVI</ARTIKEL>
<BENAMING>TAKELING INGEVOLGE VERKEERSINBREUK</BENAMING>
<OMSCHR>TAKELING INGEVOLGE VERKEERSINBREUK</OMSCHR>
<AANTAL>1,00000000</AANTAL>
<STDPRIJS>105,79000000</STDPRIJS>
<KORTING1>0,00000000</KORTING1>
<KORTING2>0,00000000</KORTING2>
<EENHPR>105,79000000</EENHPR>
<BTWKODE>4</BTWKODE>
<TOTAAL>105,79000000</TOTAAL>
<VERKREK>707000</VERKREK>
<DATUM>12/05/2006</DATUM>
<ORDERBK>TP</ORDERBK>
<ORDERNR>20601217</ORDERNR>
<ORDERTPLN />
<ORDERLIJN>1</ORDERLIJN>
</AFLD>
- <AFLD>
<AFLEVERBK>AP</AFLEVERBK>
<ARTIKEL>TAKVI</ARTIKEL>
<BENAMING>TAKELING INGEVOLGE VERKEERSINBREUK</BENAMING>
<OMSCHR>TAKELING INGEVOLGE VERKEERSINBREUK</OMSCHR>
<AANTAL>1,00000000</AANTAL>
<STDPRIJS>105,79000000</STDPRIJS>
<KORTING1>0,00000000</KORTING1>
<KORTING2>0,00000000</KORTING2>
<EENHPR>105,79000000</EENHPR>
<BTWKODE>4</BTWKODE>
<TOTAAL>105,79000000</TOTAAL>
<VERKREK>707000</VERKREK>
<DATUM>12/05/2006</DATUM>
<ORDERBK>TP</ORDERBK>
<ORDERNR>20601266</ORDERNR>
<ORDERTPLN />
<ORDERLIJN>1</ORDERLIJN>
</AFLD>
- <AFLD>
<AFLEVERBK>AP</AFLEVERBK>
<ARTIKEL>TAKVI</ARTIKEL>
<BENAMING>TAKELING INGEVOLGE VERKEERSINBREUK</BENAMING>
<OMSCHR>TAKELING INGEVOLGE VERKEERSINBREUK</OMSCHR>
<AANTAL>1,00000000</AANTAL>
<STDPRIJS>105,79000000</STDPRIJS>
<KORTING1>0,00000000</KORTING1>
<KORTING2>0,00000000</KORTING2>
<EENHPR>105,79000000</EENHPR>
<BTWKODE>4</BTWKODE>
<TOTAAL>105,79000000</TOTAAL>
<VERKREK>707000</VERKREK>
<DATUM>12/05/2006</DATUM>
<ORDERBK>TP</ORDERBK>
<ORDERNR>20601305</ORDERNR>
<ORDERTPLN />
<ORDERLIJN>1</ORDERLIJN>
</AFLD>
- <AFLD>
<AFLEVERBK>AP</AFLEVERBK>
<ARTIKEL>TAKBB</ARTIKEL>
<BENAMING>TAKELING BUITENLANDSE BESTUURDER</BENAMING>
<OMSCHR>TAKELING BUITENLANDSE BESTUURDER</OMSCHR>
<AANTAL>1,00000000</AANTAL>
<STDPRIJS>105,79000000</STDPRIJS>
<KORTING1>0,00000000</KORTING1>
<KORTING2>0,00000000</KORTING2>
<EENHPR>105,79000000</EENHPR>
<BTWKODE>4</BTWKODE>
<TOTAAL>105,79000000</TOTAAL>
<VERKREK>707000</VERKREK>
<DATUM>12/05/2006</DATUM>
<ORDERBK>TP</ORDERBK>
<ORDERNR>20601316</ORDERNR>
<ORDERTPLN />
<ORDERLIJN>1</ORDERLIJN>
</AFLD>
- <AFLD>
<AFLEVERBK>AP</AFLEVERBK>
<ARTIKEL>TAKTP</ARTIKEL>
<BENAMING>TAKELING TER PLAATSE</BENAMING>
<OMSCHR>TAKELING TER PLAATSE</OMSCHR>
<AANTAL>1,00000000</AANTAL>
<STDPRIJS>23,97000000</STDPRIJS>
<KORTING1>0,00000000</KORTING1>
<KORTING2>0,00000000</KORTING2>
<EENHPR>23,97000000</EENHPR>
<BTWKODE>4</BTWKODE>
<TOTAAL>23,97000000</TOTAAL>
<VERKREK>707000</VERKREK>
<DATUM>12/05/2006</DATUM>
<ORDERBK>TP</ORDERBK>
<ORDERNR>20601355</ORDERNR>
<ORDERTPLN />
<ORDERLIJN>1</ORDERLIJN>
</AFLD>
- <AFLD>
<AFLEVERBK>AP</AFLEVERBK>
<ARTIKEL>TAKVI</ARTIKEL>
<BENAMING>TAKELING INGEVOLGE VERKEERSINBREUK</BENAMING>
<OMSCHR>TAKELING INGEVOLGE VERKEERSINBREUK</OMSCHR>
<AANTAL>1,00000000</AANTAL>
<STDPRIJS>105,79000000</STDPRIJS>
<KORTING1>0,00000000</KORTING1>
<KORTING2>0,00000000</KORTING2>
<EENHPR>105,79000000</EENHPR>
<BTWKODE>4</BTWKODE>
<TOTAAL>105,79000000</TOTAAL>
<VERKREK>707000</VERKREK>
<DATUM>12/05/2006</DATUM>
<ORDERBK>TP</ORDERBK>
<ORDERNR>20601387</ORDERNR>
<ORDERTPLN />
<ORDERLIJN>1</ORDERLIJN>
</AFLD>
- <AFLD>
<AFLEVERBK>AP</AFLEVERBK>
<ARTIKEL>TAKVI</ARTIKEL>
<BENAMING>TAKELING INGEVOLGE VERKEERSINBREUK</BENAMING>
<OMSCHR>TAKELING INGEVOLGE VERKEERSINBREUK</OMSCHR>
<AANTAL>1,00000000</AANTAL>
<STDPRIJS>105,79000000</STDPRIJS>
<KORTING1>0,00000000</KORTING1>
<KORTING2>0,00000000</KORTING2>
<EENHPR>105,79000000</EENHPR>
<BTWKODE>4</BTWKODE>
<TOTAAL>105,79000000</TOTAAL>
<VERKREK>707000</VERKREK>
<DATUM>12/05/2006</DATUM>
<ORDERBK>TP</ORDERBK>
<ORDERNR>20601404</ORDERNR>
<ORDERTPLN />
<ORDERLIJN>1</ORDERLIJN>
</AFLD>
- <AFLD>
<AFLEVERBK>AP</AFLEVERBK>
<ARTIKEL>TAKBB</ARTIKEL>
<BENAMING>TAKELING BUITENLANDSE BESTUURDER</BENAMING>
<OMSCHR>TAKELING BUITENLANDSE BESTUURDER</OMSCHR>
<AANTAL>1,00000000</AANTAL>
<STDPRIJS>105,79000000</STDPRIJS>
<KORTING1>0,00000000</KORTING1>
<KORTING2>0,00000000</KORTING2>
<EENHPR>105,79000000</EENHPR>
<BTWKODE>4</BTWKODE>
<TOTAAL>105,79000000</TOTAAL>
<VERKREK>707000</VERKREK>
<DATUM>12/05/2006</DATUM>
<ORDERBK>TP</ORDERBK>
<ORDERNR>20601417</ORDERNR>
<ORDERTPLN />
<ORDERLIJN>1</ORDERLIJN>
</AFLD>
- <AFLD>
<AFLEVERBK>AP</AFLEVERBK>
<ARTIKEL>TAKVI</ARTIKEL>
<BENAMING>TAKELING INGEVOLGE VERKEERSINBREUK</BENAMING>
<OMSCHR>TAKELING INGEVOLGE VERKEERSINBREUK</OMSCHR>
<AANTAL>1,00000000</AANTAL>
<STDPRIJS>105,79000000</STDPRIJS>
<KORTING1>0,00000000</KORTING1>
<KORTING2>0,00000000</KORTING2>
<EENHPR>105,79000000</EENHPR>
<BTWKODE>4</BTWKODE>
<TOTAAL>105,79000000</TOTAAL>
<VERKREK>707000</VERKREK>
<DATUM>12/05/2006</DATUM>
<ORDERBK>TP</ORDERBK>
<ORDERNR>20601438</ORDERNR>
<ORDERTPLN />
<ORDERLIJN>1</ORDERLIJN>
</AFLD>
</AFL>
</BRILJANT>

In the help of vfp9 i found
CURSORTOXML("LABELS", "myXMLFile.xml", 1, 512, 0, ;
"mySchema.xsd", "

http://www.microsoft.com/mySchema.xsd")

How can I make myShema.xsd and when I have that how do I fill it up with the data of my cursor

Wfg,

Filip Merlier

 

[Olaf Doschke]

What you show there smells like the display of an xml file with internet explorer. The "-" in front of the AFLD tags are part of IEs GUI to expand or collapse XML nodes, it's not within the XML file you want to create.

As I already said you don't need to create an xsd for cursortoxml to generate xml. In fact the command will generate both myxmlfile.xml and myschema.xsd.

You indirectly answered my question that you need to genrate XML.

As you also said the supplier didn't deliver any xsd file, so why do you still think it's needed?

Cursortoxml will not be able to generate such an xml file, because it's not a straight forward table like schema. Even if you had an xsd schema it would not help.

The xml has repeating tags from the first AFLD tag onwards, but before that you have another section INSTELLING which ends before an AFL node, and the AFL node contains all other nodes until after the last AFLD. While it is still rather flat it is hierarchical XML. You may be able to do somthing like that with the XMLAdapter.

You'd need three cursors INSTELLING, AFL and AFLD, with one record in INSTELLING and AFL and records of AFLD related to the one record in AFL.

This should get you started

Create Cursor instelling (instellingID I)
Create Cursor afl (aflID I)
Create Cursor afld (afldID I, aflid I)
Index on aflid tag afl
Select afl
Set Relation to aflid into afld

Insert into instelling values (1)
Insert into afl values (2)
Insert into afld values (3,2)
Insert into afld values (4,2)

o = CreateObject("xmladapter")
o.AddTableSchema("instelling")
o.AddTableSchema("afl")
o.AddTableSchema("afld")
o.tables(2).nest(o.tables(3))
o.RespectNesting = .t.
o.XMLSchemaLocation = 'c:\external.xsd'
o.ToXML("lcXML","",.F.)
? lcXML

You see that it's almost in the needed structure, VFPDataSet should be replaced by BRILJANT and it's even closer.

You need the cursors having the fieldnames and types as in your xml sample, and you're almost there. Eg it looks like AFLEVERBK is the key field in AFL and the foreign key field in AFLD, for which I used aflid in my sample code instead.

The rest might be done by fiddling with more settings of the xmladapter object, eg setting o.UTF8Encoded = .T. will encode the xml in UTF-8 instead of ansi windows codepage 1252 Or you take the lcXML result string and modify it to your needs.

Bye, Olaf.

 

[dbMark]

CursorToXml() and XmlToCursor() are both routines for XML data that is NOT nested, that is, not multiple subsets. Since your data is nested, it would be best to forget about using those two functions unless you are willing to cut and paste.

objXml = CREATEOBJECT("xmlAdapter")
objXml.LOADXML(myXMLfile)
IXML = objXml.IXMLDOMELEMENT

Make calls to this method to pull out the data...
myStreet = IIF(IXML.getElementsByTagName("Street").LENGTH<>0,IXML.getElementsByTagName("Street").ITEM(0).TEXT,"No street data")

For the repeated sections of the XML you will have to loop through each group to get or write all the data.

Needless to say, read the docs, but test thoroughly.

 

[Olaf Doschke]

How's it going, Filip?

 

[fluppe689]

Hellow Olaf,

this is what I do

*!* CREATE CURSOR instelling;
*!* ( ID c(10),;
*!* ACTIE c(4))

*!* CREATE CURSOR llv;
*!* ( LEVERBK C(2),;
*!* LEVERNR N(10),;
*!* DATUM D(8),;
*!* LEVNR C(8))
*!* INDEX on leverbk + STR(levernr,10,0) TAG leverbk additive

*!* create cursor llva;
*!* ( LEVERBK C(2),;
*!* LEVERNR N(10),;
*!* VOLGNR N(5),;
*!* ARTNR C(20),;
*!* ARTNAAM C(50),;
*!* AANTAL N(10))
*!* INDEX on leverbk + STR(levernr,10,0)+STR(volgnr,5,0) TAG leverbk additive

*!* SELECT llv
*!* SET RELATION TO leverbk INto llva

SELECT instelling
GO TOP
REPLACE instelling.ID WITH "LOGRAPPORT"
REPLACE instelling.actie WITH "FOUT"

SELECT tmplldet
SET ORDER TO TAG levnr
GO TOP
m.levnr = tmplldet.levnr
m.levernr = 100000
m.volgnr = 1
SELECT llv
SET ORDER TO TAG leverbk
APPEND BLANK
REPLACE llv.leverbk WITH "LL"
REPLACE llv.levernr WITH m.levernr
REPLACE llv.datum WITH DATE()
REPLACE llv.levnr WITH m.levnr
SELECT tmplldet
SET ORDER TO TAG levnr
GO TOP
SCAN
IF tmplldet.levnr > m.levnr
EXIT
ENDIF
SELECT llva
SET ORDER TO TAG leverbk
APPEND BLANK
REPLACE llva.leverbk WITH "LL"
REPLACE llva.levernr WITH m.levernr
REPLACE llva.volgnr WITH m.volgnr
REPLACE llva.artnr WITH tmplldet.artnr
REPLACE llva.artnaam WITH tmplldet.artnaam
REPLACE llva.aantal WITH tmplldet.geleverd
m.volgnr = m.volgnr + 1
ENDSCAN
SELECT llva
SET ORDER TO TAG leverbk
GO TOP




o = CREATEOBJECT("xmladapter")
o.ADDTABLESCHEMA("instelling")
o.ADDTABLESCHEMA("llv")
o.ADDTABLESCHEMA("llva")
o.TABLES(2).NEST(o.TABLES(3))
o.RESPECTNESTING = .T.
o.XMLSCHEMALOCATION = 'c:\external.xsd'
o.TOXML("lcXML","",.F.)
? lcXML

Question : how do i save that xml file, now i see only a print on the screen
I know this question is probably silly, but it is the first time a make some xml

wfg,

FILIP MERLIER

 

[fluppe689]

hellow Olaf,

i changed o.TOXML INTO o.TOXML("c:\lcxml.xlm,"",.T.

this is the result I got
<?xml version="1.0" encoding="Windows-1252" standalone="yes" ?>
- <VFPDataSet xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="c:\external.xsd">
- <llv>
<leverbk>LL</leverbk>
<levernr>100000</levernr>
<datum>2008-11-15</datum>
<levnr>JAPCOR</levnr>
- <llva>
<leverbk>LL</leverbk>
<levernr>100000</levernr>
<volgnr>1</volgnr>
<artnr>7400A090</artnr>
<artnaam>KLEM</artnaam>
<aantal>20</aantal>
</llva>
- <llva>
<leverbk>LL</leverbk>
<levernr>100000</levernr>
<volgnr>2</volgnr>
<artnr>6810448110</artnr>
<artnaam>8354LZPR5RDW</artnaam>
<aantal>1</aantal>
</llva>
- <llva>
<leverbk>LL</leverbk>
<levernr>100000</levernr>
<volgnr>3</volgnr>
<artnr>7553414140</artnr>
<artnaam>8257ASMCL</artnaam>
<aantal>1</aantal>
</llva>
- <llva>
<leverbk>LL</leverbk>
<levernr>100000</levernr>
<volgnr>4</volgnr>
<artnr>7554553010</artnr>
<artnaam>8371ASCSS</artnaam>
<aantal>150</aantal>
</llva>
</llv>
</VFPDataSet>

Question : where is my file instelling that has to be on top ?
Anyway already very much thanks from you, I would not know how to begin without your info.

wfg,

FILIP MERLIER

 

[Olaf Doschke]

I don't know why you don't get the instelling at the top, you added it to the xmladapter and it should be output. Do you really have a record in there? You only made 2 replace, no insert, no append blank.

You already found out how to create a file instead of putting the xml into a variable. You could also have use strtofile() to save that srtring to a file.

Bye, Olaf.

 

[fluppe689]

Hello Olaf,

The last question i hope:

how do i change
<VFPDataSet xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="c:\external.xsd"> into <BRILJANT> and 2nd why are my fields lowercase and not uppercase

WFG,

FILIP MERLIER

 

[Olaf Doschke]

Well, if you create that xml into a variable again, you can use substr() to cut off the header and replace it with BRILJANT. I don't know why the xmladapter will create loer case letters, dobt that it will have any influence on the processing of the xml.

lcXML = "<?xml version="1.0" ?><BRILJANT>"+StrExtract(lcXML,"<instelling>","</afl>",1,5)+"</BRILJANT>"
strtofile(lcXML,"final.xml")

Bye, Olaf.

 

[Olaf Doschke]

use lcXML=Upper(lcXML) if you really need upper case.

Bye, Olaf.

 

[fluppe689]

Hey Olaf,

This is what I do now
SET date TO dmy
SET CENTURY on
o = CREATEOBJECT("XMLADAPTER")
o.ADDTABLESCHEMA("INSTELLING")
o.ADDTABLESCHEMA("LLV")
o.ADDTABLESCHEMA("LLVA")
o.TABLES(2).NEST(o.TABLES(3))
o.RESPECTNESTING = .T.
o.XMLSCHEMALOCATION = 'C:\EXTERNAL.XSD'

o.TOXML("lcXML","",.F.)
lcXML=Upper(lcXML)
lcXML = "<?xml version='1.0' ?><BRILJANT>"+StrExtract(lcXML,"<instelling>","</afl>",1,5)+"</BRILJANT>"
strtofile(lcXML,"c:\final.xml")

The result is :
<?xml version="1.0" ?>
<BRILJANT />

only this.
What do I wrong ???

Filip Merlier

 

[fluppe689]

Hello Olaf,

Sorry i found the solution. Some silly mistake.
One question rest
Why does the xml changes the date format ?
Otherwise else is going very fine

wfg,

FILIP

 

[Olaf Doschke]

As you do the upper before the StrExtract(), the StrExtract does not find anything, as it is searching case sensitive, and as you search for lower case tags it does not find anything.

Bye, Olaf.