Solved

SQL - Retreive value from nested XML based on coulmn

Posted on 2014-02-24
4
314 Views
Last Modified: 2014-03-02
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.
0
Comment
Question by:Kyle Abrahams
  • 3
4 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39883863
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
 
LVL 40

Author Comment

by:Kyle Abrahams
ID: 39885822
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
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 0 total points
ID: 39887134
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
 
LVL 40

Author Closing Comment

by:Kyle Abrahams
ID: 39898373
Found answer on my own.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question