OPENXML with dynamic SQL

I am trying to use OPENXML in a dynamic query and i guess its croaking becaause it starts a new thread and so it does not know about the file handle

any workaorounds

I can paste the snippet of code if needed

Thanks

To make it clear

sp_xml_prepare_document @.hdoc OUTPUT,@.doc

SET @. Insert =

'Insert into ..... select * from OPENXML(cast(@.hdoc as nvarchar(max) ......

Seems to give me a error on open xml

can some one help ?

Thanks

|||

You can pass the xml handle into sp_executesql.

e.g.

Code Snippet

DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

create table #tmp (CustomerID varchar(10),ContactName varchar(20))

insert #tmp
exec sp_executesql N'SELECT *
FROM OPENXML (@.idoc, ''/ROOT/Customer'',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))', N'@.idoc int',@.idoc

select * from #tmp

drop table #tmp

EXEC sp_xml_removedocument @.idoc