从XML数据中迭代并更新表

I have a table called customer, and I have the columns id, sortcode, and name.

我有一个名为customer的表,我有列id,sortcode和name。

I have one more table called tblxml, it has one column called xmlData of type CLOB and it stores the XML data.

我有一个名为tblxml的表,它有一个名为xdbData的列,类型为CLOB,它存储XML数据。

I have stored XML data as follows:

我存储了如下XML数据:

 <root>
    <nd>
        <id>1</id>
        <sc>001</sc>
    </nd>
    <nd>
        <id>2</id>
        <sc>001001</sc>
    </nd>
    <nd>
        <id>11</id>
        <sc>001001001</sc>
    </nd>
    <nd>
        <id>16</id>
        <sc>001001001001</sc>
    </nd>
    <nd>
        <id>13</id>
        <sc>001001002</sc>
    </nd>
    <nd>
        <id>9</id><sc>001002</sc>
    </nd>
    <nd>
        <id>14</id>
        <sc>001002001</sc>
    </nd>
    </root>

I have aligned it so it is easy to understand.

我已将它对齐,因此很容易理解。

I need to:

我需要:

extract this XML data from tblxml and update it in customer table.

从tblxml中提取此XML数据并在customer表中更新它。

extract Id from tblxml table and update the sortcode in customer table which matches to id.

从tblxml表中提取Id并更新customer表中与id匹配的sortcode。

loop the XML data in update the sortcode according to id.

循环XML数据,根据id更新sortcode。

I have used <nd> to separate the rows.

我用来分隔行。

As I'm new to Oracle, I haven't tried coding for this. Expecting some suggestions.

由于我是Oracle的新手,我还没有尝试过编码。期待一些建议。

2 个解决方案

#1

Sounds like XMLTABLE is what you're after:

听起来像XMLTABLE就是你所追求的:

with sample_data as (select '<root><nd>
    <id>1</id>
    <sc>001</sc>
</nd>
<nd>
    <id>2</id>
    <sc>001001</sc>
</nd>
<nd>
    <id>11</id>
    <sc>001001001</sc>
</nd>
<nd>
    <id>16</id>
    <sc>001001001001</sc>
</nd>
<nd>
    <id>13</id>
    <sc>001001002</sc>
</nd>
<nd>
    <id>9</id><sc>001002</sc>
</nd>
<nd>
    <id>14</id>
    <sc>001002001</sc>
</nd></root>' xdata from dual)
-- end of mimicking a table called "sample_data" containing your xml data. See the main SQL below:
select id,
       sc
from   sample_data sd
       cross join xmltable('/root/nd'
                           passing xmltype(sd.xdata)
                           columns id number path 'id',
                                   sc varchar2(10) path 'sc');


        ID SC        
---------- ----------
         1 001       
         2 001001    
        11 001001001 
        16 0010010010
        13 001001002 
         9 001002    
        14 001002001

Once you have the query to pull the results from the xml column, you can then use that as part of an update or merge statement to do the necessary updating. This is left as an exercise for you to do - there are plenty of examples in * and Google! If you get stuck, feel free to update your question with what you've tried.

一旦获得了从xml列中提取结果的查询,就可以将其用作更新或合并语句的一部分来进行必要的更新。这是留给你的练习 - 在*和Google中有很多例子!如果您遇到困难,请随时根据您的尝试更新您的问题。

#2

I have solved this problem.

我已经解决了这个问题。

Created new Temporary table called TMP_XML it has one column as hold_xml

创建了一个名为TMP_XML的新临时表,它有一列作为hold_xml

Inserting xml content from tblxml to TMP_XML table

将xml内容从tblxml插入到TMP_XML表中

Then extracting value from TMP_XML as follows and updating sortcode based on matched Id.

然后如下从TMP_XML中提取值并根据匹配的Id更新排序码。

  Insert    InTo TMP_XML
  Select    XmlType(PkgCBDmlXml.ExtractNodeValues(xmlData, 'root'))
    From    tblxml; 

  update    customer 
     set    sortcode =  (   Select  ExtractValue(Value(E),'//sc') Sc_New
                            From TMP_XML A,
                            TABLE(XmlSequence(Extract(A.hold_xml, '//nd'))) E
                            Where ExtractValue(Value(E),'//id') = id     
                            )

#1