Pass text variable to stored proc

Hi,
I use a stored proc to load XML data into tables. The stored proc takes as
input the XML as a text parameter:
spLoadXML (@.XMLText text)
I have a table "tblXMLContent" with a column of text type containing the XML
text.
How do I pass the content of the column to the stored proc?
ThanksIt depends on how you are calling the stored procedure.
If you are calling it from the application (odbc,jdbc) then you should be
able to find the correlated datatype for a text column and pass it in.
If you are calling it from a stored procedure, you can not declare a
variable of type text and therefore I don't think you can unless the xml is
a parameter to that stored procedure as well. The only hack I have been able
to come up with is declare multiple varchar(8000) columns and parse the text
column 8000 bytes at a time into these variables
i.e.
Declare @.vc1 varchar(8000)
,@.vc2 varchar(8000)
Select @.vc1 = substring(text,1,8000)
,@.vc2 = substring(text,8001,8000)
from txtTable
Then I would use the exec statement that would call the proc
i.e.
exec ('up_procWithTextParam '+''''+ @.xml1+@.xml2+@.xml3+@.xml4+@.xml5+'''')
Hope this helps.
"fleo" <fleo@.discussions.microsoft.com> wrote in message
news:2D87C93A-303F-4BFF-84B7-2BE6D00677A4@.microsoft.com...
> Hi,
> I use a stored proc to load XML data into tables. The stored proc takes
> as
> input the XML as a text parameter:
> spLoadXML (@.XMLText text)
> I have a table "tblXMLContent" with a column of text type containing the
> XML
> text.
> How do I pass the content of the column to the stored proc?
> Thanks|||Thanks JI
But OMG!!!! What a waste of time. I can't believe Microsoft haven't
thought of that. I mean they didn't test their OPENXML with normal XML files
'
Do you know if the process is easier in SQL Server 2005?
"JI" wrote:

> It depends on how you are calling the stored procedure.
> If you are calling it from the application (odbc,jdbc) then you should be
> able to find the correlated datatype for a text column and pass it in.
> If you are calling it from a stored procedure, you can not declare a
> variable of type text and therefore I don't think you can unless the xml i
s
> a parameter to that stored procedure as well. The only hack I have been ab
le
> to come up with is declare multiple varchar(8000) columns and parse the te
xt
> column 8000 bytes at a time into these variables
> i.e.
> Declare @.vc1 varchar(8000)
> ,@.vc2 varchar(8000)
> Select @.vc1 = substring(text,1,8000)
> ,@.vc2 = substring(text,8001,8000)
> from txtTable
> Then I would use the exec statement that would call the proc
> i.e.
> exec ('up_procWithTextParam '+''''+ @.xml1+@.xml2+@.xml3+@.xml4+@.xml5+'''')
> Hope this helps.
> "fleo" <fleo@.discussions.microsoft.com> wrote in message
> news:2D87C93A-303F-4BFF-84B7-2BE6D00677A4@.microsoft.com...
>
>|||I use a DTS to get the text column value into a global variable and call the
sp.
Then I call the DTS from another sp.
"JI" wrote:

> It depends on how you are calling the stored procedure.
> If you are calling it from the application (odbc,jdbc) then you should be
> able to find the correlated datatype for a text column and pass it in.
> If you are calling it from a stored procedure, you can not declare a
> variable of type text and therefore I don't think you can unless the xml i
s
> a parameter to that stored procedure as well. The only hack I have been ab
le
> to come up with is declare multiple varchar(8000) columns and parse the te
xt
> column 8000 bytes at a time into these variables
> i.e.
> Declare @.vc1 varchar(8000)
> ,@.vc2 varchar(8000)
> Select @.vc1 = substring(text,1,8000)
> ,@.vc2 = substring(text,8001,8000)
> from txtTable
> Then I would use the exec statement that would call the proc
> i.e.
> exec ('up_procWithTextParam '+''''+ @.xml1+@.xml2+@.xml3+@.xml4+@.xml5+'''')
> Hope this helps.
> "fleo" <fleo@.discussions.microsoft.com> wrote in message
> news:2D87C93A-303F-4BFF-84B7-2BE6D00677A4@.microsoft.com...
>
>