DECLARE @x XML
SELECT @x = '
<Root>
<Variables>
<Variable VariableName="V1" Value=" 1" />
<Variable VariableName="V2" Value=" 2" />
<Variable VariableName="V3" Value=" 3" />
</Variables>
</Root>'
DECLARE @var VARCHAR( 20)
DECLARE @val VARCHAR( 20)
SELECT @var = 'V3' --需要修改的Variable
SELECT @val = ' 6' --需要修改的值
--定位并且修改
SET @x.modify(' replace value of ( /Root/Variables/Variable[@VariableName=sql:variable("@var")]/@Value )[ 1] with sql:variable("@val") ')
SELECT @x
/*
<Root>
<Variables>
<Variable VariableName="V1" Value="1" />
<Variable VariableName="V2" Value="2" />
<Variable VariableName="V3" Value="6" />--此处VALUE值被修改
</Variables>
</Root>
*/
SELECT @x = '
<Root>
<Variables>
<Variable VariableName="V1" Value=" 1" />
<Variable VariableName="V2" Value=" 2" />
<Variable VariableName="V3" Value=" 3" />
</Variables>
</Root>'
DECLARE @var VARCHAR( 20)
DECLARE @val VARCHAR( 20)
SELECT @var = 'V3' --需要修改的Variable
SELECT @val = ' 6' --需要修改的值
--定位并且修改
SET @x.modify(' replace value of ( /Root/Variables/Variable[@VariableName=sql:variable("@var")]/@Value )[ 1] with sql:variable("@val") ')
SELECT @x
/*
<Root>
<Variables>
<Variable VariableName="V1" Value="1" />
<Variable VariableName="V2" Value="2" />
<Variable VariableName="V3" Value="6" />--此处VALUE值被修改
</Variables>
</Root>
*/