Thanks Jacob for your quick reply.

I have checked that query is running perfectly fine and it is retrieving the data within 3 seconds but when I am using the function (XML data as input parameter) to retrieve the data, it is taking around 1 minute (I used same query in the function to parse the XML).

For Example below query returns the data within 3 seconds:

declare @XMLdata XML

SET @XMLdata =

'<XmlStart>..</XmlEnd>'

; WITH XMLNAMESPACES ( DEFAULT

'http://abc.com',

'http://hd.com' AS a

'http://www.w3.org/2001/XMLSchema-instance' AS i)

select

T.C.value('a:data1[1]', 'smallint') AS Column1

, T.C.value('(a:data2/a:data3)[1]', 'nvarchar(12)') AS Column2

from @XMLdata.nodes('/XmlStart/Data/a:col1/a:col1Data') T ( C )

But, when I use the same query in function & try to retrieve the data as below:

declare @XMLdata XML

SET @XMLdata =

'<XmlStart>..</XmlEnd>'

select * from hdFunction(@XMLdata)

It is taking 1 minute or more than it.

I need to use the function because lots of stored procedures are using the same XML. So, parsing code needs to be at one place. If is there any change in the business rule, it is easy to change at once place only.

Please guide me to slove the issue.