0

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
Dale K
  • 25,246
  • 15
  • 42
  • 71
AMDI
  • 895
  • 2
  • 17
  • 40
  • 1
    https://dbfiddle.uk/lDmx3DeM `insert text{sql:variable("@RuleName")} into (/Lists/List[@ListName="Rules"]/Items/Item)[1]` – Charlieface Jul 18 '23 at 20:27

0 Answers0