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.