sp_xml_preparedocument inside UDF

Question

sp_xml_preparedocument inside UDF

Jorge Luis Ribeiro

Valued Member

Points: 73
More actions
February 13, 2004 at 5:24 pm

#65932

Dear friends,
I need to get an information from a XML node and this content is inside of a text field called "XMLContent" from a table called "TBL_SIMULATION"
Here the script:
Create Function getProductID(@XMLContent text, @SearchFor char(4))
returns int
as
begin
declare @idoc int
declare @ProductID int
declare @XML_XPath varchar(8000)
set @XML_XPath = '//simulation/products/product[@released=' + @SearchFor + ']'
EXEC sp_xml_preparedocument @XMLContent OUTPUT, @idoc
SELECT @ProductID = product_id
FROM OPENXML (@idoc, @XML_XPath,1)
WITH (product_id int)
EXEC sp_xml_removedocument @idoc
return(@ProductID)

End
go
select prm.dbo.getProductID(XMLContent, '1999')
from prm.dbo.Tbl_Simulation
where Simulation_ID = 316
go
When I run the code above I got an error message:
Server: Msg 557, Level 16, State 2, Procedure getProductID, Line 12
Only functions and extended stored procedures can be executed from within a function.
Does anyone could help me?
Thanks in advance.
Jorge
February 16, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

netmikem SSCarpal Tunnel Points: 4001 More actions · Answer 1

Jorge,

Did you ever sort out a way around this? I'm trying to do the same thing. I've had to code it as a stored procedure but that makes my solution rather awkward.

- Mike

Ben Noblet Grasshopper Points: 15 More actions · Answer 2

I too am seeking a solution to this problem.

Does anyone have any ideas?

Thanks

Ben

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 3

Hey, add me to the list on this one too. (At two years and counting, it might be a bit of a wait...)

Philip

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 4

I did a google check on this, and it looks like Jorge posted his question on any number of sites back when. Looks like the general consensus is: you can't get there from here.

The best excuse I found was that sp_xml_prepareDocument is an internal extended stored procedure [?!?], you can't use it for this purpose, sorry, please wait for Yukon. (Yes, we're still on SQL 2000 here.)

The best advice I found was posted by Billy Yao of Microsoft, at [http://www.developmentnow.com/g/113_2004_2_0_0_422672/UserFunction.htm]. There, he says that such functions can be run, but only by explicit calls (EXE ...). Not too useful if, like me, you have a table with XML in a column, and you want to run the same process on every XML in every row.

Philip