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 (//ns
rder[@.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.
Mikesdotnetting: