I have XML in the form:\[code\]<Books> <Book> <Name>Lord of the Rings</Name> </Book> <Book> <Name>Harry Potter</Name> </Book> <Book> <Name>Girl with the Dragon Tattoo</Name> </Book> </Books>\[/code\]I then have a table containing two columns (Id and Name):
1: Lord of the Rings
2: Harry Potter
3: Girl with the Dragon Tattoo
Using TSQL, I want to update the XML to become:\[code\]<Books> <Book> <Name>1</Name> </Book> <Book> <Name>2</Name> </Book> <Book> <Name>3</Name> </Book> </Books>\[/code\]As in, I need to do a lookup to the 'lookup' table and update the value of the nodes to be the ID instead of the name.The only way I can think of doing this is to create a loop and to use XPath to extract the name, query the lookup table and then call modify on the XML using 'replace value of'. Is there a better way as I imagine my proposed solution will be very slow? The solution needs to be in TSQL and cannot be performed in business logic prior to calling the SP. I imagine there is a way to do this in a set based operation, but I am struggling to find it.
