Permissions on XML execution missing?

Question

I have taken this function from https://stackoverflow.com/a/9714484/1678652

SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

INSERT INTO @result(Id)
SELECT DISTINCT r.value('.','int') as Item
FROM @xml.nodes('//root//r') AS RECORDS(r)

It takes a string and splits into a table wth an element in each row. It works when I test it in SSMS, but when I call it from the websserver (php/pdo) it returns an empty result set.

I think it must be something with XML permissions, because if I let the function add hardcoded results into the table, I do get the results back to php.

If I hardcode parameters to the function, I do not get a result to php, and I can alter the function to just return the parameters, so it is not an input issue.

I wonder if this is relevant:

http://beingoyen.blogspot.dk/2009/06/tsql-error-execute-permission-denied-on.html

Solution was simple: GRANT EXECUTE ON XML SCHEMA COLLECTION::{XMLSchema1} TO {RestrictedLoginName}

But what would this XML Schema collection be called? In SSMS i can browse for objects of the XML Schema collection-type for adding permissions to the user, but there is no objects of this type.

I have granted all permissions on the function to the user.

score 0 · Answer 1 · answered Apr 19 '13 at 13:29

I must admit I do not know what's the difference between the two functions is, but I got this function from http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings instead:

ALTER FUNCTION [dbo].[Split]
(
   @Delimiter  NVARCHAR(255),
   @List       NVARCHAR(MAX)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT val = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

But it is working as expected :-)

The actual use is my product pages are indexed in mnogosearch; I do a search, get a list of product id's, build a space-separated string of these numbers. passes this string to a sql-statement and gets a table of products back.

Permissions on XML execution missing?

1 Answers1