Text to sp_xml_preparedocument
-
I have a table which stores xml strings in a text field. I have a stored procedure to which I want to pass the key to the table, have it read the xml from the textfield and then submit it to the sp_xml_preparedocument so that I can select the data from that xml document into a temporary table and work with it.
The xml strings are often longer than 8000 characters.
What is my best way of pasing them to the sp_xml_preparedocument?
-
This was removed by the editor as SPAM
-
When you call sp_xml... in your sp, you need to declare a local variable. However, SQL 2000 does not support local variable with Text or nText data type. So the short answer is your solution does not work.
However, if you know the possible maximum length of the xml, you can split it into 8Kb chunks in your SP and pass it to another sp with text/ntext parameter in a dynamic call. E.g if your xml never execeeds 16000bytes, you can split it into 2 varchar variables and call your sp:
EXEC ('your_SP' + @XmlChunk1+ @XmlChunk2)
Another solution is to use xquery. You can check bol for details.
-
Thanks.. I may give that a try..
In the meantime, I'm just pulling the xml into a VFP mid tier object and processing it there..works well, though I would prefer to do it all in sql server..
-
I had a same situation. I used TEXT or NTEXT type of input parameter to pass xml text. Didn't find any better technique for that.
-
You can read the xml into a text variable. But you can not declare a text variable in a stored procedure. You can have a parameter of type text, so what I do is to add an optional parameter to the procedure, and use that to read the text in. You can then pass this variable to sp_xml_preparedocument. I can find you a working example if needed ...
Bert
create procedure usp_tst ( @id int, @txt text = null )
as
begin
set @@TEXTSIZE = ....
select @txt = ... from dbo.... where id = @id
exec sp_xml_preparedocument ...
end
-
To Bert De Haes:
create procedure usp_tst ( @id int, @txt text = null )
as
begin
set @@TEXTSIZE = .... ???
select @txt = ... from dbo.... where id = @id ???
-- The assignment operator operation cannot take a text data type as an argument.
exec sp_xml_preparedocument ...
end
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply