在复杂的 PL/SQL 中使用 Oracle XMLElement
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13134170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Oracle XMLElement in complex PL/SQL
提问by wadesworld
Using Oracle 10gR2, I need to produce something like the following pseudo-example from data stored in standard relational tables.
使用 Oracle 10gR2，我需要从存储在标准关系表中的数据生成类似于以下伪示例的内容。
<product>
<productName>p1</productName>
<productNumber>100</productNumber>
<productObsoletes>
<obsoletedProduct label=1>50</obsoletedProduct>
<obsoletedProduct label=2>55</obsoletedProduct>
</productObsoletes>
</product>
The problem is, I need to do decision making using the rows which contain the data. My database (which I inherited) is poorly designed and the logic needed to decide upon a rows inclusion is complex. Unfortunately, redesigning the database is not an option. I'm vastly simplifying the logic here, so it's not a case where a simple join or where clause can be used. There's a complex hierarchy to the data, and the data format definition.
问题是，我需要使用包含数据的行来做决策。我的数据库（我继承的）设计得很差，决定行包含所需的逻辑很复杂。不幸的是，重新设计数据库不是一种选择。我在这里大大简化了逻辑，所以这不是可以使用简单连接或 where 子句的情况。数据和数据格式定义具有复杂的层次结构。
In psuedo code, the way-oversimplified decision would look something like:
在伪代码中，过于简单化的决策看起来像这样：
BEGIN
--select our basic attributes
select XMLEMENT("product",XMLELEMENT("productName",name),XMLELEMENT("productNumber",product_number))
into xml_output from products where product_number = 100;
--now process our obsolete rows
select XMLELEMENT("productObsoletes") into xml_output from dual;
FOR c_row in (select * from product_obsoletes where id=100)
LOOP
IF c_row.display = 'YES' THEN
select XMLELEMENT("obsoletedProduct", XMLATTRIBUTES(c_row.label as "label"), c_row.obsoleted_product_id) into xml_output from dual;
ELSE
CONTINUE;
END IF;
END LOOP;
END;
Obviously though, this doesn't work. First, XMLElement always puts a closing tag on, so unless you can formulate a single select statement containing all of your elements, it's not going to work. Second, in this example, I'd be overwriting the previous XMLELEMENT output. I can't concatenate, because XMLELEMENT has already closed the tag.
显然，这行不通。首先，XMLElement 总是放置一个结束标记，因此除非您可以制定包含所有元素的单个 select 语句，否则它不会起作用。其次，在本例中，我将覆盖之前的 XMLELEMENT 输出。我无法连接，因为 XMLELEMENT 已经关闭了标记。
The only solution I can think of at this point is to select the various XMLElement pieces I need into different VARCHARS and then use string manipulation functions to find the right spot to insert the various pieces. Ugly with a capital-U.
此时我能想到的唯一解决方案是将我需要的各种 XMLElement 片段选择到不同的 VARCHARS 中，然后使用字符串操作函数找到插入各种片段的正确位置。丑陋的大写-U。
Is there another solution? Is there perhaps a way to prevent XMELEMENT from closing the tag so that you can you use complex looping logic? Or another Oracle construct which would allow me to achieve my goal of building up this XML without having to use a single select statement?
还有其他解决方案吗？有没有办法阻止 XMELEMENT 关闭标签，以便您可以使用复杂的循环逻辑？或者另一种 Oracle 构造可以让我实现构建此 XML 的目标，而无需使用单个 select 语句？
My path of last resort would be to do the XML generation from another language such as Java or Perl where I could simply fetch the data and then do my complex processing and XML generation there. However, I'd much prefer just to have a SQL function which would return the correct XML as a string, so if it's possible to do it in PL/SQL, that'd be my preferred route.
我最后的方法是从另一种语言（例如 Java 或 Perl）生成 XML，在那里我可以简单地获取数据，然后在那里进行复杂的处理和 XML 生成。但是，我更喜欢有一个 SQL 函数，它将正确的 XML 作为字符串返回，因此如果可以在 PL/SQL 中执行此操作，那将是我的首选路线。
采纳答案by cagcowboy
How about using XMLDOM (a different inbuilt package) to create the XML instead?
使用 XMLDOM（不同的内置包）来创建 XML 怎么样？
You'll probably need to write more code, but if your logic is sufficiently complicated this is what you're going to have to do anyway.
您可能需要编写更多代码，但如果您的逻辑足够复杂，那么无论如何您都必须这样做。
This is a good quick summary of a few options you could use (including XMLDOM), with examples:
这是您可以使用的一些选项（包括 XMLDOM）的一个很好的快速总结，并附有示例：
回答by A.B.Cade
I think this can be done as a query, you'll have to combine xmlaggxmlelementand xmlforest
我认为这可以作为查询来完成，你必须结合xmlaggxmlelement和xmlforest
Here is an example:
下面是一个例子：
select XMLELEMENT("product",(XMLForest( p.product_name as "productName",
p.product_number as "productNumber",
xmlagg(
XMLELEMENT("obsoletedProduct",
XMLATTRIBUTES(po.label as "label"),
po.obsoleted_product_id)) as "productObsoletes")))
from products p
join product_obsoletes po on p.product_number=po.id
group by p.product_name, p.product_number
and hereis the fiddle
而这里是小提琴
