oracle+xml嵌套,Oracle SQL生成嵌套的xml

我想通过在Oracle的PLSQL函数中只使用一个查询来获取嵌套

XML.

数据库(无法更改)：

表’products_details’：

`attr_id` | `attribute` | `fk_parent_id`(Foreign key on `attr_id`)

-------------------------------------------------------------------------------

1 | name | null

3 | sizes | null

4 | size_women | 3

5 | size_man | 3

6 | size_dimension | 3

表’product_contents’：

`detail` | `value` | variation_number | `product_id` (doesnt matter)

-------------------------------------------------------------------------------

name | Tshirt | null | 1000

price | 14.99 | null | 1000

size_man | XL | 1 | 1000

size_women | L | 1 | 1000

size_dimesion | 21x25cm | 1 | 1000

size_man | M | 2 | 1000

size_women | S | 2 | 1000

size_dimesion | 14x16cm | 2 | 1000

...

正如您所看到的,每种产品只有一些选项(名称,价格),但也有一些选项(size_man,size_woman ……)是变体,每种产品可以存在多次.

我想要的是一个XML：

Tshirt

14.99

XL

L

...

M

S

到目前为止我尝试过的(当然不是真的有效)：

SELECT

(

XMLELEMENT( "attribute",

XMLATTRIBUTES(pc.detail as "detail"),

(SELECT XMLAGG

(

XMLELEMENT("row", XMLATTRIBUTES(pc.variant_number as "variation_number") )

)

FROM product_contents pc

JOIN product_details pd ON pc.detail = pd.attribute and pc.product_id = '1000'

WHERE pd.fk_parent_id = pd.ID

)

).getClobVal() CONTENT

FROM product_details pd

pd.fk_parent_id is null

order by pd.attribute;

我怎么能用一个查询来做到这一点？