Re: XML Import options
- From: "Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 29 Jun 2005 12:10:53 -0700
I would probably go with the SQLXMLBulklload and DTS solution for SQL Server
2000.
OpenXML may work as well, but if the documents are too big, you get into
scaling issues.
BTW: If you pass the content of the file from the client to the server
stored proc directly, you can use NTEXT/TEXT as stored proc parameters!
Best regards
Michael
"Feargal Hogan" <feargalhNO@xxxxxxxxxxxxxxxxxxx> wrote in message
news:eeH0WcIfFHA.572@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>
.
- Follow-Ups:
- Re: XML Import options
- From: Feargal Hogan
- Re: XML Import options
- References:
- XML Import options
- From: Feargal Hogan
- XML Import options
- Prev by Date: Re: Annotated schema with views?
- Next by Date: Update primary key und foreign key with updategram
- Previous by thread: XML Import options
- Next by thread: Re: XML Import options
- Index(es):
Relevant Pages
|
