Large text variable

Question

Large text variable

david.buncle

Right there with Babe

Points: 774
More actions
April 11, 2008 at 5:25 am

#71475

Hi.
I have a problem where I'm storing a very large xml sting in a field of type ntext in the database. A problem arises when I try and extract the values from the xml in the table... I can't pass all the XML text to the sp_xml_preparedocument function because I don't have a variable big enough to store the data. This is because I can't use a variable of type 'text' as a local variable!! Is there a way to pass a string of size 20,000 + chars to this function?
The T-SQL is seen below where I'd like to store all the xml text in the variable @sXML.
DECLARE@iDoc INT
DECLARE@sXML text
SET @sXML = (SELECT event_xml from xml_ureg where event_id = x)
EXEC sp_xml_preparedocument @iDoc OUTPUT, @sXML, namespace
Thanks,
Dave

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ronald Nijland SSC Enthusiast Points: 157 More actions · Answer 1

I'm not sure i get you correctly but why store a xml of 20000+ chars in the database? Just curious...

Ontopic.. Maybe you could pass the id to the xml parser and in the function you do you thing with the id in stead of the string...

david.buncle Right there with Babe Points: 774 More actions · Answer 2

It's actually xml data being transferred from an oracle database stored in a BLOBs. The application that read the data would decompress and then extract the data from the XML within the BLOB. I'm brining the data into an SQL database where I'm trying to extract the data and then store it in normalised tables.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

david.buncle (4/11/2008)
Hi.
I have a problem where I'm storing a very large xml sting in a field of type ntext in the database. A problem arises when I try and extract the values from the xml in the table... I can't pass all the XML text to the sp_xml_preparedocument function because I don't have a variable big enough to store the data.

Actually you can.

Just declare your variable as a parameter of SP.

And.

Parse XML and process its data at the moment when it's supplied to database.

Don't store XML in database. It's just pile of rubbish you have no way to use (as you already figured out).

SQL Server is RDMS, not web-server, it has nothing to do with XML.

If you need to store XML for history or audit purposes reserve a folder on you server and drop copies of processed XML's in there. File name + date is everything you need to store in database.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Sergiy (4/12/2008)
Actually you can.
Just declare your variable as a parameter of SP.

Never too late to say thanks... you showed me that cool trick a couple of years ago. Thanks Sergiy!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Well, now I suppose to mention whom I've got it from?

But I don't remember!!! :w00t:

:hehe:

_____________
Code for TallyGenerator