Getting text data into a Stored Procedure

[mattdp]

Hi,

I've got some XML which exists as a text variable in a temp table.

I need to pass this XML into sp_xml_preparedocument so I can rebuild a table out of it. But I can't figure out the syntax.

If I try doing this:


declare @idoc int
exec sp_xml_preparedocument @idoc output, (select XmlResult from #cache)


I get an error, with or without the brackets round the select statement.

The temp table is created using an SP, but I can't call that directly either. This:


declare @idoc int
exec sp_xml_preparedocument @idoc output, exec Search$GetCache @searchID


Also throws an error.

I can't put it into a local variable because they can't be of type text. I can't pass it into the SP somewhere as it's being generated on the fly.

How can I get my xml into sp_xml_preparedocument?

Cheers,
Matt

 

[Qik3Coder]

Have you tried changing

select XmlResult from #cache

to

select Top 1 XmlResult from #cache

-Sometimes the answer to your question is the hack that works

 

[gmmastros]

What version of SQL Server are you using?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom