brownhqueen
New Member
First off, my tables look like this..\[code\]Checkpoint_Data---------------Checkpoint_IdSequence_NumberCheckpoint_DataCheckpoint----------Checkpoint_IdLocationTransaction_IdTransaction-----------Transaction_IdNameTime\[/code\]Checkpoint_data contains broken up XML, and the sequence number determines how it fits togetherI want to concatenate all of a Checkpoint's linked Checkpoint_Data together in sequence (1,2,..) and then use an XPath to extract some data that I want.Here is the query I have right now, but it does not do any concatenation, and does not work correctly:\[code\]SELECT distinct(xmltype(data.trans_data).extract('//somePath/text()').getStringVal()) FROM Checkpoint_Data cpd, Checkpoint cp, Transaction trWHERE cpd.checkpoint_id = cp.checkpoint_id and cp.location = 'someLocation' and cp.trans_id = tr.trans_id and tr.time >= to_date('3/26/2013:8:00 AM', 'mm/dd/yyyy:hh:miam') and tr.time <= to_date('3/26/2013:11:00 AM', 'mm/dd/yyyy:hh:miam') \[/code\]How can I concatenate the broken up XML contained in Checkpoint_Data and then use an XPath to extract data from it?