尝试遍历XML以提取PLSQL中的值 [英] Trying to loop through XML to extract values in PLSQL

问题描述

我正在尝试遍历XML并提取UUID.我有以下内容，它正在遍历正确的次数并每次都打印空白行.为什么不提取UUID节点的文本值?

I am trying to loop through XML and extract the UUIDs. I have the following and it is looping through the correct number of times and printing a blank row each time. Why is it not extracting the text value of the UUID node?

   DECLARE
       X XMLTYPE := XMLTYPE('<?xml version="1.0" ?> 
         <StatusUp>
           <G_UUIDs>
               <UUID>1 test 1</UUID>
               <UUID>2 test 2</UUID>
               <UUID>3 test 3 </UUID>
               <UUID>4 test 4 </UUID>
           </G_UUIDs>
        </StatusUp>');
    BEGIN
       FOR r IN (SELECT EXTRACTVALUE(VALUE(p), 'StatusUp/G_UUIDs/UUID/text()') AS uuid

                   FROM TABLE(XMLSEQUENCE(EXTRACT(X, '//StatusUp/G_UUIDs/UUID'))) p)
       LOOP
          DBMS_OUTPUT.PUT_LINE('UUID' || r.uuid);
       END LOOP;
    END;

推荐答案

如果要将其转换为sql语句并运行它，如下所示:

If you were to convert this to a sql statement and run it, like so:

WITH sample_data AS (SELECT XMLTYPE('<?xml version="1.0" ?> 
         <StatusUp>
           <G_UUIDs>
               <UUID>1 test 1</UUID>
               <UUID>2 test 2</UUID>
               <UUID>3 test 3 </UUID>
               <UUID>4 test 4 </UUID>
           </G_UUIDs>
        </StatusUp>') x FROM dual)
SELECT p.*,
       EXTRACTVALUE(VALUE(p), 'StatusUp/G_UUIDs/UUID/text()') AS uuid
FROM   sample_data sd,
       TABLE(XMLSEQUENCE(EXTRACT(sd.x, '//StatusUp/G_UUIDs/UUID'))) p;

很容易发现问题:

COLUMN_VALUE             UUID
------------------------ ----------
<UUID>1 test 1</UUID>
<UUID>2 test 2</UUID>
<UUID>3 test 3 </UUID> 
<UUID>4 test 4 </UUID>

即您试图从仅包含节点UUID的xml中提取节点StatusUp/G_UUIDs/UUID.相反，如果您更正了要查询的节点，则会得到正确的结果:

I.e. you're trying to extract the node StatusUp/G_UUIDs/UUID from xml that only contains the node UUID. Instead, if you correct the node you're querying for, you get the right result:

DECLARE
   X XMLTYPE := XMLTYPE('<?xml version="1.0" ?> 
     <StatusUp>
       <G_UUIDs>
           <UUID>1 test 1</UUID>
           <UUID>2 test 2</UUID>
           <UUID>3 test 3 </UUID>
           <UUID>4 test 4 </UUID>
       </G_UUIDs>
    </StatusUp>');
BEGIN
   FOR r IN (SELECT EXTRACTVALUE(VALUE(p), 'UUID/text()') AS uuid
               FROM TABLE(XMLSEQUENCE(EXTRACT(X, '//StatusUp/G_UUIDs/UUID'))) p)
   LOOP
      DBMS_OUTPUT.PUT_LINE('UUID' || r.uuid);
   END LOOP;
END;
/

UUID1 test 1
UUID2 test 2
UUID3 test 3 
UUID4 test 4 

但是，不建议使用EXTRACT和EXTRACTVALUE-您应该改用XMLTABLE:

However, EXTRACT and EXTRACTVALUE are deprecated - you should be using XMLTABLE instead:

DECLARE
   X XMLTYPE := XMLTYPE('<?xml version="1.0" ?> 
     <StatusUp>
       <G_UUIDs>
           <UUID>1 test 1</UUID>
           <UUID>2 test 2</UUID>
           <UUID>3 test 3 </UUID>
           <UUID>4 test 4 </UUID>
       </G_UUIDs>
    </StatusUp>');
BEGIN
   FOR r IN (SELECT *
             FROM   XMLTABLE('//StatusUp/G_UUIDs/UUID'
                             PASSING x
                             COLUMNS uuid varchar2(10) PATH '.'))
   LOOP
      DBMS_OUTPUT.PUT_LINE('UUID' || r.uuid);
   END LOOP;
END;
/

UUID1 test 1
UUID2 test 2
UUID3 test 3 
UUID4 test 4 

---

您应尝试运行的等效查询:

---

Equivalent query that you should try running:

WITH sample_data AS (SELECT XMLTYPE('<?xml version="1.0" ?> 
         <StatusUp>
           <G_UUIDs>
               <UUID>1 test 1</UUID>
               <UUID>2 test 2</UUID>
               <UUID>3 test 3 </UUID>
               <UUID>4 test 4 </UUID>
           </G_UUIDs>
        </StatusUp>') x FROM dual)
SELECT *
FROM   sample_data sd,
       XMLTABLE('//StatusUp/G_UUIDs/UUID'
                PASSING sd.x
                COLUMNS uuid varchar2(10) PATH '.');

这篇关于尝试遍历XML以提取PLSQL中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！