Oracle XML: Extract multiple record in a given node

Question

Can someone help on how go i extract all the RefID? And what correct datatype will i use? Here's XML below:

<?xml version="1.0" encoding="UTF-8"?>
<Query xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="OutputQuery">
 <OutputOption>
  <RefId>
     <long>9474841</long>
     <long>9436906</long>
     <long>9506794</long>
  </RefId>
 </OutputOption>
</Query>

Output:

RefID
-----
9474841
9436906
9506794

Adrian P · Accepted Answer · 2018-09-14 13:06:11Z

0

This query should give you what you need:

select RefId
from
  xmltable(
    '/Query/OutputOption/RefId/long'
    passing xmltype(
      q'[<?xml version="1.0" encoding="UTF-8"?>
<Query xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="OutputQuery">
  <OutputOption>
    <RefId>
       <long>9474841</long>
       <long>9436906</long>
       <long>9506794</long>
    </RefId>
  </OutputOption>
</Query>
      ]'
    )
    columns RefId number path '/'
  )
;

The data type that you will need is XMLTYPE.

answered Sep 14, 2018 at 13:06

Adrian P

9048 silver badges14 bronze badges

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

2 Comments

Brian Leach Over a year ago

I am still searching for a good place to learn about using XML queries. Do you have any recommendations?

Adrian P Over a year ago

Tim Hall's oracle-base.com is a great site for reference. Then Oracle's official documentation.

Collectives™ on Stack Overflow

Oracle XML: Extract multiple record in a given node

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related