0

I tried what was recomended on this thread:

Can I do a find/replace in t-sql?

UPDATE xmlTable SET xmlCol = REPLACE( CAST( xmlCol as varchar(max) ), '[search]', '[replace]')

So I tried it and it worked, sometimes. For example the following would work:

UPDATE TestSET xmlCol = REPLACE( CAST( xmlCol as varchar(max) ), 'Value', 'NewValue')

The following will NOT work:

UPDATE xmlTable SET xmlCol = REPLACE( CAST( xmlCol as varchar(max) ), 'Value', 'New Value')

(the space between new and value)

I get an error message:

Msg 9414, Level 16, State 1, Line 1 XML parsing: line 1, character 824, equal expected

I am not sure what to do, any ideas? I am trying to update any element that has attribute 'Value'

Community
  • 1
  • 1
jsefton007
  • 1
  • 2
  • Without a sample of the XML that reproduces this error plus the Value and New Value you are using, it will be less likely that someone will be able to answer the question. Is the Value's the name of an attribute or element? – Dijkgraaf Oct 15 '13 at 22:16
  • – jsefton007 Oct 15 '13 at 23:42
  • I believe is an attribute of the element – jsefton007 Oct 15 '13 at 23:43
  • That is an incomplete bit of XML. But interpreting that it should have a tag at the beginning and and a root node of some name around that I can get a piece of XML I can work with. I tried your UPDATE statement with the space, and it worked with no issues. BTW re Function is an element Name=Value is the attribute of Function (which a a sub element of the FunctionList. – Dijkgraaf Oct 16 '13 at 00:23
  • – jsefton007 Oct 16 '13 at 17:09
  • UPDATE [PhocasMaster].[dbo].[Faves] SET Payload.modify(' replace value of (/View//FunctionList/Function[@Name="Value"]/@Name)[1] with "VALUE2" ') – jsefton007 Oct 16 '13 at 17:09
  • I can't get it to update for some reason. – jsefton007 Oct 16 '13 at 17:10
  • If you keep only giving fragments of the XML that aren't valid on their own due to no root node and missing end tags, then it makes it really hard for people to help you as they then can't reproduce the error. As I suggested in my first comment and Mikael Eriksoon has suggested in his answer, you have an attribute that had Value as part of it's name. Roman Pekar is also correct in suggesting that you don't use a string replace but use xpath statments which are a lot more exact and safer to use. – Dijkgraaf Oct 16 '13 at 20:11

2 Answers2

0

You have an attribute name or element name that is called Value or has Value as part of its name.
The replace statement will replace all occurrences of Value even if it is part of the XML element names or attribute names.
The replace will leave you with an attribute name with a space and that is not allowed. So when the parser finds the space in the attribute name it complains about it and wants an equal sign instead. Your invalid XML probably looks something like this. <ElementName New Value="2"></ElementName>

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
0

Why don't you just use proper xml update with modify() function:

update xmlTable set xmlcol.modify('
    replace value of (/View/FunctionList/Function[@Name="VALUE"]/@Name)[1]
    with "New Value"
')

sql fiddle demo

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197