Re: problem with an xpath parameter to StoredProc

As I mentioned in another threat, please file a suggestion (or comment if
one has been filed in the meantime) at
http://connect.microsoft.com/SQLServer/Feedback.

Thanks
Michael

"Denis Ruckebusch [MSFT]" <denisruc@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:4498622b$1@xxxxxxxxxxxxxxxxxxxxx
Chris,


The behavior you're experiencing is by design. When you use
sql:variable() the way you do you construct a text node, and therefore the
exist method will always return one since the expression didn't return the
empty sequence. The contents of the variable are in no way interpreted as
an XPath expression.

Currently there is no way to parameterize the expression used by the exist
method. You can however construct the whole T-SQL statement in a string
variable and run it using sp_executesql.

Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:uTDXOEKlGHA.1204@xxxxxxxxxxxxxxxxxxxxxxx
Seems it doesn't work after all. SP compiles but when I pass in an XPath
expression, it returns all records - regardless of the XPath I pass in:

EXEC dbo.sp_ListTemplates '/o:CustomDocumentProperties[o:Category="6"]';
GO
-- returns all records, not just those which match xpath

Obviously I don't fully understand the use of sql:variable() here.

Anyone got a solution for passing a string param which is used as an
XPath expression?

Chris


"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:%23TqFrlBlGHA.4444@xxxxxxxxxxxxxxxxxxxxxxx
Hey,

I am getting a parse error on this SP expression. I'd assumed this work
work.

error is "Incorrect syntax near the keyword 'exists'."

CREATE PROCEDURE dbo.sp_ListTemplates
(
@PropXPath varchar(1024),
)
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON

SELECT fileName, docProps, version
FROM Template
WHERE docProps.exists('sql:variable("@PropXPath")') = 1

END
GO

Chris Harrington
Active Interface, Inc.
http://www.activeinterface.com








.

Loading