2 Replies Latest reply: Apr 30, 2012 11:11 AM by 268879 RSS

    Unable to extract XML element value

    268879
      Hello,

      I am trying to extract the value of the element, but getting an error which is mentioned below..

      1) Here's the XML:
      <?xml version="1.0" encoding="UTF-8" standalone=''yes''?>
      <ns1:XLS version="1.0" ns1:lang="en" rel="4.6.1.sp01" xmlns:ns1="http://www.opengis.net/xls">
      <ns1:ResponseHeader sessionID="888"/>
      <ns1:Response requestID="888" numberOfResponses="1" version="1.0">
      <ns1:DDSXMLResponse queryToken="S2LN">
      <ns1:Status>
      <ns1:Token keyword="EXTIME" value="1"/>
      <ns1:Token keyword="S" value="OK"/>
      <ns1:Token keyword="N" value="1"/>
      </ns1:Status>
      <ns1:OutputGroups>
      <ns1:OutputGroup>
      <ns1:Token1 keyword="LL" value="40.195166,-119.512163"/>
      <ns1:Token2 keyword="SN" value=""/>
      <ns1:Token3 keyword="PC" value="89405"/>
      <ns1:Token4 keyword="COUNTRY" value="USA"/>
      <ns1:Token5 keyword="STATE" value="NV"/>
      <ns1:Token6 keyword="CITY" value=""/>
      </ns1:OutputGroup>
      </ns1:OutputGroups>
      </ns1:DDSXMLResponse>
      </ns1:Response>
      </ns1:XLS>

      2) here's my query:
      SELECT b.street_number
      FROM (SELECT XMLTYPE ('<?xml version="1.0" encoding="UTF-8" standalone=''yes''?>
      <ns1:XLS version="1.0" ns1:lang="en" rel="4.6.1.sp01" xmlns:ns1="http://www.opengis.net/xls">
      <ns1:ResponseHeader sessionID="888"/>
      <ns1:Response requestID="888" numberOfResponses="1" version="1.0">
      <ns1:DDSXMLResponse queryToken="S2LN">
      <ns1:Status>
      <ns1:Token keyword="EXTIME" value="1"/>
      <ns1:Token keyword="S" value="OK"/>
      <ns1:Token keyword="N" value="1"/>
      </ns1:Status>
      <ns1:OutputGroups>
      <ns1:OutputGroup>
      <ns1:Token1 keyword="LL" value="40.195166,-119.512163"/>
      <ns1:Token2 keyword="SN" value=""/>
      <ns1:Token3 keyword="PC" value="89405"/>
      <ns1:Token4 keyword="COUNTRY" value="USA"/>
      <ns1:Token5 keyword="STATE" value="NV"/>
      <ns1:Token6 keyword="CITY" value=""/>
      </ns1:OutputGroup>
      </ns1:OutputGroups>
      </ns1:DDSXMLResponse>
      </ns1:Response>
      </ns1:XLS>
      ') AS revcode_xml
      FROM DUAL) A,
      XMLTABLE (
      XMLNAMESPACES (DEFAULT 'http://www.opengis.net/xls'), '/XLS'
      PASSING A.revcode_xml
      COLUMNS street_number VARCHAR2(200) PATH 'Response/DDSXMLResponse/OutputGroups/OutputGroup/Token/[@keyword="State"]') b

      I am getting this error:
      ORA-19112: error raised during evaluation: XVM-01003: [XPST0003] Syntax error at '['1 declare default element namespace

      What am I doing wrong? This is my first time working with XML, so maybe it's something basic which I am missing. So any help would be appreciated!!
        • 1. Re: Unable to extract XML element value
          odie_63
          Hi,
          What am I doing wrong?
          The PATH expression is not a valid XPath expression, probably a typo?
          Response/DDSXMLResponse/OutputGroups/OutputGroup/Token/[@keyword="State"]
                                                                ^
                                                                no slash here
          should be :
          Response/DDSXMLResponse/OutputGroups/OutputGroup/Token[@keyword="State"]
          Anyway, considering your input XML, this path points to nothing. There's no "Token" element child under OutputGroup.
          Maybe you meant something like this :
          SQL> SELECT b.street_number
            2  FROM (SELECT XMLTYPE ('<?xml version="1.0" encoding="UTF-8" standalone=''yes''?>
            3  <ns1:XLS version="1.0" ns1:lang="en" rel="4.6.1.sp01" xmlns:ns1="http://www.opengis.net/xls">
            4  <ns1:ResponseHeader sessionID="888"/>
            5  <ns1:Response requestID="888" numberOfResponses="1" version="1.0">
            6  <ns1:DDSXMLResponse queryToken="S2LN">
            7  <ns1:Status>
            8  <ns1:Token keyword="EXTIME" value="1"/>
            9  <ns1:Token keyword="S" value="OK"/>
           10  <ns1:Token keyword="N" value="1"/>
           11  </ns1:Status>
           12  <ns1:OutputGroups>
           13  <ns1:OutputGroup>
           14  <ns1:Token1 keyword="LL" value="40.195166,-119.512163"/>
           15  <ns1:Token2 keyword="SN" value=""/>
           16  <ns1:Token3 keyword="PC" value="89405"/>
           17  <ns1:Token4 keyword="COUNTRY" value="USA"/>
           18  <ns1:Token5 keyword="STATE" value="NV"/>
           19  <ns1:Token6 keyword="CITY" value=""/>
           20  </ns1:OutputGroup>
           21  </ns1:OutputGroups>
           22  </ns1:DDSXMLResponse>
           23  </ns1:Response>
           24  </ns1:XLS>
           25  ') AS revcode_xml
           26  FROM DUAL) A,
           27    XMLTABLE (
           28      XMLNAMESPACES (DEFAULT 'http://www.opengis.net/xls')
           29    , '/XLS/Response/DDSXMLResponse/OutputGroups/OutputGroup'
           30      PASSING A.revcode_xml
           31      COLUMNS
           32        street_number VARCHAR2(200) PATH '*[@keyword="STATE"]/@value'
           33    ) b
           34  ;
           
          STREET_NUMBER
          --------------------------------------------------------------------------------
          NV
           
          • 2. Re: Unable to extract XML element value
            268879
            Awesome, it works..Thanks a lot!!