05-10-2018 02:33 AM
I have a requirement to read a text file(.sas code file) and save in to MS SQL server DB table(complete file in xml object column)by converting to xml format.
As of now I can create XML file on disk by SAS program and I can connect to MS SQL serve for DB transactions.
But here challenges in step wise way(all in SAS code) are ,
1) Read text file via SAS - Done
2) Convert aboveto xml object (not file) - How to do ?
3) Save this object to MS SQL DB - How to do ?
Requesting to please have the suggestions.
05-10-2018 03:29 AM
05-10-2018 05:45 AM
05-10-2018 06:24 AM
I am assuming SAS will have xml object like DB or C#.
If not, then by using SAS code how can we insert the complete file (by reading from disk) in to MS sql database(pass-through or lib name) ?
05-10-2018 07:07 AM
SAS has two data types: character and numeric. Since XML is text, you can store it in a character variable, provided that the XML in question does not exceed 32767 characters.
05-10-2018 08:19 AM
You are going to have a problem moving a complete SAS program file (which could be thousands of lines long) into a single XML "object" in the server. SAS has a 32K limit on the size of character variable.
So either move the original file line by line into an SQL server table and have the database transform it into an XML object.
Or move the XML file line by line into the SQL server and have the server combine the lines into a single blob/clob or whatever they use to store XML.
05-10-2018 09:11 AM