I get the following XML from a request:
<records>
<record>
<field name="code" value="1"/>
<field name="dexcription" value="MyName"/>
<field name="id" value="666">
</record>
<record>
<field name="code" value="2"/>
<field name="dexcription" value="MyName"/>
</record>
...
</records>
The first record was processed successfully, returning the element "id"; the second was not, and so returned without this element.
I need to write an XML SQL query based returning these two columns ("code" and "id"), but only records that were processed successfully. I tried to use XMLType, but it still fails. Can anyone help me?
Thanks in advance, and sorry for my "googled" English.
I found the solution:
SELECT EXTRACT (COLUMN_VALUE, '/record/field[@name="code"]/@value').GETSTRINGVAL () AS CODE,
EXTRACT (COLUMN_VALUE, '/record/field[@name="id"]/@value').GETSTRINGVAL () AS ID
FROM TABLE (XMLSEQUENCE (XMLTYPE (:XXML).EXTRACT ('/records/record')))
WHERE EXTRACT (COLUMN_VALUE, '/record/field[@name="id"]') IS NOT NULL