sql - 使用连接到其他表来更新 Oracle 中的 XML 元素

Question

我有一个 XML 存储在具有加密元素的 XMLTYPE 列中，我需要通过加入其他表来将其替换为解密的值。

-- Sample XML
<Root_Element xmlns="http://euroconsumers.org/notifications/2019/01/notification">    
    <DocID>ABCD1234</DocID>
    <InsertDate>2019-03-01</InsertDate>
    <TimeSeries>
        <Received_DocID>123456</Received_DocID>
        <Encrypted_ProviderID>PROV001</Encrypted_ProviderID>
        <Point>
            <Encrypted_BidID>BD001</Encrypted_BidID>
            <Amount>1.63</Amount>
        </Point>
    </TimeSeries>
    <TimeSeries>
        <Received_DocID>123456</Received_DocID>
        <Encrypted_ProviderID>PROV001</Encrypted_ProviderID>
        <Point>
            <Encrypted_BidID>BD002</Encrypted_BidID>
            <Amount>1.63</Amount>
        </Point>
    </TimeSeries>
    <TimeSeries>
        <Received_DocID>987654</Received_DocID>
        <Encrypted_ProviderID>PROV002</Encrypted_ProviderID>
        <Point>
            <Encrypted_BidID>BD101</Encrypted_BidID>
            <Amount>1.85</Amount>
        </Point>
    </TimeSeries>
</Root_Element>

-- PROVIDERS table

ProviderID Encrypted_ProviderID
PROVEU     PROV001
PROVUK     PROV002

-- BIDS table

Received_DocID BidID Encrypted_BidID
123456         1111  BD001
123456         2222  BD002
987654         3333  BD101


-- Expected output
<Root_Element xmlns="http://euroconsumers.org/notifications/2019/01/notification">    
    <DocID>ABCD1234</DocID>
    <InsertDate>2019-03-01</InsertDate>
    <TimeSeries>
        <Received_DocID>123456</Received_DocID>
        <Encrypted_ProviderID>PROVEU</Encrypted_ProviderID>
        <Point>
            <Encrypted_BidID>111</Encrypted_BidID>
            <Amount>1.63</Amount>
        </Point>
    </TimeSeries>
    <TimeSeries>
        <Received_DocID>123456</Received_DocID>
        <Encrypted_ProviderID>PROVEU</Encrypted_ProviderID>
        <Point>
            <Encrypted_BidID>2222</Encrypted_BidID>
            <Amount>1.63</Amount>
        </Point>
    </TimeSeries>
    <TimeSeries>
        <Received_DocID>987654</Received_DocID>
        <Encrypted_ProviderID>PROVUK</Encrypted_ProviderID>
        <Point>
            <Encrypted_BidID>3333</Encrypted_BidID>
            <Amount>1.85</Amount>
        </Point>
    </TimeSeries>
</Root_Element>

该标记将在 XML 中重复大约 1000 次，因此更新需要快速并且如果可能的话在一个语句中。

使用 XMLQuery 是否可以轻松快捷地执行此操作？

数据库是 Oracle 11.2.0.3

标签： sqlxmloracle