Post reply

Large text variable

  • 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

  • April 11, 2008 at 6:50 am

    #801434

    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...

  • April 11, 2008 at 7:35 am

    #801471

    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.

  • April 12, 2008 at 4:31 pm

    #801834

    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

  • April 12, 2008 at 11:42 pm

    #801857

    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)
    Intro to Tally Tables and Functions

  • April 13, 2008 at 11:11 pm

    #801919

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

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

    :hehe:

    _____________
    Code for TallyGenerator

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

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