Parsing large XML string in stored procedure

Question

Parsing large XML string in stored procedure

Ryan Torborg

SSC Enthusiast

Points: 142
More actions
October 24, 2008 at 7:51 am

#200351

I have a SQL Server 2000 data table with 1 field that contains an XML string. I am currently using an ETL tool to pass an XML string into a stored procedure for each record in my table (~100K records), which is causing some inefficiencies with going across platforms (Unix/Windows). I would like to find a way to go through my entire dataset in a self-contained procedure, but the issue I am facing is that I cannot create an NTEXT local variable to hold my XML string as I go through a SQL Server cursor. My XML is too big to fit into a varchar/nvarchar datatype, and I haven't been able to come across a way to pass the XML into the proc (which is set to receive NTEXT).
Is anyone aware of any way that I can utilize some form of "temporary" storage (cursor, temp table, etc.) to get through my process in a single stored proc?

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

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

DB_Andrew Ten Centuries Points: 1089 More actions · Answer 1

Upgrade to SQL Server 2005.

Then you will have the XML data type which gives you up to 2 gigs in a single XML instance. If you need more then this then your doing something wrong and need to go back to the white board.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

I'll second the "upgrade to 2005" opinion. That being said, once you DO, consider changing the behavior to take in a SINGLE "large" XML with all of the records in it (and not cursor through anything). Bulk-load that using the tools at hand, index it etc..., if need be accessing an external file with the XML in it.

Single threading through this in the way you're envisioning is just putting an unnecessary throttle on an aspect that has seen a lot of perf improvements in the last 2 versions (2005 and 2008 that is).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Ryan Torborg SSC Enthusiast Points: 142 More actions · Answer 3

I appreciate your feedback, but it's not that simple. We have a third-party application that does not support 2005. I understand this isn't the ideal situation, but sometimes you have to deal with the hand you're given. 😉

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Then I'd flip the issue around. Use a .NET console app to create a properly delimited file (not XML) from the "native" XML, and the bulk load the new file. That way - the bulk load gets a "clean file" and can concentrate on importing the stuff as fast as it can. In my experience - that's been the fastest way to load stuff like what you're describing.

Keep in mind that you could also use SSIS as the processing vehicle, which can then end up pointing to a SQL 2000 destination (once everything is all done etc...) Of course - this involves having 2 SQL Servers (one to process the file in which would be SQL 2005, and one with the 3rd party app which remains in 2000)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Ryan Torborg SSC Enthusiast Points: 142 More actions · Answer 5

Wonderful...I'll have to look into the possibility of putting the data in a delimited format. I'm not sure exactly how I can go about doing that, but I have a path to explore.

Thanks!