SQL Server - How to use sp_xml_preparedocument proc in UDF?

Hi,

I need to write a function which will parase the Extract the information from xml document.
The xml document is stored in another table As nText.

I tried to use the sp_xml_preparedocument in my function. But It shows Error Message like "Only Extened stored Procedures can be used inside a function".

The Proc "sp_xml_preparedocument " is aslo an extended proc. !!

How can we solve this?

Thank you
Nagaraj

where sp_xml_preparedocument is an extended stored proc. Have you looked at the documentation to ensure that your syntax is correct?

http://msdn2.microsoft.com/en-us/library/ms187367.aspx

Thanks for reply

Yes, I have checked the sytax. Its not showing any error while parsing the syntax.
It is complied with out errors.

Code Here :

Create function parseMyXml(@input int,@node varchar(100))
returns varchar(1000)
As
begin
declare @handle int
Declare @Result Varchar(1000)
set @Result = ''
declare @xmldoc varchar(1000)
set @xmldoc = (select Content_Html from content Where Content_Id = @input)
Exec sp_xml_preparedocument @handle OUTPUT,@xmldoc
set @node = '/root/User/' + Convert(varchar(100),@node)
set @Result = (SELECT sDescription FROM openxml(@handle,@node)
with (
 sDescription varchar(100) 'text()'
        )
)
Exec sp_xml_removedocument @handle
return @Result
end

While Trying to Execute the function, It shows the following error.
select dbo.parseMyXml(10234,'City') As scity

Erroe Message :

Msg 557, Level 16, State 2, Line 1
Only functions and extended stored procedures can be executed from within a function.


Thanks
Nagaraj

http://msdn2.microsoft.com/en-us/library/aa175782(SQL.80).aspx

Read carefully, especially the part near the bottom.