developers

    Forum Stats

  • 3,873,852 Users
  • 2,266,621 Discussions
  • 7,911,645 Comments

Discussions

XQuery help

865005
865005 Member Posts: 4
edited Sep 30, 2012 7:18AM in XQuery
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

Answers

  • 865005
    865005 Member Posts: 4
    Never mind, I figured it out myself.
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    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.
This discussion has been closed.
developers