Mike Lewis
Programmer
I am trying to use XML to move data from VFP to SQL Server. I'm nearly there, but I've got a couple of irritating problems. I hope someone can help.
Basically, I have a bunch of native VFP tables (DBFs), and a similar number of back end tables on SQL Server 2000. The DBFs have essentially the same structures as the corresponding back-end tables.
I need to copy selected records from the DBFs to the back end. I want to do it in a generic way, that is, such that my code does not need to know any of the column names.
My idea is to create an XML representation of the relevant records in the DBF; send this as a string to a stored procedure; the stored proc will then use SQL Server's XML features (specifically sp_xml_preparedocument and OPENXML()) to create a record set, which I can then insert into the back end tables.
I'm using the following VFP code to create the XML:
I've more-or-less got this working, except for the following problems:
(i) VFP is outputting all the field names in lower case. SQL Server wants them to be in the same case as the column names on the back end. I don't see any way of achieving this, other than to standardise all the column names in the SQL tables to lower-case. That's not possible, as I have no control over the database schema.
(ii) VFP outputs logical fields as text strings, "false" and "true" respectively. As these are mapped to bit fields, SQL Server expects them to be 0 and 1.
Has anybody any experience in this area that might help? I'm sure I'm not the only person who is using XML to move data from VFP to SQL Server, but it seems to be much more difficult than I expected.
I know I can generate INSERT commands programmatically, but I thought XML would be a neat way to achieve my goal.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
Basically, I have a bunch of native VFP tables (DBFs), and a similar number of back end tables on SQL Server 2000. The DBFs have essentially the same structures as the corresponding back-end tables.
I need to copy selected records from the DBFs to the back end. I want to do it in a generic way, that is, such that my code does not need to know any of the column names.
My idea is to create an XML representation of the relevant records in the DBF; send this as a string to a stored procedure; the stored proc will then use SQL Server's XML features (specifically sp_xml_preparedocument and OPENXML()) to create a record set, which I can then insert into the back end tables.
I'm using the following VFP code to create the XML:
Code:
CURSORTOXML(SELECT(),"SomeVariable",3)
I've more-or-less got this working, except for the following problems:
(i) VFP is outputting all the field names in lower case. SQL Server wants them to be in the same case as the column names on the back end. I don't see any way of achieving this, other than to standardise all the column names in the SQL tables to lower-case. That's not possible, as I have no control over the database schema.
(ii) VFP outputs logical fields as text strings, "false" and "true" respectively. As these are mapped to bit fields, SQL Server expects them to be 0 and 1.
Has anybody any experience in this area that might help? I'm sure I'm not the only person who is using XML to move data from VFP to SQL Server, but it seems to be much more difficult than I expected.
I know I can generate INSERT commands programmatically, but I thought XML would be a neat way to achieve my goal.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk