2 Replies Latest reply: Sep 30, 2012 6:18 AM by odie_63 RSS

    XQuery help

    865005
      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
          Never mind, I figured it out myself.
          • 2. Re: XQuery help
            odie_63
            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.