Pass XML between SPROCs possible in SQL 2000? 2005?

A: I can create an XML fragment using the For XML Auto clause. No problem
so far.
B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocument and the OPENXML function to create
a table from it - no problem with that.
Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.Hello Dave,

> Now I want to execute my stored procedure using the XML result of part
> A as the parameter for the sproc in part B. Is that possible? The
> only way I know how to do it right now is to return the result of A
> using ADO.NET, insert a root element (to make the XML valid), and then
> call the sproc created in part B with this xml as the parameter.
In 2005, you'd pass the XML as string to a SQLCLR stored proc. That stored
proc could parse the XML using a XmlReader, extact the command text and para
meter
values and have that proc execute other ones.
This is like the second time today that is idea has come up. Seems like it
time for some demo code... ;)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hello me, meet the real me,

> This is like the second time today that is idea has come up. Seems
> like it time for some demo code... ;)
Okay, here's the CLR-less version:
http://www.sqljunkies.com/WebLog/kt...er.aspx

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Pass text (or, best, ntext) type value to an OLE Automation function call

Hello!
Is that possible to create a user-defined function, that would take value of
type text, and pass it to the OLE Automation function call?
The SP code I use to do OLE Aut. call, is as follows:
DECLARE @.object int
DECLARE @.hr int
DECLARE @.property nvarchar(255)
DECLARE @.src nvarchar(255), @.desc nvarchar(255)
DECLARE @.return nvarchar(255)
EXEC @.hr = sp_OACreate 'Blah.Something', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
EXEC @.hr = sp_OAMethod @.object, 'myMeth', @.return OUT, 'Test string'
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
PRINT @.return
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
****
I have currently working version, that uses char type, but I'd prefer to use
text or even ntext type. It seems like, that if it is not possible to pass
the text-type value to the OLE function call, I will be furced to pass a
record ID instead, and read the text value within the external code from the
same database, what actually sounds like risky way.
Thanks,
PavilsThere is no way to declare a local n/text variable. So, the answer is 'no'.
-oj
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:ufTfn5KTFHA.548@.tk2msftngp13.phx.gbl...
> Hello!
> Is that possible to create a user-defined function, that would take value
> of type text, and pass it to the OLE Automation function call?
> The SP code I use to do OLE Aut. call, is as follows:
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.property nvarchar(255)
> DECLARE @.src nvarchar(255), @.desc nvarchar(255)
> DECLARE @.return nvarchar(255)
> EXEC @.hr = sp_OACreate 'Blah.Something', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.object, 'myMeth', @.return OUT, 'Test string'
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> PRINT @.return
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> ****
> I have currently working version, that uses char type, but I'd prefer to
> use text or even ntext type. It seems like, that if it is not possible to
> pass the text-type value to the OLE function call, I will be furced to
> pass a record ID instead, and read the text value within the external code
> from the same database, what actually sounds like risky way.
> Thanks,
> Pavils
>