July 28, 2015 at 8:33 pm
**I have a XML variable with the following data:**
DECLARE @CropXML xml
set @CropXML =
' <Crop Id="8" Name="Apricot" Type="1">
<Varieties>
<Variety Id="1" Name="General" PlantsPerHa="0" FertApplicationMethod="1" ImageId="44" PlantsPerHaUnit="0">
<SoilTypes>
<SoilType Id="1" Name="General">
<YieldGoals>
<YieldGoal Id="1" Name="10" YieldGoalUnits="0">
<Stages UnitId="1" P_Cbo="0" K_Cbo="0" Ca_Cbo="0" Mg_Cbo="0" S_Cbo="0">
<Stage Id="1" StageName="Total Nutrient Uptake" Duration="180" ApplicationInterval="180" UnitId="1" N_Val="39" P_Val="5.24" K_Val="75.52" Ca_Val="57.39" Mg_Val="10.86" />
</Stages>
</YieldGoal>
<YieldGoal Id="2" Name="30" YieldGoalUnits="0">
<Stages UnitId="1" P_Cbo="0" K_Cbo="0" Ca_Cbo="0" Mg_Cbo="0" S_Cbo="0">
<Stage Id="1" StageName="Total Nutrient Uptake" Duration="365" ApplicationInterval="365" UnitId="1" N_Val="93" P_Val="13.09" K_Val="179.25" Ca_Val="124.82" Mg_Val="28.97" />
</Stages>
</YieldGoal>
<YieldGoal Id="3" Name="40" YieldGoalUnits="0">
<Stages UnitId="1" P_Cbo="0" K_Cbo="0" Ca_Cbo="0" Mg_Cbo="0" S_Cbo="0">
<Stage Id="1" StageName="Total Nutrient Uptake" Duration="365" ApplicationInterval="365" UnitId="1" N_Val="120" P_Val="17.02" K_Val="230.71" Ca_Val="158.54" Mg_Val="38.02" />
</Stages>
</YieldGoal>
<YieldGoal Id="4" YieldGoalUnits="0" Name="20.00">
<Stages UnitId="1" P_Cbo="2" K_Cbo="1" Ca_Cbo="1" Mg_Cbo="1" S_Cbo="0">
<Stage Id="1" StageName="Total Nutrient Uptake" Duration="124" ApplicationInterval="124" N_Val="90.000" P_Val="32.000" K_Val="154.00000" Ca_Val="126.99999" Mg_Val="33.00000" />
<Stage Id="2" StageName="Bud swelling" Duration="11" ApplicationInterval="11" N_Val="3.400" P_Val="4.900" K_Val="3.200" Ca_Val="0.000" Mg_Val="0.000" />
<Stage Id="3" StageName="Leaves expansion and flowering" Duration="13" ApplicationInterval="13" N_Val="10.200" P_Val="7.400" K_Val="19.300" Ca_Val="14.400" Mg_Val="7.700" />
<Stage Id="4" StageName="Fruit set" Duration="23" ApplicationInterval="23" N_Val="20.300" P_Val="7.400" K_Val="25.700" Ca_Val="36.300" Mg_Val="7.700" />
<Stage Id="5" StageName="Beginning of fruit maturing" Duration="13" ApplicationInterval="13" N_Val="18.300" P_Val="4.900" K_Val="32.100" Ca_Val="44.400" Mg_Val="12.800" />
<Stage Id="6" StageName="Maturing" Duration="41" ApplicationInterval="41" N_Val="20.300" P_Val="2.500" K_Val="38.500" Ca_Val="43.800" Mg_Val="7.700" />
<Stage Id="7" StageName="Fruit ripe for consumption" Duration="9" ApplicationInterval="9" N_Val="20.300" P_Val="2.500" K_Val="32.100" Ca_Val="13.800" Mg_Val="3.800" />
<Stage Id="8" StageName="Post harvest" N_Val="4.100" P_Val="2.500" K_Val="3.200" Ca_Val="6.300" Mg_Val="2.600" Duration="14" ApplicationInterval="14" />
</Stages>
</YieldGoal>
<YieldGoal Id="5" Name="25.00" YieldGoalUnits="0">
<Stages UnitId="1" P_Cbo="0" K_Cbo="0" Ca_Cbo="0" Mg_Cbo="0" S_Cbo="0">
<Stage Id="1" StageName="Total Nutrient Uptake" Duration="365" ApplicationInterval="365" N_Val="112.000" P_Val="17.45962" K_Val="153.52697" Ca_Val="107.60402" Mg_Val="24.74351" />
</Stages>
</YieldGoal>
</YieldGoals>
</SoilType>
</SoilTypes>
</Variety>
</Varieties>
</Crop> '
I try to delete the entire node child
<Stage Id="1" StageName="Total Nutrient Uptake" Duration="124" ApplicationInterval="124" N_Val="90.000" P_Val="32.000" K_Val="154.00000" Ca_Val="126.99999" Mg_Val="33.00000" />
under the following node keys
@cropid =8
@varietyid= 1
@yieldid=4
@StageId =1
declare @cropid int,@varietyid int,@yieldid int,@StageId int,
@cropXML XML,@CropXmlafter XML
set @cropid =8
set @varietyid= 1
set @yieldid=4
set @StageId =1
select @cropXML
set @cropXML.modify('delete /Crop/Varieties/Variety[Id=sql:variable("@varietyid")]
/SoilTypes/SoilType/YieldGoals/YieldGoal[Id=sql:variable("@yieldid")]
/Stages/Stage[Id=sql:variable("@StageId")][1]')
select @cropXML
I try ALSO TO REMOVE THE [1] IN THE LAST xquery sentence , but nothing .
Its works,But nothing has been changed, why
I try the following code but nothing:
SET @cropXML = (SELECT DataXml from dbo.Crop
WHERE DataXml.value('(Crop/Id)[1]','int')= @cropid)
SET @cropXML.modify('
delete
if ((/Crop/Varieties/Variety/Id=sql:variable("@varietyid")) and
(/Crop/Variety/SoilTypes/SoilType/YieldGoals/YieldGoal/Id=sql:variable("@yieldid")) and
(/Crop/Variety/SoilTypes/SoilType/YieldGoals/YieldGoal/Stages/Stage/Id=sql:variable("@StageId")))
then (/Crop/Variety/SoilTypes/SoilType/YieldGoals/YieldGoal/Stages/Stage[1])
else()
')
by setting the XML variable from its original col table
I will appreciate any idea,Thank's
July 28, 2015 at 10:14 pm
Quick fix adding the "@" to the attribute names;-)
😎
set@cropXML.modify('
delete (/Crop/Varieties/Variety[@Id=sql:variable("@varietyid")]/SoilTypes/SoilType/YieldGoals/YieldGoal[@Id=sql:variable("@yieldid")]/Stages/Stage[@Id=sql:variable("@StageId")])[1]
');
July 30, 2015 at 1:22 am
thank you. so quick so simple
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply