Kevin,

Thanks for the experience info. I've asked a similar question on the SAS consultants forum (http://groups.google.com/group/sasconsulting).

This limitation, which probably has historical reasons, seems antiquated now. Complex datatypes and today's '1TB for $200) world makes a cap on a variable length antiquated IMO.

I need to store XML (or something else) which is long into the dataset. It seems that this cap should be lifted and allow a dataset to grow to any size desired.

I've started coding a C# workaround but I don't like having to it. I'll try and bend an ear at SGF in Orlando.

Alan

Alan Churchill Savian "Bridging SAS and Microsoft Technologies" www.savian.net

-----Original Message----- From: Kevin Myers [mailto:KMyers1@clearwire.net] Sent: Wednesday, August 16, 2006 6:57 PM To: Alan Churchill; SAS-L@LISTSERV.UGA.EDU Subject: Re: Tough XML question

I am often hindered by the same limits with my work in the petroleum industry. Unfortunately, I have not found any *good* solutions. In the past, I have resorted to such ugliness as routing output from SAS into a text file, then using some type of command line import/update utility to get the data into the destination DBMS or application.

----- Original Message ----- From: "Alan Churchill" <SASL001@savian.net> To: <SAS-L@LISTSERV.UGA.EDU> Sent: Wednesday, August 16, 2006 19:27 Subject: Tough XML question

> Guys, > > > > SQL Server supports an XML datatype. I would like to take a SAS dataset, > convert it to XML, and then store it in a SQL Server xml type column. > > > > Take for example the following: > > > > libname outdata xml 'c:\temp\sample.xml' xmlmeta=schemadata; > > > > data outdata.shoes ; > > set sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > sashelp.shoes > > ; > > run; > > > > This produces a really long sample XML file. > > > > How do I then insert this data into a SQL Server variable? SAS variables are > capped at 32K so I am struggling as to how to get it into SQL Server. > > > > > > Thanks, > > Alan > > > > Alan Churchill > Savian "Bridging SAS and Microsoft Technologies" > www.savian.net <http://www.savian.net/> > >