This is my query (just an example) to the SQL server: SELECT No, Name From Items FOR XML PATH(‘Item’)
The query returns more than 1.1 mill (300 mb) lines or more, so thats why i return it into a blob.
So maybe you need to do it in a different way. You’re talking about moving 300 MB from SQL to your client program. Why? That will make everything slow. Can’t you filter your query? or partially return some lines in a batch?
What are you going to do with the resultant temp.xml file?
A few things to bear in mind…
By default FOR XML returns the results as Unicode text which may not be what you want if you’re going to further process the file.
Also the result will not be a valid XML file - it will be an XML fragment without a valid root opening tag or root closing tag - so if you try opening it in Explorer it will only display the first record non-formatted.
In addition the result will be one large string of characters with no line breaks between Items.
Thanks for the feed back.
The issue is not whether the document is a valid XML or not. I solve this by just wrapping my query with an extra SELECT () FOR XML.
My problem is that I only get 2033 charaters return in my blob no matter what I do.
Try with a TOP clause to see if that makes it work.
I only get 2033 characters
Is that the size of the temp.xml file on disk?
I wouldn’t save the file to c:\ as later versions of Windows can get stroppy creating files in the root folder - use BlobToFile(SQL:DATA,path() & ‘\temp.xml’) instead
See attached for example that I’ve used to retrieve 700MB XML fragments.
Few things to note…
1 - Dummy file doesn’t need the TURBOSQL switch because BLOB fields are outside of the RECORD declaration - so Clarion doesn’t send verification commands to the backend to check if the field is present and of the correct datatype
2 - Using Prop:SQLRowSet avoids the need for a stored procedure on the backend
3 - Note how the SQL command CASTs the Unicode text to ASCII, adds a processing instruction, valid root start and termination etc
I have tried storing data in BLOBs (TPS) and varchar(max) (SQL-Server). My data was Microsoft Word documents (ie binary).
To store binary data in SQL-Server us varchar(max).
Use BLOBTOFILE to get the data out. However, FWIW I had corruption problems in both cases and now store my data within the filesystem and only store the filename in the database. But that was only my experience.
I haven’t looked at it but might Capesoft’s XFiles help you since you are storing XML?