我想通过在Oracle的PLsql函数中只使用一个查询来获取嵌套
XML.
数据库(无法更改)：
表’products_details’：
@H_301_15@`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’：
@H_301_15@`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：
@H_301_15@<attribute detail="name">Tshirt</attribute> <attribute detail="price">14.99</attribute> <attribute detail="sizes"> <row variation_number="1"> <attribute detail="size_man">XL</attribute> <attribute detail="size_women">L</attribute> ... </row> <row variation_number="2"> <attribute detail="size_man">M</attribute> <attribute detail="size_women">S</attribute> </row> </attribute>到目前为止我尝试过的(当然不是真的有效)：
@H_301_15@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;我怎么能用一个查询来做到这一点？
解决方法
这个给你：
@H_301_15@WITH
-- "memory table"
product_details AS (
SELECT 1 attr_id,'name' attr,null parent_id FROM dual UNION ALL
SELECT 2,'price',null FROM dual UNION ALL
SELECT 3,'sizes',null FROM dual UNION ALL
SELECT 4,'size_women',3 FROM dual UNION ALL
SELECT 5,'size_man',3 FROM dual UNION ALL
SELECT 6,'size_dimension',3 FROM dual
),-- "memory table"
product_contents AS (
SELECT 'name' detail,'Tshirt' value,null variation,1000 product_id FROM dual UNION ALL
SELECT 'price','14.99',null,1000 FROM dual UNION ALL
SELECT 'size_man','XL',1,1000 FROM dual UNION ALL
SELECT 'size_women','L',1000 FROM dual UNION ALL
SELECT 'size_dimesion','21x25cm','M',2,'S','14x16cm',1000 FROM dual
),product_contents_xml AS (
SELECT
variation,detail,XMLELEMENT(
"attribute",XMLATTRIBUTES(detail as "detail"),value
) attr,product_id
FROM product_contents
),attrs AS (
SELECT
pc.attr
FROM
product_contents_xml pc JOIN
product_details pd ON pc.detail = pd.attr and pc.product_id = 1000
WHERE
pd.parent_id IS NULL
UNION ALL
SELECT
XMLELEMENT("attribute",XMLATTRIBUTES(t.attr AS "detail"),XMLAGG(t.value)) attr
FROM (
SELECT
parent.attr,XMLELEMENT("row",XMLATTRIBUTES(pc.variation as "variation_number"),XMLAGG(pc.attr)) value
FROM
product_contents_xml pc JOIN
product_details pd ON pc.detail = pd.attr and pc.product_id = 1000 JOIN
product_details parent ON parent.attr_id = pd.parent_id
WHERE
pd.parent_id IS NOT NULL
GROUP BY
parent.attr,pc.variation
) t
GROUP BY t.attr
)
SELECT XMLAGG(attr) FROM attrs
attrs视图分为两部分 – 一部分用于没有parent_id的属性,另一部分用于具有parent_id的部分.
