Incorrect use of the xml data type method ''modify''. A non-mutator method is expected in th

Hi,

I'm getting an error when I'm executing the follwing query in SQL Server 2005

declare @.xml xml

set @.xml = '<ROOT>

<Customer CustomerID="VINET" ContactName="Paul Henriot">

<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"

OrderDate="1996-07-04T00:00:00">

<OrderDetail ProductID="11" Quantity="12"/>

<OrderDetail ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00">

<OrderDetail ProductID="72" Quantity="3"/>

</Order>

</Customer>

</ROOT>'

if @.xml.exist('//Order[OrderID="10283"]')=0

begin

set @.xml = @.xml.modify('insert <Order OrderID="10284" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00"></Order> after //Order[@.OrderID="10283"]')

select @.xml

end

The error is

Incorrect use of the xml data type method 'modify'. A non-mutator method is expected in this context.

Please give me the answare

Regards,

Koustav

Here is a corrected, working version that simply uses SET @.x.modify:

Code Snippet

declare @.xml xml

set @.xml = '<ROOT>

<Customer CustomerID="VINET" ContactName="Paul Henriot">

<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"

OrderDate="1996-07-04T00:00:00">

<OrderDetail ProductID="11" Quantity="12"/>

<OrderDetail ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00">

<OrderDetail ProductID="72" Quantity="3"/>

</Order>

</Customer>

</ROOT>'

if @.xml.exist('//Order[OrderID="10283"]')=0

begin

