Loop on XML with SQL (XMLType)

Question

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.

It's not clear what you're trying to do, what you've tried, and what behaviour you're seeing. Perhaps some sample code, and what error you're getting, would help. — Jeffrey Kemp
– Jeffrey Kemp, Commented May 31, 2012 at 3:20

marciel.deg · Accepted Answer · 2012-05-31 11:36:56Z

1

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

answered May 31, 2012 at 11:36

marciel.deg

5643 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rob van Wijk Over a year ago

If you are on Oracle10 or above, you'd want to switch to XMLTable(...) instead of TABLE(XMLSequence(...)). Shorter and clearer syntax.

Collectives™ on Stack Overflow

Loop on XML with SQL (XMLType)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related