OraFAQ Forum: SQL & PL/SQL » How to insert an array from an xml into a query?

Home » SQL & PL/SQL » SQL & PL/SQL » How to insert an array from an xml into a query?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to insert an array from an xml into a query? [message #210114]

Tue, 19 December 2006 08:02

jonathan184
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,
         to_char(substr(eventdata,instr(eventdata,'<ns0:SiteContractNumber>')+24,
                   instr(eventdata,'</ns0:SiteContractNumber>') - instr(eventdata,
                   '<ns0:SiteContractNumber>')-24)) SiteContractNumber,
       	 to_char(substr(eventdata,instr(eventdata,'<ns0:ContactNumber>')+19,
       	        instr(eventdata,'</ns0:ContactNumber>') - instr(eventdata,
       	        '<ns0:ContactNumber>')-19)) ContactNumber,
       	 to_char(substr(eventdata,instr(eventdata,'<ns0:LineItemNumber>')+20,
       	        instr(eventdata,'</ns0:LineItemNumber>') - instr(eventdata,
       	        '<ns0:LineItemNumber>')-20)) LineItemNumber, 
       	 to_char(substr(eventdata,instr(eventdata,'<ns0:StartDate>')+15,
       	        instr(eventdata,'</ns0:StartDate>') - instr(eventdata,
       	        '<ns0:StartDate>')-15)) StartDate,
       	 to_char(substr(eventdata,instr(eventdata,'<ns0:EndDate>')+13,
       	        instr(eventdata,'</ns0:EndDate>') - instr(eventdata,
       	        '<ns0:EndDate>')-13)) EndDate,
       	 to_char(substr(eventdata,instr(eventdata,'<ns0:ProductNumber>')+19,
       	        instr(eventdata,'</ns0:ProductNumber>') - instr(eventdata,
       	        '<ns0:ProductNumber>')-19)) MaterialNumber,
         to_char(substr(eventdata,instr(eventdata,'<ns0:ProductDescription>')+24,
       	        instr(eventdata,'</ns0:ProductDescription>') - instr(eventdata,
       	        '<ns0:ProductDescription>')-24)) ServicePartDescription,     
         to_char(substr(eventdata,instr(eventdata,'<ns0:SLANumber>')+15,
       	        instr(eventdata,'</ns0:SLANumber>') - instr(eventdata,
       	        '<ns0:SLANumber>')-15)) ServicePartNumber,   
         to_char(substr(eventdata,instr(eventdata,'<ns0:Description>')+17,
       	        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:SLAS>
- <ns0:SLA>
  <ns0:Type>PHONE_COVERAGE</ns0:Type> 
  <ns0:Description>8X5</ns0:Description> 
  </ns0:SLA>
- <ns0:SLA>
  <ns0:Type>PHONE</ns0:Type> 
  <ns0:Description>1 HOUR CALLBACK</ns0:Description> 
  </ns0:SLA>
- <ns0:SLA>
  <ns0:Type>HARDWARE_COVERAGE</ns0:Type> 
  <ns0:Description>8X5</ns0:Description> 
  </ns0:SLA>
- <ns0:SLA>
  <ns0:Type>HARDWARE_RESPONSE</ns0:Type> 
  <ns0:Description>NBD</ns0:Description> 
  </ns0:SLA>
- <ns0:SLA>
  <ns0:Type>SOFTWARE_COVERED</ns0:Type> 
  <ns0:Description>SOFTWARE</ns0:Description> 
  </ns0:SLA>
  </ns0:SLAS>

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.

Thanks

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 00:21:09 CST 2024