XML解析Oracle

Question

我执行SQL：

with demo as(
select XMLType('<items>
                  <item         id="1745212" template="Бокс            распределительный">
                         <p60226>DKC</p60226>
                        <p60227>R5PKEB2V81713B4P</p60227>
                    <p69317>Бокс распределительный</p69317>
                  </item>
                  <item id="1745213" template="Бокс     распределительный">
                    <p60226>DKC</p60226>
                    <p60227>R5PKEB2V817144P</p60227>
                    <p69317>Бокс распределительный   малый</p69317>
                    </item>
                  </items>'
                    ) xml from dual)
select   
          extractvalue(value(dt1),'/item/@id') id,
         dt2.column_value.getrootelement() property_name,
         extractValue(value(dt2),'/*') value
from 
     demo s,
     table(XMLSequence(s.xml.extract('/items/item'))) dt1,
     table(XMLSequence(s.xml.extract('/items/item/*'))) dt2

我等了6排回来。但它会返回12行！对于每个ID，此返回每一个项的值。如何修改这个SQL，只返回6行？谢谢！

Answer 1

请尝试以下pl/sql。

它模拟从XML层次结构派生的一对多关系。最好使用XMLTable() SQL/XML函数，而不是使用extractvalue()和XMLSequence()等过时的函数。

DB Fiddle

SQL

with tbl as
(
    select
        XMLType(
        '<items>
    <item id="1745212" template="Бокс распределительный">
        <p60226>DKC</p60226>
        <p60227>R5PKEB2V81713B4P</p60227>
        <p69317>Бокс распределительный</p69317>
    </item>
    <item id="1745213" template="Бокс распределительный">
        <p60226>DKC</p60226>
        <p60227>R5PKEB2V817144P</p60227>
        <p69317>Бокс распределительный малый</p69317>
    </item>
</items>'
        ) xmldata
    from dual
)
select p.id, p.template, c.property_name, c.line
from   tbl,
       XMLTable('/items/item' 
               PASSING tbl.xmldata 
               COLUMNS id NUMBER PATH '@id', 
                       template VARCHAR2(100) PATH '@template', 
                       lineitem XMLType PATH './*') p,
       XMLTable('*' 
               PASSING p.lineitem
               COLUMNS property_name varchar2(20) PATH 'local-name()',
               line VARCHAR2(100) PATH '.') c;

输出

+----------+-------------------------+----------------+------------------------------+
|   ID     |        TEMPLATE         | PROPERTY_NAME  |             LINE             |
+----------+-------------------------+----------------+------------------------------+
| 1745212  | Бокс распределительный  | p60226         | DKC                          |
| 1745212  | Бокс распределительный  | p60227         | R5PKEB2V81713B4P             |
| 1745212  | Бокс распределительный  | p69317         | Бокс распределительный       |
| 1745213  | Бокс распределительный  | p60226         | DKC                          |
| 1745213  | Бокс распределительный  | p60227         | R5PKEB2V817144P              |
| 1745213  | Бокс распределительный  | p69317         | Бокс распределительный малый |
+----------+-------------------------+----------------+------------------------------+

Answer 2

朋友们，我发现我的代码出错了！这个版本也运行得很好：

with demo as(
select XMLType('<items>
                  <item id="1745212" template="Бокс распределительный">
                    <p60226>DKC</p60226>
                    <p60227>R5PKEB2V81713B4P</p60227>
                    <p69317>Бокс распределительный</p69317>
                  </item>
                  <item id="1745213" template="Бокс распределительный">
                    <p60226>DKC</p60226>
                    <p60227>R5PKEB2V817144P</p60227>
                    <p69317>Бокс распределительный малый</p69317>
                    </item>
                  </items>'
                    ) xml from dual)
select   
          extractvalue(value(dt1),'/item/@id') id,
          extractvalue(value(dt1),'/item/@template') template,
         dt2.column_value.getrootelement() property_name,
         extractValue(value(dt2),'/*') value
from 
     demo s,
     table(XMLSequence(s.xml.extract('/items/item'))) dt1,
     table(XMLSequence(value(dt1).extract('/item/*'))) dt2