如何“打开" Oracle中的XML数据

更新:这非常简单，因为这两种方法都是纯SQL(您可以从PL/SQL或与DB交互的任何工具中调用此SQL):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 30Sue Jones 34Here is an example of some TSQL that I would like to rewrite in PL/SQL. DECLARE @xml XMLSET @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 OpenedXMLWHERE Age BETWEEN 30 AND 35Can anyone give me some direction here. 解决方案 a couple of methods are described in this SO:Oracle Pl/SQL: Loop through XMLTYPE nodesUpdate: 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 30Sue Jones 34 这篇关于如何“打开" Oracle中的XML数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！