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?
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> |