This discussion is archived
2 Replies Latest reply: Sep 30, 2012 4:18 AM by odie_63 RSS

XQuery help

865005 Newbie
Currently Being Moderated
I have loaded an xml file into a table which has a column of type xmltype.
I want some help in selecting the data from this table.

Here's what the xml file looks like:

<?xml version="1.0" encoding="UTF-8"?>
<Download:MarketResults xmlns:Download="http://crr.caiso.org/download/xml" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://crr.caiso.org/download/xml http://ftapjbos10:8080/crr/mui/download/xml/PublicMarketResults.xsd">
<Download:Result>
<Download:CRRID>123456</Download:CRRID>
<Download:Category>PTP</Download:Category>
<Download:MarketParticipant>ABCD</Download:MarketParticipant>
<Download:Source>EFGH</Download:Source>
<Download:Sink>IJKL</Download:Sink>
<Download:StartDate>2012-10-01</Download:StartDate>
<Download:EndDate>2012-10-31</Download:EndDate>
<Download:HedgeType>OBL</Download:HedgeType>
<Download:CRRType>AUC</Download:CRRType>
<Download:Type>BUY</Download:Type>
<Download:TimeOfUse>ON</Download:TimeOfUse>
<Download:MW>50.000</Download:MW>
<Download:ClearingPrice>1066.71</Download:ClearingPrice>
</Download:Result>
</Download:MarketResults>

I am trying something like this but its not working for me. the table tempxmltype has a column called xmldata with xmltype datatype.

select opp.crr_id
FROM tempxmltype txml,
XMLTABLE (
XMLNAMESPACES (
'http://crr.caiso.org/download/xml' AS "x",
'http://www.w3.org/2001/XMLSchema-instance' AS "y"),
'http://crr.caiso.org/download/xml http://ftapjbos10:8080/crr/mui/download/xml/PublicMarketResults.xsd' AS "z"),
'/x:MarketResults'
PASSING xmldata) opps,
XMLTABLE (
XMLNAMESPACES (
'http://crr.caiso.org/download/xml' AS "x", 'http://www.w3.org/2001/XMLSchema-instance' AS "y", 'http://crr.caiso.org/download/xml http://ftapjbos10:8080/crr/mui/download/xml/PublicMarketResults.xsd' AS "z"),
'/x:MarketResults/x:Result'
PASSING xmldata
COLUMNS crr_id VARCHAR2 (30) PATH '/x:Result/x:CRRID'
) opp
WHERE feed_id = 1
  • 1. Re: XQuery help
    865005 Newbie
    Currently Being Moderated
    Never mind, I figured it out myself.
  • 2. Re: XQuery help
    odie_63 Guru
    Currently Being Moderated
    Hi,

    Just FYI, and if someone else's interested, you can simplify the query down to :
    SQL> SELECT opp.*
      2  FROM tempxmltype t
      3     , XMLTable (
      4         XMLNamespaces(default 'http://crr.caiso.org/download/xml')
      5       , '/MarketResults/Result'
      6         PASSING t.xmldata
      7         COLUMNS crr_id   VARCHAR2(30) PATH 'CRRID'
      8               , cat      VARCHAR2(10) PATH 'Category'
      9               , start_dt DATE         PATH 'StartDate'
     10               , end_dt   DATE         PATH 'EndDate'
     11       ) opp
     12  ;
     
    CRR_ID                         CAT        START_DT    END_DT
    ------------------------------ ---------- ----------- -----------
    123456                         PTP        01/10/2012  31/10/2012
     
    Only declare the namespace(s) you need to resolve the XQuery expression.
    The "xsi" prefix is also predefined in Oracle's XQuery context, so whenever you need it you can omit its declaration.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points