Hi:
Here's what I understand from your post:
1) you either have XML files on disk OR you have some kind of XML information stored in an SQL server database
2) you want the XML files or information to become a single txt file
3) you want to do "convert" the files or information from XML to TXT using SAS
4) you seem to have done some experimentation of some kind because the issue of a 32K size limit is mentioned
5) last, you asked something confusing: "how to maintain the length of the xml FILE in each CELL" (This seems to imply that you have an ENTIRE XML file in one table cell or database cell??? That seems possible, but odd to me. You might have some XML data or data in XML form with XML tags stored in a data base.)
XML is generally stored in an ASCII text file. The file has a .XML extension instead of a .TXT extension. Generally, an XML file conforms to certain rules:
1) the XML file should have an XML processing instruction or identifying tag at the top of the file, something like this:
2) the XML file should have 1 and only 1 root tag
More advanced uses of XML exist -- such as shopping carts or on-line forms -- where you do something on a web page and your information is translated to XML form and then sent to a server program that accepts the XML "stream" and uses it as input to a program. It's possible that the XML info in this case is stored in a data base -- but it should be possible using data base utilities to get the XML from data form into ASCII text form.
Anyway, back to the simpler question of combining XML files, if this XML file is stored on my C: drive in this location: c:\temp\mygifts.xml
[pre]
<?xml version="1.0" ?>
<TABLE>
<MYGIFTS>
<NAME> alan </NAME>
<AGE> 15 </AGE>
<GIFTIDEA> CD </GIFTIDEA>
</MYGIFTS>
<MYGIFTS>
<NAME> bob </NAME>
<AGE> 16 </AGE>
<GIFTIDEA> Games </GIFTIDEA>
</MYGIFTS>
<MYGIFTS>
<NAME> carla </NAME>
<AGE> 15 </AGE>
<GIFTIDEA> Jewelry </GIFTIDEA>
</MYGIFTS>
<MYGIFTS>
<NAME> dave </NAME>
<AGE> 14 </AGE>
<GIFTIDEA> iTunes </GIFTIDEA>
</MYGIFTS>
</TABLE>
[/pre]
AND, if this OTHER file is stored in c:\temp\moregifts.xml:
[pre]
<?xml version="1.0" ?>
<TABLE>
<MORE>
<NAME> edna </NAME>
<AGE> 15 </AGE>
<GIFTIDEA> iTUNES </GIFTIDEA>
</MORE>
<MORE>
<NAME> frank </NAME>
<AGE> 14 </AGE>
<GIFTIDEA> CD </GIFTIDEA>
</MORE>
<MORE>
<NAME> gail </NAME>
<AGE> 15 </AGE>
<GIFTIDEA> Books </GIFTIDEA>
</MORE>
</TABLE>
[/pre]
....Since these are both "regular", non-hierarchical XML files, I can use the SAS XML Libname engine to read the XML files into a single SAS dataset:
[pre]
libname onefile xml 'c:\temp\mygifts.xml';
libname twofile xml 'c:\temp\moregifts.xml';
data work.giftidea;
set onefile.mygifts
twofile.more;
run;
libname onefile clear;
libname twofile clear;
ods listing;
proc print data=work.giftidea;
title 'SAS dataset from XML file';
run;
[/pre]
And then, once the files are together in a SAS dataset (4 obs from the first XML file and 3 obs from the second XML file), I could run a PROC EXPORT to export it to CSV or any other kind of file supported by PROC EXPORT. I could even write my own DATA step program to write the SAS dataset to a flat file. For valid, non-hierarchical XML file, I could have read the file with the XML Libname engine and then written it out in one step with the data step, but I preferred to show the intermediate stages along the way. Here's a sample PROC EXPORT:
[pre]
proc export data=work.giftidea
outfile='c:\temp\flatfile.txt'
dbms=csv replace;
run;
[/pre]
If you have non-standard or hierarchical XML files, then you may have to use an XMLMAP to "map" from the non-standard XML to SAS column names. I know that if the XML files are in ASCII text files on disk, then SAS can either read them with the SAS XML Libname engine or it can read them with the Libname engine and an XMLMAP.
If your XML is not in identifiable .XML files and/or if you are asking whether SAS can read XML out of the SQL server database directly -- I don't know the answer to that. It seems that Tech Support might be your best resource to answer that question.
cynthia