?
Solved

SQL - Retreive value from nested XML based on coulmn

Posted on 2014-02-24
4
Medium Priority
?
325 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

752 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