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/