xmloracle-databaseoracle-sqldeveloperextract-value

Read Xml value in oracle query


I need to extract the entire LinkSnapshot xml element from a list of these elements. The list is stored in a column named Links. I've tried the following but I'm getting an error that reads "19026. 00000 - "EXTRACTVALUE can only retrieve value of leaf node""

 Extractvalue(xmltype(ss.links), '/List/LinkSnapshot') AS "Snapshot"

This is the xml

 <List>
   <LinkSnapshot application="App1" id="application1234" >
     <Attributes>
       <Map>
         <entry key="GenericAccount" value="false"/>       
       </Map>
     </Attributes>
   </LinkSnapshot>
 </List>

Is there a different command that I should use to extract the parent LinkSnapshot node as a whole?


Solution

  • Use XMLQUERY:

    SELECT XMLQUERY(
             '//List/LinkSnapshot'
             PASSING XMLTYPE(ss.links)
             RETURNING CONTENT
           ) AS snapshot
    FROM   table_name ss
    

    Which, for the sample data:

    CREATE TABLE table_name (links) AS
    SELECT EMPTY_CLOB() || '<List>
      <LinkSnapshot application="App1" id="application1234" >
        <Attributes>
          <Map>
            <entry key="GenericAccount" value="false"/>       
          </Map>
        </Attributes>
      </LinkSnapshot>
    </List>' FROM DUAL;
    

    Outputs:

    SNAPSHOT
    <LinkSnapshot application="App1" id="application1234"><Attributes><Map><entry key="GenericAccount" value="false"/></Map></Attributes></LinkSnapshot>

    fiddle