使用Oracle解析器从XML获得第一次发生值(Getting First Occurred val

我不是甲骨文的专家，但按照要求，我使用的是Oracle解析器解析XML。对于下面列出的XML即

<?xml version="1.0" encoding="iso-8859-1" ?>
<SearchOutput>
<rowArray>
    <Row>
        <cellArray>
            <Cell>
                <columnId>1</columnId>
                <valueArray>
                    <Value>
                        <value>IR000024575453</value>
                    </Value>
                </valueArray>
            </Cell>
            <Cell>
                <columnId>5</columnId>
                <valueArray>
                    <Value>
                        <value>AZ12604823-001</value>
                    </Value>
                </valueArray>
            </Cell>
            <Cell>
                <columnId>2</columnId>
                <valueArray>
                    <Value>
                        <value>IT06686</value>
                    </Value>
                </valueArray>
            </Cell>
            <Cell>
                <columnId>9</columnId>
                <valueArray>
                    <Value>
                        <value>Hu Mics Metab K</value>
                    </Value>
                </valueArray>
            </Cell>
            <Cell>
                <columnId>8</columnId>
                <valueArray>
                    <Value>
                        <value>2006-06-21</value>
                    </Value>
                </valueArray>
            </Cell>
            <Cell>
                <columnId>7</columnId>
                <valueArray>
                    <Value>
                        <value>2006-07-27</value>
                    </Value>
                </valueArray>
            </Cell>
        </cellArray>
    </Row>
</rowArray>
</SearchOutput>

我已经使用该方法，其中l_xmlclob具有CLOB数据类型和分配上面的XML。

FOR r IN (  SELECT rownum rn, cells
           FROM xmltable('/SearchOutput/rowArray/Row' passing XMLTYPE(l_xmlclob)
                                     columns CELLS  XMLTYPE PATH './cellArray')
        )
 LOOP
  DBMS_OUTPUT.PUT_LINE('Row: '||r.rn);      

  FOR c IN ( SELECT colid, colval
               FROM xmltable('/cellArray/Cell' passing r.cells
                                               columns COLID NUMBER PATH './columnId',
                                                       COLVAL VARCHAR(20) PATH     './valueArray/Value/value')
           )
  LOOP
     DBMS_OUTPUT.PUT_LINE('colid, col value: '||c.colid||', '||c.colval);
  END LOOP;
 END LOOP;

其做工精细，其输出是一样

Row: 1
colid, col value: 1, IR000024575453
colid, col value: 5, AZ12604823-001
colid, col value: 2, IT06686
colid, col value: 9, Hu Mics Metab K
colid, col value: 8, 2006-06-21
colid, col value: 7, 2006-07-27

但问题出现了，如果有两个值一前一后另一个在XML，我想选择第一次出现只即用于下面的XML

<?xml version="1.0" encoding="iso-8859-1" ?>
<SearchOutput>
<rowArray>
<Row>
    <cellArray>
        <Cell>
            <columnId>1</columnId>
            <valueArray>
                <Value>
                    <value>Uganda</value>
                </Value>
                <Value>
                    <value>Italy</value>
                </Value>
            </valueArray>
        </Cell>
        <Cell>
            <columnId>5</columnId>
            <valueArray>
                <Value>
                    <value>AZ12604823-001</value>
                </Value>
            </valueArray>
        </Cell>
        <Cell>
            <columnId>2</columnId>
            <valueArray>
                <Value>
                    <value>IT06686</value>
                </Value>
            </valueArray>
        </Cell>
        <Cell>
            <columnId>9</columnId>
            <valueArray>
                <Value>
                    <value>Hu Mics Metab K</value>
                </Value>
            </valueArray>
        </Cell>
        <Cell>
            <columnId>8</columnId>
            <valueArray>
                <Value>
                    <value>2006-06-21</value>
                </Value>
            </valueArray>
        </Cell>
        <Cell>
            <columnId>7</columnId>
            <valueArray>
                <Value>
                    <value>2006-07-27</value>
                </Value>
                <Value>
                    <value>2012-02-27</value>
                </Value>
            </valueArray>
        </Cell>
    </cellArray>
</Row>
</rowArray>
</SearchOutput>

我想“乌干达，AZ12604823-001，IT06686，胡话筒代谢K，2006-06-21,2006-07-27”只内valueArray不是“意大利2012-02-27”进行选择。但是，不要不知道如何修改现有的代码。

我想这是你在找什么换

FOR r IN (  SELECT rownum rn, cells
           FROM xmltable('/SearchOutput/rowArray/Row' passing XMLTYPE(l_xmlclob)
                                     columns CELLS  XMLTYPE PATH './cellArray') f
        )
 LOOP
  DBMS_OUTPUT.PUT_LINE('Row: '||r.rn);      
  FOR c IN ( SELECT colid, 
                    colval
               FROM xmltable('for $i in /cellArray/Cell
                              return $i' 
                              passing r.cells
                              columns COLID NUMBER PATH 'columnId',
                                      COLVAL VARCHAR(20) PATH 'valueArray/Value[1]/value')                  
           )
  LOOP
     DBMS_OUTPUT.PUT_LINE('colid, col value: '||c.colid||', '||c.colval);
  END LOOP;
 END LOOP;
end;

要么

FOR r IN (  SELECT rownum rn, cells
           FROM xmltable('/SearchOutput/rowArray/Row' passing XMLTYPE(l_xmlclob)
                                     columns CELLS  XMLTYPE PATH './cellArray')
        )
 LOOP
  DBMS_OUTPUT.PUT_LINE('Row: '||r.rn);      

  FOR c IN ( SELECT colid, colval
               FROM xmltable('/cellArray/Cell' passing r.cells
                                               columns COLID NUMBER PATH './columnId',
                                                       COLVAL VARCHAR(20) PATH     './valueArray/Value[1]/value')
           )
  LOOP
     DBMS_OUTPUT.PUT_LINE('colid, col value: '||c.colid||', '||c.colval);
  END LOOP;
 END LOOP;

输出 -

Row: 1
colid, col value: 1, Uganda
colid, col value: 5, AZ12604823-001
colid, col value: 2, IT06686
colid, col value: 9, Hu Mics Metab K
colid, col value: 8, 2006-06-21
colid, col value: 7, 2006-07-27

Value[1]会给你第一即第一多行元件的Value单。