How to remove XML attribute [xsi:nil "true"] for a tag and insert text for the same tag

Question

I want to remove the attribute xsi:nil "true" for GeographicalLimit tag and I need to enter some dummy text for the same using SQL

Current XML:

<Header>
    <SampleTag>text</SampleTag>
    <GeographicalLimit xsi:nil="true" />
    <SampleTag> text </SampleTag>
</Header>

I need to change it to this XML:

<Header>
    <SampleTag>text</SampleTag>
    <GeographicalLimit>Sample text</GeographicalLimit>
    <SampleTag> text </SampleTag>
</Header>

I want to achieve the reverse of this Replace XML node to empty and add xsi:nil through SQL

Answer 1

I am assuming that you are using MS SQL Server.

First, your XML sample is not well-formed because there is no namespace declaration with the xsi prefix. So, we need to fix it by adding such namespace declaration to the root.

The rest is a two step process:

1. Insert text value to a nil element.
2. Delete @xsi:nil attribute in the XML element.

SQL

-- DDL and data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, report XML);
INSERT INTO @tbl (report) VALUES
(N'<Header xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <SampleTag>text</SampleTag>
    <GeographicalLimit xsi:nil="true"/>
    <SampleTag>text</SampleTag>
</Header>');
-- DDL and data population, end

-- before
SELECT * FROM @tbl;

DECLARE @var VARCHAR(20) = 'Sample text';

-- Step #1
UPDATE @tbl
SET report.modify('insert text {sql:variable("@var")} as first into (/Header/GeographicalLimit)[1]')
WHERE report.exist('/Header/GeographicalLimit[text()]') = 0;

-- after
SELECT * FROM @tbl;

-- Step #2
UPDATE @tbl
SET report.modify('delete /Header/GeographicalLimit[text()]/@xsi:nil');

-- final
SELECT * FROM @tbl;

Final Output

<Header xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <SampleTag>text</SampleTag>
  <GeographicalLimit>Sample text</GeographicalLimit>
  <SampleTag>text</SampleTag>
</Header>