| Author |
Topic |
|
RyanT
Starting Member
6 Posts |
 Posted - 2007-07-02 : 11:32:36
|
| Hello all,I'm dealing with a table that has an NTEXT field that contains an XML string that is often times greater than 8000 characters. My problem is that I need to query the data from the table and use a cursor to execute another stored procedure for each record.Does anyone have a workaround that I could use to get the NTEXT data from the table into the call of my stored proc?Among other things, I've tried using SUBSTRING to split the ntext field into varchar fields and concatenate them in the execute statement, but that won't work:exec myProc part1+part2+part3Any suggestions would be greatly appreciated. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-07-02 : 11:57:54
|
| Insert the NTEXT value into a temporary table, and let the stored procedure you are calling get the value from the temp table.CODO ERGO SUM |
 |
|
|
RyanT
Starting Member
6 Posts |
Posted - 2007-07-02 : 12:07:07
|
| Thank you for the response.I'm not quite sure I understand how that gets around the issue. What does the temporary table offer me that the non-temporary table does not.If you could elaborate (possibly with an example), I would truly appreciate it. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-02 : 12:09:50
|
| Why use a CURSOR?Use OPENXML function.Peter LarssonHelsingborg, Sweden |
|
|
|
RyanT
Starting Member
6 Posts |
Posted - 2007-07-02 : 12:50:16
|
| I actually am using OPENXML to parse the string. My problem lies in getting the NTEXT field from my table into my stored procedure. I'm more than willing to rewrite my procedure that uses the OPENXML function (I inherited it), but I'm not sure how to get the NTEXT field into a local variable (@idoc) for use in the OPENXML function.SELECT *FROM OPENXML (@idoc, '/RESULTSCACHE/PAGE/QUESTION',1) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-07-02 : 13:44:12
|
| In SQL Server 2000, you cannot declare a local variable of type TEXT or NTEXT. A stored procedure can have a parameter of type TEXT or NTEXT, but you would have to call it from a client application, not from a SQL Server stored procedure.You can do this in SQL Server 2005 with local variable of type XML, varchar(max) or nvarchar(max).CODO ERGO SUM |
|
|
|
RyanT
Starting Member
6 Posts |
Posted - 2007-07-02 : 13:54:45
|
| Thanks, Michael. I am aware of those limitations. That's why I'm looking for other options/workarounds. Could you elaborate on your initial post recommending I use a temp table? |
|
|
|
RyanT
Starting Member
6 Posts |
Posted - 2007-07-02 : 13:56:10
|
quote:
Originally posted by RyanT
I'm not sure how to get the NTEXT field into a local variable (@idoc) for use in the OPENXML function.SELECT *FROM OPENXML (@idoc, '/RESULTSCACHE/PAGE/QUESTION',1)
I should clarify. I know I can't put the NTEXT field into the @idoc variable. I suppose I'm looking for another way to utilize the OPENXML function... |
|
|
|
|
Workaround for NTEXT field in stored procedure?