July 31, 2011 at 9:21 pm
I have an XML of the Form
declare @XML xml
declare @tmp1 int,@tmp2 int
declare @New_ACC_ID int
set @XML = '<Accounts>
<subAcc>
<Name>EmpID</Name>
<Value>234</Value>
</subAcc>
<subAcc>
<Name>EmpName</Name>
<Value>Test</Value>
</subAcc>
<subAcc>
<Name>EmpDOJ</Name>
<Value>3/31/2011</Value>
</subAcc>
<subAcc>
<Name>EmpDept</Name>
<Value>IT</Value>
</subAcc>
</Accounts>'
set @New_ACC_ID = '04/28/2011'
set @tmp2=3
set @XML.modify('replace value of (/Accounts/subAcc/Value[sql:variable("@tmp2")]/text())[1] with sql:variable("@New_ACC_ID")')
SELECT @XML
The above query is not modifying the vlaue od EMPDOJ...
I want to update the value of EMPDOJ from 3/31/2011(given in XML) to '04/28/2011'.How do i do this passing the positon of the node as variable ?
July 31, 2011 at 9:36 pm
Here is one way http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx
N 56°04'39.16"
E 12°55'05.25"
July 31, 2011 at 9:45 pm
This was of no help to me..Any ways thnks.
July 31, 2011 at 9:58 pm
It worked for me, but I realize I had to make it even more simple for you.
Check this out.
DECLARE @XML XML,
@NodeNumber INT,
@NewValue VARCHAR(20)
SET@XML = '<Accounts>
<subAcc>
<Name>EmpID</Name>
<Value>234</Value>
</subAcc>
<subAcc>
<Name>EmpName</Name>
<Value>Test</Value>
</subAcc>
<subAcc>
<Name>EmpDOJ</Name>
<Value>3/31/2011</Value>
</subAcc>
<subAcc>
<Name>EmpDept</Name>
<Value>IT</Value>
</subAcc>
</Accounts>'
SELECT@NewValue = '04/28/2011',
@NodeNumber = 3
SET@XML.modify('replace value of (/Accounts/subAcc[sql:variable("@NodeNumber")]/Value/text())[1] with sql:variable("@NewValue")')
SELECT@XML
N 56°04'39.16"
E 12°55'05.25"
July 31, 2011 at 10:04 pm
Thanks a lot!!!
I was trying to provide postion for the child node rather than its parent.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply