Xml Text In Temporary Table To SP In MSSQL 2000

let's say I have this one, no problem:





Code Snippet
CREATE PROCEDURE au_info
@var text
AS
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,
@var
PRINT CAST(@var as varchar(8000))
-- Use OPENXML to provide rowset consisting of customer data.
--INSERT Customers
SELECT *
FROM OPENXML(@hDoc, N'/ROOT/Customers')
-- WITH Customers
GO









Code Snippet
EXEC au_info @var = '
<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe"
CompanyName="Company1">
<Orders CustomerID="XYZAA"
OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA"
OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'







but what if I only have the xml text in a temporary table:



Code Snippet
create table #t1 (col1 text)
insert into #t1
Values('<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe"
CompanyName="Company1">
<Orders CustomerID="XYZAA"
OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA"
OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>')





if i do this:



Code SnippetEXEC au_info @var = (Select col1 from #t1)





Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.




Code SnippetEXEC au_info (Select col1 from #t1)




Server: Msg 201, Level 16, State 3, Procedure au_info, Line 0
Procedure 'au_info' expects parameter '@var', which was not supplied.