How to "open" XML data in Oracle
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.
+2
source to share
1 answer
several methods are described in this SO:
Oracle Pl / SQL: Loop Through XMLTYPE Nodes
Update: Quite simple as 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
+3
source to share