在Oracle SQL中从XML CLOB列中删除数据

pkmbmrz7 于 2023-10-16 发布在 Oracle

关注(0)|答案(3)|浏览(124)

我尝试从CLOB列中检索XML信息，但没有找到合适的例子来理解使用EXTRACTVALUE（）的模式，因为它似乎已经过时了XMLQUERY（）/ XMLTABLE（）。您可能有比Oracle文档更好的示例源（https：//docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/XMLQUERY.html#sql-9 E8 D3220 - 2CF 5 - 4C 63-BDC 2 - 0526 D57 B 9 CDB）。
CLOB单元格XML_ID1的填充方式如下：

<?xml version="1.0" encoding="utf-16"?>
<SelectionConditions Version="1">
<SelectionCondition Type="Fruits">
<Parameter Name="Customer" Value="Susie">
<Parameter Name="Bananas" Value="13649" />
<Parameter Name="Kiwis" Value="26" />
<Parameter Name="Oranges" Value="11210" />
<Parameter Name="Mangos" Value="1793" />
</SelectionCondition>
<SelectionCondition Type="Fruits">
<Parameter Name="Customer" Value="Fred">
<Parameter Name="Bananas" Value="206" />
<Parameter Name="Kiwis" Value="45" />
<Parameter Name="Oranges" Value="33300" />
<Parameter Name="Mangos" Value="200" />
</SelectionCondition>
</SelectionConditions>

CLOB单元格XML_ID2的填充方式如下：

<?xml version="1.0" encoding="utf-16"?>
<SelectionConditions Version="1">
<SelectionCondition Type="Fruits">
<Parameter Name="Customer" Value="Jack">
<Parameter Name="Bananas" Value="1456" />
<Parameter Name="Oranges" Value="9800" />
<Parameter Name="Mangos" Value="17933" />
</SelectionCondition>
</SelectionConditions>

表格结构为：
| ID|店铺名称|CLOB_列|
| --|--|--|
| 1 |NY|（XML_ID1）|
| 2 |波士顿|（XML_ID2）|
csv：
ID、ShopName、CLOB_column
1，NY，（XML_ID1）
2，波士顿，（XML_ID2）
我想要一个像这样的结果：
| ID|店铺名称|客户|香蕉|
| --|--|--|--|
| 1 |NY|苏茜| 13649 |
| 1 |NY|弗雷德| 206 |
| 2 |波士顿|杰克| 1456 |
提前感谢大家！

oracle

来源：https://stackoverflow.com/questions/77010173/retrieve-data-from-xml-clob-column-in-oracle-sql

3条答案

按热度按时间

vom3gejh1#

WITH data(xml) AS (
    SELECT q'{<?xml version="1.0" encoding="utf-16"?>
<SelectionConditions Version="1">
<SelectionCondition Type="Fruits">
<Parameter Name="Apples" Value="1" />
<Parameter Name="Bananas" Value="13649" />
<Parameter Name="Kiwis" Value="26" />
<Parameter Name="Oranges" Value="11210" />
<Parameter Name="Mangos" Value="1793" />
</SelectionCondition>
</SelectionConditions>}' FROM dual 
)
SELECT xmlquery(q'{$v/SelectionConditions/SelectionCondition/Parameter[@Name='Bananas']/@Value}'
    passing xmltype(xml) AS "v" returning content).getStringVal() AS bananas,
    xmlquery(q'{$v/SelectionConditions/SelectionCondition/Parameter[@Name='Oranges']/@Value}'
    passing xmltype(xml) AS "v" returning content).getStringVal() AS oranges
FROM DATA
;

赞(0）回复(0）举报 2023-10-16

jgovgodb2#

另一个版本：

WITH data(id, shopname, xml) AS (
    SELECT 1, 'NY', xmltype(q'{<?xml version="1.0" encoding="utf-16"?>
<SelectionConditions Version="1">
<SelectionCondition Type="Fruits">
<Parameter Name="Customer" Value="Susie" />
<Parameter Name="Bananas" Value="13649" />
<Parameter Name="Bananas" Value="13650" />
<Parameter Name="Kiwis" Value="26" />
<Parameter Name="Oranges" Value="11210" />
<Parameter Name="Mangos" Value="1793" />
</SelectionCondition>
<SelectionCondition Type="Fruits">
<Parameter Name="Customer" Value="Fred" />
<Parameter Name="Bananas" Value="206" />
<Parameter Name="Kiwis" Value="45" />
<Parameter Name="Oranges" Value="33300" />
<Parameter Name="Mangos" Value="200" />
</SelectionCondition>
</SelectionConditions>}') FROM dual 
UNION ALL
    SELECT 2, 'Boston', xmltype(q'{<?xml version="1.0" encoding="utf-16"?>
<SelectionConditions Version="1">
<SelectionCondition Type="Fruits">
<Parameter Name="Customer" Value="Jack" />
<Parameter Name="Bananas" Value="1456" />
<Parameter Name="Oranges" Value="9800" />
<Parameter Name="Mangos" Value="17933" />
</SelectionCondition>
</SelectionConditions>}') FROM DUAL 
)
    SELECT id, shopname, customer, banana, orange FROM 
    DATA d,
    xmltable(
        '/SelectionConditions/SelectionCondition' passing d.xml
        columns
            customer varchar2(20) PATH './Parameter[@Name="Customer"]/@Value',
            banana varchar2(2000) PATH 'string-join(./Parameter[@Name="Bananas"]/@Value, ";")',
            orange varchar2(20) PATH 'string-join(./Parameter[@Name="Oranges"]/@Value, ";")'
    ) x1
;

ID|SHOPNAME|CUSTOMER|BANANA     |ORANGE|
--+--------+--------+-----------+------+
 1|NY      |Susie   |13649;13650|11210 |
 1|NY      |Fred    |206        |33300 |
 2|Boston  |Jack    |1456       |9800  |

赞(0）回复(0）举报 2023-10-16

qojgxg4l3#

对于N个节点的一个解决方案，你可以使用LISTAGG（）-它不会聚合NULL-但是你需要一些东西来分组：

WITH data(id, xml) AS (
    SELECT 1, xmltype(q'{<?xml version="1.0" encoding="utf-16"?>
<SelectionConditions Version="1">
<SelectionCondition Type="Fruits">
<Parameter Name="Apples" Value="1" />
<Parameter Name="Bananas" Value="13649" />
<Parameter Name="Bananas" Value="13650" />
<Parameter Name="Kiwis" Value="26" />
<Parameter Name="Oranges" Value="11210" />
<Parameter Name="Mangos" Value="1793" />
</SelectionCondition>
</SelectionConditions>}') FROM dual 
)
SELECT id, listagg(banana,',') within group(order by banana) as bananas,
    listagg(orange,',') within group(order by orange) as oranges
FROM (
    SELECT id, banana, orange FROM 
    DATA d,
    xmltable(
        '/SelectionConditions/SelectionCondition/Parameter' passing d.xml
        columns
            banana varchar2(20) PATH '.[@Name="Bananas"]/@Value',
            orange varchar2(20) PATH '.[@Name="Oranges"]/@Value'
    ) x1
)

按id分组;

ID bananas   oranges
1   13649,13650 11210

赞(0）回复(0）举报 2023-10-16

我来回答

在Oracle SQL中从XML CLOB列中删除数据

3条答案

相关问题

热门标签

最新问答