Solved

SQL - Retreive value from nested XML based on coulmn

Posted on 2014-02-24
4
307 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
XSL FO - text wrapping and empty spaces 1 26
Using OPENQUERY in a SELECT statement 6 36
tempdb latch contention 12 48
Trying to identify overlapping date ranges 5 15
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now