SQL - Retreive value from nested XML based on coulmn

Hi All,

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
/Route/RouteParticipantPairs/RouteParticipantPair[X]/PreferedCallX/RouteCall/RouteCall/DataSerializer/RouteCallDeserializationData/RouteCall/ToParticipantId/long

and the value I need is at:
/Route/RouteParticipantPairs/RouteParticipantPair[X]/PreferedCallX/RouteCall/RouteCall/DataSerializer/RouteCallDeserializationData/RouteCall/UserDefinedCallString/string

so my update statement would look something like:

update t
set t.UserDefinedCallString = x.UserDefinedCallString
from myTable t
join theXml x on
t.ParticipantId =
--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.
LVL 41
Kyle AbrahamsSenior .Net DeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperAuthor Commented:
I was able to get it:

 ;with XMLNAMESPACES (
 'http://www.w3.org/2001/XMLSchema' as MI
  )

select sp.*, XMLNUM.NUM from Participants sp
left outer join (
 select distinct
 cast(c.query(('data(./MI:UserDefinedCallString/string)')) as varchar(1000)) as Num,
 cast(c.query(('data(./MI:ToParticipantId/long)')) as varchar(1000)) as ParticipantId,
conference_id
   from
    (
            select cast(ConnectionScript as xml) t, id from    MyTable s
    ) A1(x, conference_id)
      cross apply x.nodes('/Route/RouteParticipantPairs/RouteParticipantPair/PreferedCallX/RouteCall/MI:RouteCall/MI:DataSerializer/RouteCallDeserializationData/MI:RouteCall') XMLTABLE(c)    
) XMLNUM      on sp.ParticipantID = XMLNUM.participantID and sp.scheduledcallid = XMLNUM.conference_id
0
 
SharathData EngineerCommented:
Can you provide more details? Where do you have XML string? In a table on database or in file in your local directory?
What are the columns in theXml table? Do you have XML type column in this table and want to extact string value from the XML column?
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Hi Sharath,

Thanks for responding.

The XML string is in a column on my database that's stored as ntext.  I copy this into a temporary table and I'm trying to do updates on my temporary table.

eg:

select meetingID, ParticipantId, cast(null as varchar(1000)) UserDefinedCallString,  XmlString into #temp from myTable


The results of this can look like:
MeetingId   ParticipantId   UserdefinedCallString      XMLString
1                             2                      NULL                                              A
1                             1                      NULL                                              A
1                             3                      NULL                                              A
2                             1                      NULL                                              B
2                             2                      NULL                                              B


The XML string will be the same for each meeting.

Within the XML string at the XPATH:
/Route/RouteParticipantPairs/RouteParticipantPair[X]/PreferedCallX/RouteCall/RouteCall/DataSerializer/RouteCallDeserializationData/RouteCall/ToParticipantId/long

is the Participant ID of that conference.  I say [X] because in the case of meeting 1 you'll have 1-3 for X.  But order of the XML isn't guaranteed as far as I can tell so [1] in XML could correspond to participantID 3.

So I need to update my temp table with the userdefinedcallstring in the XML based on the participantID (which maps in the XML to the table).  I'm willing to do a cursor or a loop if need be, but didn't know if there was a way to handle this set based.

Let me know if that helps.

Attached is A.xml which corresponds to conferenceID 1.  Only the numbers have been obfuscated.
A.xml
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Found answer on my own.
0
All Courses

From novice to tech pro — start learning today.