如何在Oracle中“打开”XML数据

Question

这是一段我希望能够用PL/SQL重写的TSQL代码示例。

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.

- wcm

Answer 1

在这个SO页面中描述了几种方法：

Oracle Pl/SQL: 遍历XMLTYPE节点

更新：因为两种方法都是纯SQL（你可以从PL/SQL或任何与数据库交互的工具中调用此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       30
Sue       Jones       34

- Vincent Malgrat

---

嗨，文森特。那是一个好的链接（+1），但我不知道足够多的知识来看那里的答案如何解决我的问题。有两个回复。一个是SQL Plus解决方案，另一个是PLSQL解决方案，但它使用了一个叫做XMLTYPE的东西，没有给出完整的答案。如果我知道得更多，那可能已经为我解决了。 - wcm

@wcm：我对SQL Server不是很了解，但在这个例子中，语法看起来很像Oracle。 nodes = XMLSequence + extract -- value = extractvalue - Vincent Malgrat

你必须明白，微软尽可能从Oracle那里窃取了很多东西 :o)。在TSQL中，你倾向于将解决方案分成不同的步骤。似乎在Oracle中，你尝试在单个select中完成所有操作。在我的代码中，我将@xml声明为XML数据类型，然后设置值，最后在select语句中使用该值。而你的代码则是在select语句体中显式创建XMLTYPE。Oracle的解决方案可能更高效，但我认为TSQL更易于阅读和理解。这是一个很好的答案。我希望我能给你投多次票。 - wcm