Memo files to SQL Server via XML

[glamb]

I've been successfully using XML to load SQL Server tables from VFP tables. However, I've hit a snag with memo fields. I mapped the memo field to a varchar(max) field in SQL Server, but all I get is one character when I migrate. I'm using CURSORTOXML(0,"xmldatatoinsert", 1,0,0) and like I said, it works for every other field except the memo field.
Thanks.

 

[Olaf Doschke]

I know a bug of ODBC will return SQL Server Varchar(max) as C(1), or even C(0), if you fetch an empty field. Still the values exist in SQL Server in that case. So the question is, where do you see the single char? Back in VFP after retrieving data via SQLExec()? What do you see, if you query the tables in Sql Server Managerment Studio (SSMS)?

I ask, because cursortoxml() does put whole memo field contents into the xml it outputs. Are you even only getting one char there?

Bye, Olaf.

 

[glamb]

The XML looks fine. But what I have to do is load it into a temp SQL table that has a text field, then insert into the real table that has a varchar(max) field by using CAST AS VARCHAR.
I'm just looking for a way to skip that step.

 

[Olaf Doschke]

I still don't get where the text is cropped to just one char. On the way from XML to SQL Server, if you import to Varchar(Max) directly? And have you looked into the Varchar(Max) fields via SSMS? Or how? (you didn't answer that question yet).

If the XML is fine and well formed, have you asked in MSSQL forum with a small sample XML, how that would best be imported by SQL Server?

You have some variants with CursorToXML with the encoding, and wrapping memo in CDATA or not, for example, have you tried variations?

Bye, Olaf.