I want to update empty XML variable in MSSQL i try every solutions like replace, outer apply , modify but nothing working please help me find a solutions
<ArrayOfField xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Field>
<FieldType>1</FieldType>
<DataType>1</DataType>
<Label>department</Label>
<Name>department</Name>
<Description />
<Min />
<Max />
<DefaultValue />
<Required>0</Required>
<SelectItems />
<Formula />
<DataSourceUID xsi:nil="true" />
<TrueText />
<FalseText />
<UnconfirmtyRule xsi:nil="true" />
<UnconfirmtyRequired xsi:nil="true" />
<UnconfirmityFormUID xsi:nil="true" />
<ConfirmationStatus xsi:nil="true" />
<ConfirmationUserBasedType xsi:nil="true" />
<ConfirmationRightUsers />
<Newline>false</Newline>
<ColumnWith>12</ColumnWith>
<Offset>0</Offset>
</Field>
<Field>
<FieldType>1</FieldType>
<DataType>4</DataType>
<Label>Test</Label>
<Name>test</Name>
<Description />
<Min />
<Max />
<DefaultValue />
<Required>0</Required>
<SelectItems />
<Formula />
<TrueText />
<FalseText />
<UnconfirmtyRule xsi:nil="true" />
<UnconfirmtyRequired xsi:nil="true" />
<UnconfirmityFormUID xsi:nil="true" />
<ConfirmationStatus xsi:nil="true" />
<ConfirmationUserBasedType xsi:nil="true" />
<ConfirmationRightUsers />
<Newline>false</Newline>
<ColumnWith>12</ColumnWith>
<Offset>0</Offset>
</Field>
</ArrayOfField>
I want update DefaultValue if DataType = 1
New DefaultValue like <DefaultValue>TODAY()<DefaultValue/>
I try this code but not update anything what is my mistake?
DECLARE @SearchType NVARCHAR(100)=N'1';
DECLARE @ReplaceWith NVARCHAR(100)=N'TODAY()';
UPDATE FormSchema
SET Fields.modify('replace value of
(/ArrayOfField
/Field[DataType=sql:variable("@SearchType")]
/DefaultValue/text())[1]
with sql:variable("@ReplaceWith")')
WHERE Fields.exist('/ArrayOfField
/Field[DataType=sql:variable("@SearchType")]')=1;
/ArrayOfField/Field[Name=sql:variable("@SearchType")]