sql – XML Oracle:多个子节点提取

我有一个xml代码
<begin>
    <entry>
        <lastname>gordon</lastname>
        <NumberList>
            <number>100</number>
            <codelist>
                 <code>213</code>
                 <code>214</code>
            <codelist>
            <login>
                 <user>user1</user>
                 <user>user2</user>
            </login>
        <NumberList>
        <address>
            <addresslist>Jl. jalan pelan-pelan ke Bekasi,Indonesia</addresslist>
        </address>
    </entry>
    <entry>
        <lastname>mark</lastname>
        <address>
            <addresslist>Jl. jalan cepet-cepet ke Jakarta,Indonesia</addresslist>
        </address>
    </entry>
</begin>

我的代码

FOR r IN (SELECT VALUE(p) col_val,EXTRACT(VALUE(P),'/entry/codelist') AS code,'/entry/login') AS login
           FROM TABLE(XMLSequence(Extract(x,'/begin/entry'))) p)
LOOP
   IF r.col_val.existsnode('/entry/lastname/text()') > 0 
   THEN
      vc_lastname := r.col_val.extract('/sdnEntry/lastname/text()').getstringval();
   END IF;

   IF r.col_val.existsnode('/entry/address/addresslist/text()') > 0 
   THEN
    vc_address := r.col_val.extract('/sdnEntry/address/addresslist/text()').getstringval();
   END IF;

   IF r.col_val.existsnode('/entry/codelist/id/code/text()') > 0 AND r.col_val.existsnode('/entry/login/user/text()') > 0 
   THEN
      FOR R1 IN (SELECT EXTRACTVALUE(VALUE(T1),'/codelist/code/text()') AS code
                   FROM TABLE(XMLSEQUENCE(EXTRACT(R.code,'/codelist'))) T1)
      LOOP
         DBMS_OUTPUT.PUT_LINE(vc_uid||' - '||vc_firstName||' - '||R1.code||' - '||R2.address);
      END LOOP;

      FOR R2 IN (SELECT
                        EXTRACTVALUE(VALUE(T1),'/login/user/text()') AS user
                   FROM TABLE(XMLSEQUENCE(EXTRACT(R.address,'login/'))) T1)
      LOOP
         DBMS_OUTPUT.PUT_LINE(vc_uid||' - '||vc_firstName||' - '||R2.user||' - '||R2.address);
      END LOOP;
  ELSE
        DBMS_OUTPUT.PUT_LINE(vc_uid||' - '||vc_firstName);
  END IF;

我的问题:如何循环子节点,以便数据将如下所示:

LastName | Number | code    | user  |   address
gordon   | 100    | 213     | user1 |Jl. jalan pelan-pelan ke Bekasi,Indonesia
gordon   | 100    | 213     | user2 |Jl. jalan pelan-pelan ke Bekasi,Indonesia
gordon   | 100    | 214     | user1 |Jl. jalan pelan-pelan ke Bekasi,Indonesia
gordon   | 100    | 214     | user2 |Jl. jalan pelan-pelan ke Bekasi,Indonesia
mark     | Null   | null    | null  |Jl. jalan cepet-cepet ke Jakarta,Indonesia

任何帮助将是欣赏.

解决方法

您可以使用XMLTable()函数获得所需的结果:
select q.Lastname,q.Numberid,s.codeid,w.LoginId,q.address
  from t1 t
  left join xmltable('/begin/entry'
                      passing t.xml_col 
                      columns LastName   varchar2(21)  path 'lastname',NumberId   number        path 'NumberList/number',Address    varchar2(201) path 'address/addresslist',CodeList   XmlType       Path 'NumberList/codelist/code',Logins     XmlType       Path 'NumberList/login/user'
                      ) q
    on (1=1) 
  left join xmltable('/code'
                      passing q.CodeList
                      columns CodeId number path '.') s
    on (1=1)
  left join   xmltable('/user'
                        passing q.Logins
                        columns LoginId varchar2(11) path '.') w
    on (1=1)

结果:SQLFiddle Demo

Lastname Numberid Codeid Loginid Address 
---------------------------------------------------------------------------
gordon   100      213    user1   Jl. jalan pelan-pelan ke Bekasi,Indonesia 
gordon   100      213    user2   Jl. jalan pelan-pelan ke Bekasi,Indonesia 
gordon   100      214    user1   Jl. jalan pelan-pelan ke Bekasi,Indonesia 
gordon   100      214    user2   Jl. jalan pelan-pelan ke Bekasi,Indonesia 
mark     null     null   null    Jl. jalan cepet-cepet ke Jakarta,Indonesia

Find out more关于XMLTable()函数.

注意:使用11.2.0.2之前的Oracle版本,当cursor_sharing参数设置为FORCE或SIMILAR(从11.2开始不建议使用)时,您可能会遇到某些类型的XML查询的ORA-1780错误(错误8545377).将cursor_sharing参数设置为EXACT(默认值),将解决问题.

相关文章

(一)日志传送架构 (1.1)相关服务器 主服务器 :用于生产的服务器,上面运行这生产SQL Server数据库...
(一)事故背景 最近在SQL Server 2012生产数据库上配置完事物复制(发布订阅)后,生产数据库业务出现了...
(一)测试目的 目前公司使用的SQL SERVER 2012高可用环境为主备模式,其中主库可执行读写操作,备库既...
(一)背景个人在使用sql server时,用到了sql server的发布订阅来做主从同步,类似MySQL的异步复制。在...
UNION和OR谓词 找出 product 和 product2 中售价高于 500 的商品的基本信息. select * from product wh...
datawhale组队学习task03