set @.xml.modify('

insert <Order OrderID="10284" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"></Order> after (//Order[@.OrderID="10283"])[1]')

select @.xml

end

|||

Thanx Martin its now working fine.

I've another query How will I get an attribute's value of a particuler node?

say for example

I've @.xml as xml document and I've find out the following node using xquery

<Order OrderID="10284" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"></Order>

Now I want to check if OrderDate >= "1996-08-16" then insert some OrderDetail.

How will I read this OrderDate attribute's value.

Please help me.

Regards,

Koustav

|||

I hope the following example helps:

Code Snippet

DECLARE @.x xml;

SET @.x = '

<Orders>

<Order OrderID="10284" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"></Order>

<Order OrderID="10285" CustomerID="Someone" EmployeeID="3" OrderDate="1994-08-16T00:00:00"></Order>

</Orders>';

IF @.x.exist('

/Orders/Order[@.OrderDate >= "1996-08-16T00:00:00"]

') = 1

BEGIN

SET @.x.modify('

insert <OrderDetail Product="Something"/>

into (/Orders/Order[@.OrderDate >= "1996-08-16T00:00:00"])[1]

');

SELECT @.x

END

|||Thanx a lot Martin. It perfectly working.

|||

Continuing the above example I'm facing anaother problem.

Instead of insert xml node I'm trying to replace an attribute's value. I've written

if @.xml.exist('/Customer/Order[@.OrderID="10283"]')=0

begin

--set @.xml.modify('insert <Order OrderID="10284" CustomerID="LILAS" EmployeeID="3"

-- OrderDate="1996-08-16T00:00:00"></Order> after (//Order[@.OrderID="10283"])[1]')

set @.xml.modify('declare namespace ns="http://myOrder";

replace value of (//nsrder[@.OrderID="10283"]/OrderDetail/@.Quantity)[1] with 4')

select @.xml

end

The XML is untyped xml. There are no such schema named "myOrder" but if I'm not giving the line "declare namespace ns='http://myOrder';" its throwing error.

Now the problem is. this query is executing but the value dose not replace.

Please give me the solution.

|||

The following sample code works flawlessly for me, the Quantity attribute value is changed from 3 to 4:

Code Snippet

DECLARE @.xml xml;

SET @.xml = '<Customer>

<Order OrderID="10283">

<OrderDetail Quantity="3"/>

</Order>

</Customer>';

IF @.xml.exist('/Customer/Order[@.OrderID="10283"]') = 1

BEGIN

SET @.xml.modify('

replace value of (//Order[@.OrderID="10283"]/OrderDetail/@.Quantity)[1] with 4

');

END;

SELECT @.xml;

|||

Thank you Martin, I don't know why it was not running in my environment. But fortunatly now it aslo running in my machine. Thanks for your help.

Martine, I'm also facing another problem.

...

...

declare @.CID varchar(10)

DECLARE C1 CURSOR FOR SELECT ID FROM select customerID from customer

OPEN C1;

FETCH NEXT FROM C1 into @.CID

WHILE @.@.FETCH_STATUS = 0

BEGIN

set @.queryString = 'for $cust in //Customer where $cust/@.CustmerID = "' + @.CID + '" return $cust'

--set @.detail = @.xml.query('for $cust in //Customer where $cust/@.CustmerID = "' + @.CID + '" return $cust')

set @.detail = @.xml.query(@.queryString)

select @.detail

select @.queryString

FETCH NEXT FROM C1 into @.CID

END

....

...

Its showing an error

The argument 1 of the xml data type method "query" must be a string literal.

That means query() can't execute a string veriable. But I've to meet this type of requirment. From above XML I want to read each node then doing some processing into subnodes and then put the value into a table. So first I've to process 1st customer node and its child nodes then shift to 2nd customer and so on.

Please help me how will I proceed.

|||

You can use sql:variable as follows:

Code Snippet

DECLARE @.CID nvarchar(10);

SET @.CID = '1234';

DECLARE @.xml xml;

SET @.xml = '<root>

<Customer CustomerId="1233"/>

<Customer CustomerId="1234"/>

</root>'

SELECT @.xml.query('

for $c in root/Customer

where $c/@.CustomerId = sql:variable("@.CID")

return $c

');

|||

Hi,

I have problem in replacing set of elements with text.

declare @.dtToday datetime

, @.input xml

set @.dtToday = getdate()

-- here is my input XML

set @.input = '<TsfDesc>

<physical_to_loc>1000004</physical_to_loc>

<to_loc_type>S</to_loc_type>

<pick_not_before_date>

<year>2001</year>

<month>03</month>

<day>09</day>

<hour>00</hour>

<minute>00</minute>

<second>00</second>

</pick_not_before_date>

<to_loc_type>S</to_loc_type>

</TsfDesc>'

I would like to get the output as shown below

'<TsfDesc>

<physical_to_loc>1000004</physical_to_loc>

<to_loc_type>S</to_loc_type>

<pick_not_before_date>03/09/2001 00:00:00

</pick_not_before_date>

<to_loc_type>S</to_loc_type>

</TsfDesc>'

would you please help me?

Thanks in advance.

Meeran.

|||

Here is a solution using several steps:

Code Snippet

DECLARE @.input xml;

set @.input = '<TsfDesc>

<physical_to_loc>1000004</physical_to_loc>

<to_loc_type>S</to_loc_type>

<pick_not_before_date>

<year>2001</year>

<month>03</month>

<day>09</day>

<hour>00</hour>

<minute>00</minute>

<second>00</second>

</pick_not_before_date>

<to_loc_type>S</to_loc_type>

</TsfDesc>';

DECLARE @.date nvarchar(19);

SET @.date = @.input.value('

concat((TsfDesc/pick_not_before_date/day)[1], "/", (TsfDesc/pick_not_before_date/month)[1], "/", (TsfDesc/pick_not_before_date/year)[1], " ", (TsfDesc/pick_not_before_date/hour)[1], ":", (TsfDesc/pick_not_before_date/minute)[1], ":", (TsfDesc/pick_not_before_date/second)[1])

', 'nvarchar(19)');

SET @.input.modify('

delete TsfDesc/pick_not_before_date/node()

')

SET @.input.modify('

insert text{sql:variable("@.date")}

into (TsfDesc/pick_not_before_date)[1]

');

SELECT @.input;

|||

Thanks a lot Martin, for your prompt response.

Incorrect syntax with Declare

Good morning
I hope this will be a simple one, but I am trying to write a view to query my SQL server database, setting a variable using "declare". The code runs fine and returns a number of records, but when I try to save it, it comes up with "Incorrect syntax near the keyword DECLARE", and will not save. I am a bit of a novice when it comes to SQL, but I don't understand why it runs, but won't save. Here is my variable:
DECLARE @.ANCHORDATE AS DATETIME
SET @.ANCHORDATE = CASE WHEN datepart(mm, getdate()) BETWEEN 4 AND 9 THEN dateadd(mm, 3, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) ELSE dateadd(mm, - 9,
DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) END

Thanks for any help.I am not 100% sure but you can try not to use DECLARE and try and save it. Let me know if it helps.|||I have replaced all the instances of the variable with the actual definition, and the view runs and saves fine. I was hoping to be able to keep it simple by not having to type in the variable definition each time. I don't understand why it can run, but saving it returns an error.

Incorrect syntax near the keyword 'OR'.

Hi,
I have a stored procedure
CREATE PROCEDURE dbo.Retrieve
(
@.SEARCH_STRING nvarchar(200),
@.COUNT int
)
AS
DECLARE @.STRING_COUNT varchar(3)
DECLARE @.SQL varchar(1000)
SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE ' +
@.SEARCH_STRING + '% )'
EXEC (@.SQL)
The stored procedure is created successfully.
But I get the error when I try to use it: (Retrieve '',10)
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OR'.
Thanks
KiranAnswered in .programming. Please don't multi-post.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Kiran" <Kiran@.nospam.net> wrote in message
news:O9oiPrX#EHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a stored procedure
> CREATE PROCEDURE dbo.Retrieve
> (
> @.SEARCH_STRING nvarchar(200),
> @.COUNT int
> )
> AS
> DECLARE @.STRING_COUNT varchar(3)
> DECLARE @.SQL varchar(1000)
>
> SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
> SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
> WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE '
+
> @.SEARCH_STRING + '% )'
> EXEC (@.SQL)
> The stored procedure is created successfully.
> But I get the error when I try to use it: (Retrieve '',10)
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'OR'.
>
> Thanks
> Kiran
>sql

Incorrect syntax near the keyword Declare.

Dear Group,

I am trying to create a view and keep getting the Incorrect syntax near the
keyword 'Declare'" error.

Here is the code I am writing.

Create view fixed_airs (sid, fad_a2, fad_a3) as
Declare @.sid int,
@.fad_a2 int,
@.fad_a3 int
select @.sid=cast(substring(subject_id,1,8)as int) ,
@.fad_a2 =cast (substring(fad_2_4,1,1) as int),
@.fad_a3=cast(substring(fad_2_4,2,1) as int)
from parentpacket.

Thanks for the help in advance.

Jeff MagouirkJeff Magouirk wrote:
> Dear Group,
> I am trying to create a view and keep getting the Incorrect syntax near the
> keyword 'Declare'" error.
> Here is the code I am writing.
> Create view fixed_airs (sid, fad_a2, fad_a3) as
> Declare @.sid int,
> @.fad_a2 int,
> @.fad_a3 int
> select @.sid=cast(substring(subject_id,1,8)as int) ,
> @.fad_a2 =cast (substring(fad_2_4,1,1) as int),
> @.fad_a3=cast(substring(fad_2_4,2,1) as int)
> from parentpacket.
> Thanks for the help in advance.
> Jeff Magouirk

You keep getting syntax errors because you're using illegal syntax in
your CREATE VIEW statement. :D You can't use DECLARE, nor can you pass
in variables to a view. Check Books Online for proper syntax. But, in a
nutshell, you can only use a SELECT statement in a view.

Zachsql

Incorrect syntax near comparison on parameter value

Error: Incorrect syntax near '@.today'.

DECLARE @.today CHAR(8)

SET @.today = CONVERT(CHAR(8), GETDATE(), 112)

SELECT c.name,

c.cust,

m.num,

ph.Entered

FROM Master m (NOLOCK)

INNER JOIN dbo.hisy ph ON ph.number = m.num

INNER JOIN dbo.Cust c ON c.Cust = m.Cust

WHERE m.customer IN (0000162, 0000164)

AND ph.Entered IS NOT NULL

AND CONVERT(CHAR(8), ph.Entered, 112) = @.today <-Error is here

It passes syntax checking for me. Is this the entire batch?

If I add a BEGIN before the statement, I get the syntax error two. Sometimes SQL Server error messages are really misleading, though it is technically correct.

|||thanks I was missign the END, that was it.

Include File Concept

Hi All
I wanted to check if there is any feature like the INCLUDE file concept in ASP.
I have set of variables that I need to declare in each of the Stored Procedures by default, so that I can maintain this list in single place, dont have to worry about changing the list for every SP in the database.
Does SQL Server provide any such feature.
If not, is this planned in YUKON?You could try creating a template for use in Query Analyzer. You can modify
one of the existing procedure and save it as another name. See BOL:Using
Templates in Query Analyzer for more information.
"Prasanna" <pprabhu@.pbs.solutionsiq.com> wrote in message
news:FD16045E-A2F1-4534-88A2-65DD471DA1EE@.microsoft.com...
> Hi All
> I wanted to check if there is any feature like the INCLUDE file concept in
ASP.
> I have set of variables that I need to declare in each of the Stored
Procedures by default, so that I can maintain this list in single place,
dont have to worry about changing the list for every SP in the database.
> Does SQL Server provide any such feature.
> If not, is this planned in YUKON?|||Hello,
Templates are boilerplate files containing SQL scripts that help you create
objects in the database. There are however, unable to help maintain the set
of variables in a single place.
Currently, SQL Server does not provide this feature you require. As I
understand, SQL Server Yukon will provide .NET Programming Features, but
because Yukon is not a released version of SQL Server, we are unable to
guarantee it will have the feature like the INCLUDE file concept in ASP.
Thanks for understanding.
For additional information regarding .NET programming Features, please
refer to the following article:
SQL Server Yukon: .NET Programming Features
http://server1.msn.co.in/sp03/teched/pop5.html
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.
Thanks for using MSDN newsgroup
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.