从CLOB提取XML数据 [英] Extracting XML data from CLOB

问题描述

如何从如下所述的数据中提取食品编号和食品名称和数量.这在plsql的clob列中.

How can I extract Food ItemID and Food Item Name and Quantity from the data as mentioned below. This is in clob column in plsql.

<ServiceDetails>
    <FoodItemDetails>
        <FoodItem FoodItemID="6486" FoodItemName="CARROT" Quantity="2" Comments="" ServingQuantityID="142" ServingQuantityName="SMALL GLASS" FoodItemPrice="50" ItemDishPriceID="5336" CurrencyName="INR" Currency Id="43"/>
    </FoodItemDetails>
    <BillOption>
        <Bill Details Total Price="22222" BillOption="cash"/>
    </BillOption>
    <Authoritativeness/>
</Service Details>

推荐答案

使用 xmltable .

数据设置:

create table myt(
col1 clob
);

insert into myt values('<ServiceDetails>
    <FoodItemDetails>
        <FoodItem FoodItemID="6486" FoodItemName="CARROT" Quantity="2" Comments="" ServingQuantityID="142" ServingQuantityName="SMALL GLASS" FoodItemPrice="50" ItemDishPriceID="5336" CurrencyName="INR" CurrencyId="43"/>
    </FoodItemDetails>
    <BillOption>
        <BillDetails TotalPrice="22222" BillOption="cash"/>
    </BillOption>
    <Authoritativeness/>
</ServiceDetails>'
);

commit;

查询:

select cols.*
from myt
cross join xmltable('ServiceDetails/FoodItemDetails/FoodItem' passing xmltype(col1)
            columns fooditemid varchar2(10) path '@FoodItemID',
                fooditemname varchar2(20) path '@FoodItemName'
        ) cols;

结果:

FOODITEMID FOODITEMNAME
---------- --------------------
6486       CARROT

这篇关于从CLOB提取XML数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！