Hello,Using a code snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by a snippet from the xml clob that I am pulling from.select * from (Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag from ws_transactions Where Wtr_Service_Tag In ('20458749610') And Wtr_Req_Xml Like ('%CSM%') Order By Wtr_Receive_Date Desc)where rownum = 1;<ASSETTAGDATA><LIST NAME="AssetTag"> <VALUE SID="1">186037</VALUE> <VALUE SID="2">186038</VALUE> </LIST>
This query is only able to pull the first value in the list.I have two questions that I am hoping I can get some help with. [1]How can I edit this query to pull all of the list items when there are more than 1? I have another field, in a separate table, that I can pull from to get that number. [2]This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
Thank you in advance for your help. I hope I have provided enough information. I have I have not, I will gladly respond back with anything else that is needed.-Matt
Looping and querying data from XML clob
Posted - 2013-10-05 : 13:11:01
