DATA Step, Macro, Functions and more

Read a file in SAS and save in ms sql server as XML

Reply
Occasional Contributor
Posts: 13

Read a file in SAS and save in ms sql server as XML

Dear Folk,

 

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.

 

Regards,

Tushar Jain

Super User
Posts: 5,915

Re: Read a file in SAS and save in ms sql server as XML

Just out of curiosity, what is the benefit of storing your SAS programs in XML objects?
Also, SAS programs is in clear text, so I can't see any benefit from having SAS to create the XML object. Go directly to SQL Server tools for this.
Data never sleeps
Occasional Contributor
Posts: 13

Re: Read a file in SAS and save in ms sql server as XML

XML object is required to save in Database table for reporting and archival purpose.
Super User
Posts: 10,544

Re: Read a file in SAS and save in ms sql server as XML

Define "XML object". For me a XML object (see DOM) is either a data island, a tree or a node.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 13

Re: Read a file in SAS and save in ms sql server as XML

Posted in reply to KurtBremser

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) ?

Super User
Posts: 10,544

Re: Read a file in SAS and save in ms sql server as XML

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 8,272

Re: Read a file in SAS and save in ms sql server as XML

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.

Occasional Contributor
Posts: 13

Re: Read a file in SAS and save in ms sql server as XML

Hell Tom,

If there is no way to move complete xml in one go then line by line is only option left(but i have not chose it because of it's inefficient).
I here want to convert complete file in to some xml object, just like xml output file and then move then object to db column in single update uery.

Or if you can think any other solution then it will be appreciated.

Thanks,
Ask a Question
Discussion stats
  • 7 replies
  • 148 views
  • 4 likes
  • 4 in conversation