CursorToXML() in specific XML format 1

[Gandalf23]

Hi all,

Is it possible to accomplish the following:

Say I have two table Client- Cars. Each client has a specific car(s) with its own properties.

I want to make an XML file, per client in the following format:

<client clientnr&quot;1&quot; name&quot;John Doe&quot;>
<cars>
<brand>&quot;BMW&quot;</brand>
<color>&quot;red&quot;</color>
</cars>
<cars>
<brand>&quot;Mercedes&quot;</brand>
<color>&quot;white&quot;</color>
</cars>
</client>
<client clientnr&quot;2&quot; name&quot;John Two&quot;>
<cars>
<brand>&quot;Fiat&quot;</brand>
<color>&quot;blue&quot;</color>
</cars>
</client>

etc...

Is it possible to generate this? If yes, how?

Greetings,

Gandalf.

 

[AndrewCoates]

Here's some code off the top of my head to do this:

#DEFINE CR chr(13) + chr(10)

clos data all
use client
set order to clientno && index expr = clientno
select 0
use cars
set order to clientnocarno && indexexpr = str(clientno) + str(carno)
set relation to clientno into client

local lcXML
lcXML = [<docroot>] + CR

local lnClientNo
lnClientNo = 0 && assume there's no 0 in the table

scan
  if lnClientNo <> cars.clientno
    * if this is not the first client, close the preceeding client tag
    if lnClientNo <> 0
      lcXML = lcXML + [  </client>] + CR
    endif
    * write the opening client tag for the new client
    lcXML = lcXML + [  <client clientnr=&quot;] + tran(cars.clientno) + [&quot; name=&quot;] + alltrim(client.name) + [&quot;>] + CR

    * update the client number flag
    lnClientNo = cars.clientno
    
  endif  && lnClientNo <> cars.clientno

  * write the cars tag for this car
  lcXML = lcXML + [    <cars>] + CR + ;
    [      <brand>] + alltrim(cars.brand) + [</brand>] + CR + ;
    [      <color>] + alltrim(cars.color) + [</color>] + CR + ;
    [    </cars>] + CR

endscan

* if there was anything in the cars table, then we'll need to close the client tag
if lnClientNo <> 0
  lcXML = lcXML + [  </client>] + CR
endif

* finally, close the XML
lcXML = lcXML + [</docroot>]

* now, write the XML out to a file
strtofile(lcXML, 'carlist.xml')

* close the tables
use in cars
use in clients

** END

Cheers,


Andrew Coates

http://www.civilsolutions.com.au/

OzFox 2003 Australia's VFP Conference --

http://www.ozfox.com.au/

------
DISCLOSURE
We are the Australasian Distributor for the Hentzenwerke Series of Books. (By the same token, I really do think they're the best printed VFP resource out there -- that's why we sell them)

 

[Gandalf23]

Thanks for your input Andrew. This is indeed a way to accomplish it. The tables I mentioned above are just an example. In real I am talking about 12 to 13 tables, each related to each other. With the above code it will be to complex to achive.

I had hoped that there would be somekind of statement that would do this automaticaly, as there is in C#. But unfortunatly, I suppose Foxpro hasn't.

Thanks for your help anyway.

Greetings,

Gandalf.

 

[craigber]

CURSORTOXML() doesn't support related tables as it only works on a single cursor. You can, however, use the XMLAdapter in VFP8 to handle related tables.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports&quot;

 

[Gandalf23]

Tx Craig,

the XMLAdapter does indeed do the trick. Can it also handle related cursors? Let me ask it this way: Can I set relations between x cursors as you can with tables?

Greetings,

Gandalf

 

[craigber]

Yes. Once you've got the data in a cursor, you can create an index as needed, then use SET RELATION.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports&quot;