sql - 使用 SQL 提取 XML 数据
问题描述
我希望能够在我的示例中为名为“Arshad Ali”的客户使用 Oracle 从 XML 类型中提取特定数据
这是我插入的 xml 数据:
<Customers>
<Customer CustomerName="Arshad Ali" CustomerID="C001">
<Orders>
<Order OrderDate="2012-07-04T00:00:00" OrderID="10248">
<OrderDetail Quantity="5" ProductID="10" />
<OrderDetail Quantity="12" ProductID="11" />
<OrderDetail Quantity="10" ProductID="42" />
</Order>
</Orders>
<Address> Address line 1, 2, 3</Address>
</Customer>
<Customer CustomerName="Paul Henriot" CustomerID="C002">
<Orders>
<Order OrderDate="2011-07-04T00:00:00" OrderID="10245">
<OrderDetail Quantity="12" ProductID="11" />
<OrderDetail Quantity="10" ProductID="42" />
</Order>
</Orders>
<Address> Address line 5, 6, 7</Address>
</Customer>
<Customer CustomerName="Carlos Gonzlez" CustomerID="C003">
<Orders>
<Order OrderDate="2012-08-16T00:00:00" OrderID="10283">
<OrderDetail Quantity="3" ProductID="72" />
</Order>
</Orders>
<Address> Address line 1, 4, 5</Address>
</Customer>
</Customers>
</ROOT>
使用 get clob我能够提取所有客户。
想知道是否有人可以帮助我为特定客户提取数据..尝试使用以下但不成功
SELECT extract(OBJECT_VALUE, '/root/Customers') "customer"
FROM mytable2
WHERE existsNode(OBJECT_VALUE, '/customers[CustomerName="Arshad Ali" CustomerID="C001"]')
= 1;
解决方案
XML 节点的大小写和确切名称很重要:
SELECT extract(OBJECT_VALUE,
'/ROOT/Customers/Customer[@CustomerName="Arshad Ali"][@CustomerID="C001"]') "customer"
FROM mytable2
WHERE existsnode (OBJECT_VALUE,
'/ROOT/Customers/Customer[@CustomerName="Arshad Ali"][@CustomerID="C001"]') = 1
如果您只想按名称搜索,则仅使用该属性:
SELECT extract(OBJECT_VALUE,
'/ROOT/Customers/Customer[@CustomerName="Arshad Ali"]') "customer"
FROM mytable2
WHERE existsnode (OBJECT_VALUE,
'/ROOT/Customers/Customer[@CustomerName="Arshad Ali"]') = 1
但是extract()
和existsnode()
已被弃用;使用xmlquery()
andxmlexists()
代替:
SELECT xmlquery('/ROOT/Customers/Customer[@CustomerName="Arshad Ali"][@CustomerID="C001"]'
passing object_value
returning content) "customer"
FROM mytable2
WHERE xmlexists('/ROOT/Customers/Customer[@CustomerName="Arshad Ali"][@CustomerID="C001"]'
passing object_value)
推荐阅读
- mysql - 如果在spring数据jpa查询中为null返回空白
- c++ - 打印两个整数之间的偶数
- javascript - 根据从数据库中获取的值打开表单
- nginx - Odoo 13 网络资产失败
- unity3d - 如何绘制路径轨迹像下面的游戏参考统一
- microcontroller - 使用 UART 同步两个微控制器之间的串行通信的最佳方法是什么?
- php - 如何在codeigniter中限制同一用户多次登录另一个系统
- google-cloud-platform - 将子域重定向到谷歌云
- php - Laravel 6 - Illuminate\Database\Eloquent\RelationNotFoundException 调用未定义的关系
- php - “错误:插入”非特定插入错误