Re: XML Import options
- From: "Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 31 Aug 2005 09:25:55 -0700
My apologies for the late reply, but I just returned from a month-long
vacation.
You would write a stored procedure such as (in pseudo-code):
create procedure LoadXML @x ntext as
declare @h int
exec sp_xml_preparedocument @h output, @x -- add a namespace binding
argument if you have namespaces
insert into table
select .... from OpenXML(@h, ...) WITH ...
exec sp_xml_removedocument @h
and then call the procedure from your client code while passing the content
of the file (in UCS-2 or UTF-16 encoding!) to the argument @x.
Best regards
Michael
"Nikola Milic" <hotmnikola@xxxxxxxxxxx> wrote in message
news:%23TIx$fBnFHA.1148@xxxxxxxxxxxxxxxxxxxxxxx
> Hi,
> What do you mean by this?
>> 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!
>
> Please explain with more details.
> Thanks in advance
> Nikola
>
> "Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx> wrote in message
> news:ORQ6G5NfFHA.576@xxxxxxxxxxxxxxxxxxxxxxx
>>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
>>>
>>>
>>
>>
>
>
.
- Next by Date: Re: Importing XML Sample file and XML Schema
- Next by thread: Re: Importing XML Sample file and XML Schema
- Index(es):
Relevant Pages
|
