SQL - Retreive value from nested XML based on coulmn
Posted on 2014-02-24
I have an XML string that I'm trying to do updates with in a table:
my XPath for the update join is:
--X can be 1 thru the number of participants in the call, let's say 100
and the value I need is at:
so my update statement would look something like:
set t.UserDefinedCallString = x.UserDefinedCallString
from myTable t
join theXml x on
--the value in /Route/RouteParticipantPairs/RouteParticipantPair[X]/PreferedCallX/RouteCall/RouteCall/DataSerializer/RouteCallDeserializationData/RouteCall/ToParticipantId/long
Essentially I need to loop over or break the /Route/RouteParticipantPairs into it's own table and then do an update from there.
I'm doing this on a massive data update so it can't be a one off. Any samples would be greatly appreciated.