Writing SQL function with XQuery Parameters

Question

Following on from one of the answers in this thread; Using XQuery in Linq To SQL?

I'm trying to create a sql table function that will take parameters in order to perform queries on xml data. The function can then be consumed by a linq query.

Issue's i'm having are ;

If i take the code from the previously mentioned thread i get a "The argument 1 of the xml data type method "value" must be a string literal" error.
If i write my own function using sp_executesql then i get a "Only functions and extended stored procedures can be executed from within a function."

Here's my function;

CREATE FUNCTION fnGetOpManualXMLDataFromInt 
(   
    -- Add the parameters for the function here
    @valueXPath varchar(255), 
    @criteriaXPath varchar(255),
    @intCriteriaVal int
)
RETURNS @returntable TABLE 
(
omId int,
xmlNodes xml
)
AS
BEGIN

DECLARE @strExecute nvarchar(4000), @SingleQuote varchar(1)
SET @SingleQuote = char(39)

SET @strExecute = 'insert into @returntable select omID,
   omText.query(' + @SingleQuote + @valueXPath + @SingleQuote + ') as Value
   from dbo.htOperationsManuals
   where omText.value(' + @SingleQuote + @criteriaXPath + @SingleQuote + ', ' + @SingleQuote + 'int' + @SingleQuote + ') = ' + ltrim(str(@intCriteriaVal))

exec sp_executesql @strExecute
return
end

And here's my test for it;

DECLARE 
@valueXPath varchar(255), 
@criteriaXPath varchar(255),
@intCriteriaVal int

SET @valueXPath = '/operationsManual/sections/section/contentItem'
SET @criteriaXPath = '(/operationsManual/sections/section/contentItem/imageContentItem/imageId)[1]'
SET @intCriteriaVal = 131

select * from fnGetOpManualXMLDataFromInt(@valueXPath, @criteriaXPath, @intCriteriaVal)

Can anyone think of a way of achieving this?

EDIT: BTW, the reason i'm not doing this directly in linq is that i get a error;

  Dim imageUsage = From opmanual In dc.OperationsManuals _
                   Where opmanual.OutOfService = False _
                   And opmanual.omText.<sections>.<section>.<contentItem>.<imageContentItem>.<imageId>.Value = imageId _
                   Select opmanual

Error;

Message = "Method 'System.Collections.Generic.IEnumerable`1[System.Xml.Linq.XElement] Elements(System.Xml.Linq.XName)' has no supported translation to SQL."

score 1 · Accepted Answer · edited May 23 '17 at 11:48

1

You can't execute dynamic xpath, you should write a usual query with xpath function arguments being literal strings and embed your parameters in these strings with sql:variable(@var). Take a look at this thread for more info.

edited May 23 '17 at 11:48

Community

1
1

answered Jul 29 '09 at 12:10

Saulius Valatka

7,017
5
26
27

Ok, i can see sql:variables are probably the way to go.... I can't find many similar examples of where the sql variable holds the entire path... Only where it's being used in the traditional sense of holding a variable for comparison against hardcoded xml elements/attribute paths. Any ideas? – GordonBy Jul 29 '09 at 16:15
I'm not sure that that's even possible, never had to do it. I'd guess even if that's possible, you could achieve it with some clever dynamic quering or something. – Saulius Valatka Jul 29 '09 at 18:53
In the end on this one, i created a stored procedure... then referenced it in linq and used the resultset to join to the other linq tables i needed to use. Simples. – GordonBy Aug 25 '09 at 11:54

Writing SQL function with XQuery Parameters

1 Answers1