Sql-server – How to replace strings of a table from another table column

If all apples are green and all bananas are yellow a Fruit table (ID int, Name varchar(50), colour varchar(50)) would be appropriate, with your data table having a foreign key to it.

If you have yellow apples and orange bananas (yea for genetic engineering!), but only certain combinations are permitted you will need FruitRainbow(ID int, FruitID, ColourID) with the latter two as FKs to your option 2 tables above and your data table having a FK to FruitRainbow.

If any fruit can occur in any colour, and you don't want to limit those combinations in advance, your option 2 is fine.

If your query is really about resource optimisation rather than relational integrity then you'll have to decide what you want to gain and what you're willing to trade to get it. By using integer FKs instead of natural name you get a smaller disk footprint at the cost of runtime load. There are no free lunches. Pick your problem and solve it in the knowledge of the compromises it will entail.

If you just need to replace the whole piece of XML in once, then you can do a normal UPDATE, eg something like this:

UPDATE yourTable
SET yourXML = '<yourNewValidXML/>' 
WHERE rowId = 1

If you need to edit individual attributes or elements then you can use the .modify method of the XML data-type in SQL Server to update single values. Here's a simple example to get you started:

DECLARE @t TABLE ( rowId INT IDENTITY PRIMARY KEY, yourXML XML )

INSERT INTO @t ( yourXML )
VALUES ( '<Users>
    <User Name="Bob"></User>
    <User Name="Mikhail"></User>
    <User Name="John"></User>
    <User Name="Sue"></User>
</Users>' )

SELECT 'before' s, DATALENGTH(yourXML) dl, yourXML
FROM @t
WHERE rowId = 1

-- Update one attribute
UPDATE @t
SET yourXML.modify('replace value of (Users/User/@Name[.="Bob"])[1] with "wBob"')
WHERE rowId = 1

SELECT 'after' s, DATALENGTH(yourXML) dl, yourXML
FROM @t
WHERE rowId = 1

If you need more help, please post a small sample of your XML and your expected results.