How to generate XML (SQL server data types) from VFP 9 1

[USMA99]

I need to shoot out XML data daily for use by someone else's .NET app from my Fox 9.0 app that of course uses tables. I've figured out how to produce XML data with native XML data-types, but need to export XML data with MS SQL Server data-types (ie varchar, etc). I don't see any posts on this and haven't come across it in the help files. I suppose my time crunch isn't helping either. Ideas anyone?

 

[robsuttonjr]

Foxpro CURSORTOXML() will of course dump the data from the cursor into a standard xml format. After that it is up to the sql server process to translate the data from the foxpro generated xml data into the proper varchar etc. If it is being processed by the .net app the same applies. Once you have dumped to xml there is not foxpro function to manipulate the xml datatypes. Like I indicated it is up to the receiver to interpret the data types approprietly.

Regards,

Rob

 

[USMA99]

Are you completely sure? I'm getting the "fix your app, we aren't paying to change theirs" line.

I figured if all else failed, I could edit the XML data programatically, it is just text afterall. I would be open to some data not being valid for a given data type but I have a set repetitive file format so I might be able to use some of the more vanilla ones and get away with it.

 

[robsuttonjr]

Its hard to know what to say without knowing what they are doing with the xml file. Are they expecting a non-standard xml format? Foxpro dumps out in standard format. Sounds like the .net app is not setup for standard xml. But anyway check these links out as they may help you sort things out:

http://msdn.microsoft.com/library/d...html/f8ce8cf6-5def-40d0-9dcb-65570d47ce92.asp

http://msdn.microsoft.com/library/d...html/b53b14fa-c1b0-4c29-82b1-3149adf53073.asp

Regards,

Rob

 

[Mike Lewis]

USMA99,

Is your problem specifically related to varchar data types? If so, did you know that VFP 9.0 supports varchars?

If your VFP tables doesn't contain varchars, you can fix that either by using ALTER TABLE to change the chars to varchars, or copy the table to a cursor (using SELECT), and use CAST() to do the conversion.

That said, I'm not sure if this will have any effect on the XML. As far as I know, this will simply contain character strings without any padding, which are in effect varchars anyway.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[rkolva]

I had to do something similar and found it easy to just use StrTran() to modify the schema definition in the XML. I used another parse function to get just the schema into a variable so I didn't accidentally modify a record.

lsChar = '<xsd:attribute name="crc_" use="required"><xsd:simpleType><xsd:restriction base="xsd:char"><xsd:maxLength value="2"/>'
lsVarChar = '<xsd:attribute name="crc_" use="required"><xsd:simpleType><xsd:restriction base="xsd:varchar"><xsd:maxLength value="6"/>'
lsSchema = STRTRAN( lsSchema, lsChar, lsVarChar)

Ralph Kolva

 

[USMA99]

The best I can tell, XMLTOCURSOR/CURSORTOXML does not support SQL server data types. The XML adapter class allows you to import SQL server data types but strangely not export them.

Make your own schema, it's the only way. The "Ralph Kolva method" is working for me.

-Thanks All