XML Import options

Hi

I have a set of over 4000 xml files which are regularly edited and updated.

Access to the files is controlled from a DB hosted on SQL2K.

I have a text parsing routine on the front end written many years ago when XML was
still a child. It didn't use a standard xml parser. This is used to track the
changes and store the xml in ntext fields in the DB. It is chunked up into
manageable portions by the routine and then added to the DB using a standard
Insert/Update SP.

I have been thinking for a long time that there must be an easier way to do this
and have looked at openXML, XMLBulkLoad/DTS and Updategrams.

I have no real problems getting BulkLoad to work, but because of the nature of the
data (i.e. in XML files on the filesystem) I would have to activate the DTS each
time an xml file is saved/checked-in. Using dtsrun or an ole controlled routine to
run the bulkload is not very attractive.

With openXML, my problems are different. The inability to declare an ntext
variable in an sp means I am left with a bit of a kludge involving declaring a
sequence of nvarchar(4000) variables and concatenating them together to pass to
sp_xml_preparedocument. Not very elegant. And I need to know the maximum possible
filesize before writing the sp.

Updategrams are the most interesting as the check-in process already involves an
xslt transform. An updategram template could patched into this using the
"document()" function to pass the the changed xml to the updategram template. But
as the size of the xml strings could be quite large (>8000 chars) I feel I would
probably run into limitations on the size of a URI.

So where to now? Back to XMLBulkLoadDTS ?

Is there a reliable way of slicing up a series of files on demand - perhaps in a
trigger or sp - and storing the xml fragments in an ntext field ?

Any suggestions guys ?

TIA


.

Loading