0

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;
2
  • 1
    I want update DefaultValue if DataType = 1 doesn't agree with the XPath /ArrayOfField/Field[Name=sql:variable("@SearchType")] Commented Nov 25, 2022 at 11:15
  • thanks for your help but its updated only if defaultvalue is not null i need to update all default values Commented Nov 25, 2022 at 11:18

1 Answer 1

1

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:

Fields
<ArrayOfField><Field><DataType>1</DataType><DefaultValue>TODAY()</DefaultValue></Field><Field><DataType>2</DataType><DefaultValue /></Field></ArrayOfField>
<ArrayOfField><Field><DataType>1</DataType><DefaultValue>TODAY()</DefaultValue></Field><Field><DataType>3</DataType><DefaultValue /></Field></ArrayOfField>
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for your help its worked :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.