I am having the following XML
<Lists xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<List ListName="Rules" StructureType="1">
<Items>
<Item Level="1" />
</Items>
</List>
<List ListName="Reasons" StructureType="1">
<Items>
<Item Level="1"> Not interested</Item>
</Items>
</List>
</Lists>
I want to add value of inner text to <Item>
as shown below
<Lists xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<List ListName="Rules" StructureType="1">
<Items>
<Item Level="1">Rule100</Item>
</Items>
</List>
<List ListName="Reasons" StructureType="1">
<Items>
<Item Level="1"> Not interested</Item>
</Items>
</List>
</Lists>
I have tried the following snippets but its not adding to inner text. Please suggest any other solution. I tried with delete and insert but it not modifying properly
declare @xmlBlob xml
set @xmlBlob=
<Lists xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<List ListName="Rules" StructureType="1">
<Items>
<Item Level="1" />
</Items>
</List>
<List ListName="Reasons" StructureType="1">
<Items>
<Item Level="1"> Not interested</Item>
</Items>
</List>
</Lists>
DECLARE @RuleName AS NVARCHAR(10) = 'Rule100';
SET @xmlBlob.modify('replace value of (/Lists/List[@ListName="Rules"]/Items/Item/text())[1]
with sql:variable("@RuleName")')
SELECT @xmlBlob