& in xml datafile

I have created a simple vbscript that uses the XMLSQL toolkit to do bulk loads into SQLSERVER 2000.

basically the system consists of 3 files. The VB script which connects to the DB and executes SQLXMLBulkLoad. This script uses a map schema file (xml) and a data file (xml)

The issue that arrises is that one of my fields "Description" contains the "&" in some rows of data. You may say, well just change the & to & a m p ; (without spaces) and all will be dandy! BUT..the xml data file is being auto generated from a progress script that is basically dumping a progress table.

The main idea.. dump from a progress table and insert into SQLSERVER.

I currently have a system of dumping a comma-delemitted file then using isql do a bulk insert using a sql script.

I'm just testing the limits of XML and want to see if this is a better way. It seems much easier, except for the fact that you can't have a "&" in the xml document. I've heard tales of using CDATA to fix this....

is there a way to do this instead of changing the data on the dumping of the data.xml file? Can you "allow" the & to be in an XML document?


You could say aren't there other ways like SQL's DTS's....and I could get into a long disscusion about why I'm doing it the way I am, but for now.....this is how I'd like to do it.


Thanks

Try something like this: