How do I replace an XML node value in SQL?

Question

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?

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Apr 24, 2015 at 13:31

har07 · Accepted Answer · 2015-04-24 14:52:41Z

1

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

answered Apr 24, 2015 at 14:52

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LCIII · Accepted Answer · 2015-04-24 13:46:46Z

0

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

answered Apr 24, 2015 at 13:46

LCIII

3,8045 gold badges32 silver badges47 bronze badges

Collectives™ on Stack Overflow

How do I replace an XML node value in SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related