Home » SQL & PL/SQL » SQL & PL/SQL » How to insert an array from an xml into a query?
How to insert an array from an xml into a query? [message #210114] Tue, 19 December 2006 08:02
Messages: 21
Registered: November 2006
Junior Member

This is my query

  select to_char(ed.created_date,'mm/dd/yy') Created_Date,
                  TRIM(ed.ERRORMSG) ErrorMsg,
                   instr(eventdata,'</ns0:SiteContractNumber>') - instr(eventdata,
                   '<ns0:SiteContractNumber>')-24)) SiteContractNumber,
       	        instr(eventdata,'</ns0:ContactNumber>') - instr(eventdata,
       	        '<ns0:ContactNumber>')-19)) ContactNumber,
       	        instr(eventdata,'</ns0:LineItemNumber>') - instr(eventdata,
       	        '<ns0:LineItemNumber>')-20)) LineItemNumber, 
       	        instr(eventdata,'</ns0:StartDate>') - instr(eventdata,
       	        '<ns0:StartDate>')-15)) StartDate,
       	        instr(eventdata,'</ns0:EndDate>') - instr(eventdata,
       	        '<ns0:EndDate>')-13)) EndDate,
       	        instr(eventdata,'</ns0:ProductNumber>') - instr(eventdata,
       	        '<ns0:ProductNumber>')-19)) MaterialNumber,
       	        instr(eventdata,'</ns0:ProductDescription>') - instr(eventdata,
       	        '<ns0:ProductDescription>')-24)) ServicePartDescription,     
       	        instr(eventdata,'</ns0:SLANumber>') - instr(eventdata,
       	        '<ns0:SLANumber>')-15)) ServicePartNumber,   
       	        instr(eventdata,'</ns0:Description>') - instr(eventdata,
       	        '<ns0:Description>')-17)) PHONECOVERAGE

from exception e, exception_detail ed
           where e.jmsid = ed.jmsid
             and e.timeoccured > trunc(sysdate - 2)
             and (projectname like '%delta4%' or projectname like '%delta9%')
             and projectname = 'PROD2-Createinfo'
             and ed.ERRORMSG not like '%error generated by Abort_Process%'
    order by 1 DESC, 3

Now this query works fine
The problem is the XML I used to create to char parts of the query
They usually have there label like
<ns0:StartDate>08/01/2006 00:00:00</ns0:StartDate> 
  <ns0:EndDate>07/31/2007 00:00:00</ns0:EndDate>
and so on and this works great.

Now the entries I am having problems with are
- <ns0:SLA>
- <ns0:SLA>
  <ns0:Description>1 HOUR CALLBACK</ns0:Description> 
- <ns0:SLA>
- <ns0:SLA>
- <ns0:SLA>

I am trying to put these as to char and the problem is they all are showing up as description. It looks like an array but how do i break it down so that i could put it in the select statement.

This query is used to capture errors.
Please help or if you have another solution I am open to that also.

Previous Topic: Using mod function on varchar2 field
Next Topic: Creating procedure
Goto Forum:

Current Time: Sat Feb 25 21:30:04 CST 2017

Total time taken to generate the page: 0.09497 seconds