How to "Open" XML data in Oracle

Question

Here is an example of some TSQL that I would like to rewrite in PL/SQL.

DECLARE @xml XML

SET @xml = '<theRange>
    <theRow><First>Bob</First><Last>Smith</Last><Age>30</Age></theRow>
    <theRow><First>Sue</First><Last>Jones</Last><Age>34</Age></theRow>
    <theRow><First>John</First><Last>Bates</Last><Age>40</Age></theRow>
</theRange>'

;WITH OpenedXML AS (
    SELECT  r.value('First[1]','varchar(50)') AS First,
        r.value('Last[1]','varchar(50)') AS Last,
        r.value('Age[1]','int') AS Age
    FROM @xml.nodes('//theRange/theRow') AS Row(r)
)
SELECT * 
FROM OpenedXML
WHERE Age BETWEEN 30 AND 35

Can anyone give me some direction here.

Community · Accepted Answer · 2017-05-23 10:32:55Z

3

a couple of methods are described in this SO:

Oracle Pl/SQL: Loop through XMLTYPE nodes

Update: it's rather straightforward since both methods are pure SQL (you can call this SQL from PL/SQL or any tool that interacts with the DB):

SQL> WITH openedXml AS (
  2  SELECT extractvalue(column_value, '/theRow/First') FIRST,
  3         extractvalue(column_value, '/theRow/Last') LAST,
  4         to_number(extractvalue(column_value, '/theRow/Age')) Age
  5    FROM TABLE(XMLSequence(XMLTYPE('<theRange>
  6      <theRow><First>Bob</First><Last>Smith</Last><Age>30</Age></theRow>
  7      <theRow><First>Sue</First><Last>Jones</Last><Age>34</Age></theRow>
  8      <theRow><First>John</First><Last>Bates</Last><Age>40</Age></theRow>
  9  </theRange>').extract('/theRange/theRow')))
 10  )
 11  SELECT *
 12    FROM openedxml
 13   WHERE age BETWEEN 30 AND 35;

FIRST     LAST       AGE
--------- -------- -----
Bob       Smith       30
Sue       Jones       34

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Oct 21, 2009 at 19:05

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

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

3 Comments

wcm Over a year ago

Hi Vincent. That was a good link (+1) but I don't know enough to see how the answers there solve my problem. There are two responses. One is a SQL Plus solution and the other is a PLSQL solution but it uses something called XMLTYPE without giving a complete answer. If I knew more that would probably have solved it for me.

Vincent Malgrat Over a year ago

@wcm: I don't know much about SQL Server but in this example the synthax looks a lot like Oracle. nodes = XMLSequence + extract -- value = extractvalue

wcm Over a year ago

Ya have to understand that MS steals as much as possible from Oracle :o). In TSQL you tend to split your solutions into different steps. It seems that in Oracle you try to do everything in a single select. In my code, I declare @xml as the XML data type and then I set the value and then I use the value in my select. Your code creates the XMLTYPE explicitly in the body of the select. The Oracle solution is probably more efficient but I think the TSQL is easier to read through and understand. This was a great answer. I wish I could up vote you more than once.

Collectives™ on Stack Overflow

How to "Open" XML data in Oracle

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related