Saving XML text to variable problems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Saving XML text to variable problems

Author

Topic

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-14 : 08:32:09

I have xml that I have to supplay to xml_preparedocument procedure and before that I need to load all text into variable.

So far I was unable to do this because of several reasons, the most important to be that you can't use ntext variables in SQL Server 2000.
I used this code to get xml text to ntext table field (MSDN: Brief Tutorial on Text, Ntext, and Image)


create table ##t (n ntext)
insert ##t values('SOME TEXT HERE')

EXEC readNtextFromfile 'c:\IE-PDP.xml', '##t','n', ''
SELECT n FROM ##t
DROP TABLE ##t

I see the correct text on output but I can't do anything to transmit this data to sp_xml_preparedocument becuase it expects it to be in variable and you can't use ntext variables localy.

I tried this but with no success:


PROCEDURE dbo.test
(
	@DOC  NTEXT = NULL OUTPUT
)
AS

DECLARE	@hDoc INT 

create table ##t (n ntext)
insert ##t values('SOME TEXT HERE')

EXEC readNtextFromfile 'c:\IE-PDP.xml', '##t','n', ''
SET @DOC = (SELECT n FROM ##t)
DROP TABLE ##t

EXEC sp_xml_preparedocument @hDoc OUTPUT, @DOC

RETURN

but I got complaints for line SET @DOC = (SELECT n FROM ##t). I tried to fool SQL Server this way to let me write to NTEXT variable (btw, how can I set NTEXT OUTPUT this way - documentation said that it can be used, so I don't know what is the problem with this code)

What should I do ?

The only way for now is to call procedure that will handle my xml with xml text parameter instead of file parameter from ADO.NET, but I don't want to use this method if I can do it in plain T-SQL.

Thx in advance.

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-15 : 08:26:06

hard question or you have some problems understanding the isue ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-15 : 08:34:49

>>SET @DOC = (SELECT n FROM ##t)

You will get error if the subquery returns more than one value
If you want to execute that sp against each value of the column, then you should use Cursor or While loop

Madhivanan

Failing to plan is Planning to fail

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-16 : 02:50:48

I have only one value !

readNtextFromfile will crate table with only 1 field, containing all the text from the file.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-16 : 03:19:17

>>but I got complaints for line SET @DOC = (SELECT n FROM ##t).

What is the error you got?

Madhivanan

Failing to plan is Planning to fail

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-16 : 08:04:57

Message: The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Message: The assignment operator operation cannot take a ntext data type as an argument.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-16 : 08:08:12

Why did you declare n as nText?
Cant you use Varchar datatype?
When you say "Some text here", what type of text do you mean?

Madhivanan

Failing to plan is Planning to fail

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-17 : 06:46:19

n must be nText because it should recevie whole XML which is larger then 8000 chars available for nvarchar

I use Some text here just to initiate this field.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-17 : 10:29:39

This article describes this problem and provides a solution:

http://www.awprofessional.com/articles/article.asp?p=102307&seqNum=16&rl=1

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-20 : 09:20:53

Thenk you very much
I changed my storeproc to receive just one "XML row" at the time, but this article can't harm.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources