I have a UDF for processing XML. However it is giving error 'Only functions and extended stored procedures can be executed from within a function.' Please help.

Question

Post reply

I have a UDF for processing XML. However it is giving error 'Only functions and extended stored procedures can be executed from within a function.' Please help.

priyanka.scholar

Valued Member

Points: 50
More actions
August 28, 2008 at 5:03 am

#191932

I have a UDF for processing XML with sp_xml_preparedocument in it. However it is giving me a error

Viewing 10 posts - 1 through 9 (of 9 total)

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

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

Sorry, but I can't see your question. Can you re-post?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

John H Marsh SSChampion Points: 11671 More actions · Answer 2

Hello Priyanka,

You usually get that error because UDFs cannot perform actions that modify the database state or execute SPs.

I guess then that it is the sp_xml_preparedocument that is the root cause.

I assume this code works Okay as a regular SP?

Regards,

John

www.sql.lu
SQL Server Luxembourg User Group

priyanka.scholar Valued Member Points: 50 More actions · Answer 3

Hi John,

Yes the code works as a regular SP.

Seraching on the net i found out that 'sp_xml_preparedocument' is an extended Stored Proc..

So ideally I shd be able to execute it within the UDF. Or am I missing sumthing.

Thanks,

Priyanka

John H Marsh SSChampion Points: 11671 More actions · Answer 4

Hello Priyanka,

Sorry, you are right it is an Extended Stored Procedure. It may then be because it is nondeterministic.

Have you considered using the Query() method on an XML variable instead of OPENXML?

Regards,

John

www.sql.lu
SQL Server Luxembourg User Group

John H Marsh SSChampion Points: 11671 More actions · Answer 5

Hello Priyanka,

Mr. McKinney has just pointed me to this article: http://hosteddocs.ittoolbox.com/KG051507.pdf

It explicitly states that you cannot use sp_xml_preparedocument from inside a UDF.

If you need it to be a UDF then it looks like the XML Data Type's Query method would be teh way ahead.

Regards,

John

www.sql.lu
SQL Server Luxembourg User Group

priyanka.scholar Valued Member Points: 50 More actions · Answer 6

Hello John,

Thanks a lot for your suggestion..

I ll try using Query() method but not sure whether it will meet my requirements.

Thanks again.

Priyanka

priyanka.scholar Valued Member Points: 50 More actions · Answer 7

Hello John,

Thanks for ur suggestion.

Now i have the following UDF

CREATE FUNCTION dbo.fnIds

(

@ParmXML XML

)

RETURNS @Id table

(

TempId INT IDENTITY(1,1)

,RowId NVARCHAR(15)

)

AS

BEGIN

INSERT INTO @Id

SELECT Tbl.Col.value('.','nvarchar(15)')

FROM @ParmXML.nodes('/root/r/i/text()') Tbl(Col)

RETURN

END

GO

Now if i use this UDF in a JOIN in another SP and try to execute it, I get the following error message:

'INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.'

I have tried

SET ARITHABORT ON

GO as well but no luck.

Any help would be appreciated.

Thanks,

Priyanka

John H Marsh SSChampion Points: 11671 More actions · Answer 8

Hello Priyanka,

Just to double check that you are Setting ARITHABORT to ON inside the SP's logic, rather than in the script that creates the SP? As I understand, you get the error when executing the SP, rather than the create SP script - correct?

BTW - Does the SP happen to access any data in a Linked Server?

Regards,

John

www.sql.lu
SQL Server Luxembourg User Group

priyanka.scholar Valued Member Points: 50 More actions · Answer 9

Hello John,

Actually i was setting SET ARITHABORT ON before creating the UDF not in SP logic..

I did dat now and it is working perfectly..

Thanks a lot John.. 🙂