CODEWITHSUNDEEP
sqlsql-serverxmlreplacexml-parsing
rahulmr
rahulmr

Reputation: 681

How do I replace an XML node value in SQL?

I have an xml value in sql field

<Menu>
  <MenuItem>
    <MENU_ID>D</MENU_ID>
    <MENU_Read>1</MENU_Read>
    <MENU_Write>1,2,3,4,5</MENU_Write>
  </MenuItem>
  <MenuItem>
    <MENU_ID>A</MENU_ID>
    <MENU_Read>1</MENU_Read>
    <MENU_Write>6,7</MENU_Write>
  </MenuItem>
  <MenuItem>
    <MENU_ID>B</MENU_ID>
    <MENU_Read>1</MENU_Read>
    <MENU_Write>8,9,10</MENU_Write>
  </MenuItem>
  <MenuItem>
    <MENU_ID>C</MENU_ID>
    <MENU_Read>1</MENU_Read>
    <MENU_Write>11,12,133</MENU_Write>
  </MenuItem>
</Menu>

I want to replace the current value of the MENU_Write node sibling to the MENU_ID node value "C", with a blank value. How do I do that?

Upvotes: 1

Views: 480

Answers (2)

har07
har07

Reputation: 89325

You can try using SQL Server's replace value of, for example :

declare @T XML = '<Menu>
  <MenuItem>
    <MENU_ID>D</MENU_ID>
    <MENU_Read>1</MENU_Read>
    <MENU_Write>1,2,3,4,5</MENU_Write>
  </MenuItem>
  <MenuItem>
    <MENU_ID>A</MENU_ID>
    <MENU_Read>1</MENU_Read>
    <MENU_Write>6,7</MENU_Write>
  </MenuItem>
  <MenuItem>
    <MENU_ID>B</MENU_ID>
    <MENU_Read>1</MENU_Read>
    <MENU_Write>8,9,10</MENU_Write>
  </MenuItem>
  <MenuItem>
    <MENU_ID>C</MENU_ID>
    <MENU_Read>1</MENU_Read>
    <MENU_Write>11,12,133</MENU_Write>
  </MenuItem>
</Menu>'

set @T.modify('replace value of (//MenuItem[MENU_ID="C"]/MENU_Write/text())[1] with ""')

SQL Fiddle

Upvotes: 1

LCIII
LCIII

Reputation: 3676

You could just use a simple UPDATE with the full text you want:

UPDATE table
SET xamlfield = 
'<Menu>
  <MenuItem>
    <MENU_ID>D</MENU_ID>
    <MENU_Read>1</MENU_Read>
    <MENU_Write>1,2,3,4,5</MENU_Write>
  </MenuItem>
  <MenuItem>
    <MENU_ID>A</MENU_ID>
    <MENU_Read>1</MENU_Read>
    <MENU_Write>6,7</MENU_Write>
  </MenuItem>
  <MenuItem>
    <MENU_ID>B</MENU_ID>
    <MENU_Read>1</MENU_Read>
    <MENU_Write>8,9,10</MENU_Write>
  </MenuItem>
  <MenuItem>
    <MENU_ID> </MENU_ID>
    <MENU_Read>1</MENU_Read>
    <MENU_Write>11,12,133</MENU_Write>
  </MenuItem>
</Menu>'
WHERE id = 123

Upvotes: 0

Related Questions