CREATE TABLE [dbo].[tbl_UIConfig](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PolicyName] [nvarchar](50) NULL,
[XmlData] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT INTO [dbo].[tbl_UIConfig] VALUES('Policy1', '<main>
<mainUI>
<AppList>
<AppConfig>
<AppSettings name="notepad" path="c:\Windows\System32\notepad.exe" />
<AppDesignSettings appLogoName="notepad_534hjj45j35.png" />
</AppConfig>
<AppConfig>
<AppSettings name="wordpad" path="C:\Program Files\Windows NT\Accessories\wordpad.exe" />
<AppDesignSettings appLogoName="wordpad_nmb8mn98b908.png" />
</AppConfig>
<AppConfig>
<AppSettings name="mspaint" path="c:\Windows\System32\mspaint.exe" />
<AppDesignSettings appLogoName="mspaint_sdf9sd8f89sf.png" />
</AppConfig>
</AppList>
</mainUI>
</main>')
I have a table which contains XML column and this XML contains application details.
I want to do a functionality where if user delete image from Image Gallery then I want update this XML.
I want to update that image name to default one(defaultimg.png) in this XML. i.e. I want to update appLogoName atribute of AppDesignSettings element of every AppConfig element if appLogoName contains the same image which user has deleted.
The effect should be happen in every row of the table. and it should check every AppConfig node from AppList.
Please comment if you have any confusion.
Thanks.
What I have tried:
I have tried where only first matching record get update. But I want to update every matching item of AppList and from each table row XML.