problem with an xpath parameter to StoredProc

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.comNever mind - "exist" not "exists"
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:%23TqFrlBlGHA.4444@.TK2MSFTNGP02.phx.gbl...
> 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
>|||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@.TK2MSFTNGP02.phx.gbl...
> 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
>|||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 wil
l
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 vari
able
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@.TK2MSFTNGP02.phx.gbl...
> 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@.TK2MSFTNGP02.phx.gbl...
>