Solved

SQL - Retreive value from nested XML based on coulmn

Posted on 2014-02-24
4
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 41

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…

733 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