Pass text variable to stored proc

15 Dec 2005 4:40 PM

fleo

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

15 Dec 2005 5:44 PM

JI

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 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.

Show quote

"fleo" <f***@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

15 Dec 2005 8:09 PM

fleo

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?

Show quote

"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 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" <f***@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
>
>
>

16 Dec 2005 6:51 PM

fleo

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.

Show quote

"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 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" <f***@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
>
>
>