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;

CodePudding user response：

You need to use different XML modify operations depending on whether the target element is empty or not (i.e.: it contains no text as opposed to being xsi:nil="true"). For example...

--
-- Setup data...
--
create table dbo.FormSchema (
  Fields xml
);
insert dbo.FormSchema (Fields) values
  (N'<ArrayOfField>
  <Field>
    <DataType>1</DataType>
    <DefaultValue>Hello, world!</DefaultValue>
  </Field>
  <Field>
    <DataType>2</DataType>
    <DefaultValue />
  </Field>
</ArrayOfField>'),
  (N'<ArrayOfField>
  <Field>
    <DataType>1</DataType>
    <DefaultValue />
  </Field>
  <Field>
    <DataType>3</DataType>
    <DefaultValue />
  </Field>
</ArrayOfField>');

--
-- Perform updates...
--
DECLARE
  @SearchType NVARCHAR(100) = N'1',
  @ReplaceWith NVARCHAR(100) = N'TODAY()';

UPDATE dbo.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")]/DefaultValue/text())[1]')=1;

UPDATE dbo.FormSchema
SET Fields.modify('
  insert text{sql:variable("@ReplaceWith")}
  into (/ArrayOfField/Field[DataType=sql:variable("@SearchType")]/DefaultValue)[1]
')
WHERE Fields.exist('(/ArrayOfField/Field[DataType=sql:variable("@SearchType")]/DefaultValue)[1]')=1
AND Fields.exist('(/ArrayOfField/Field[DataType=sql:variable("@SearchType")]/DefaultValue/text())[1]')=0;

--
-- Check results...
--
SELECT * FROM dbo.FormSchema;

Which yields the results: