2

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;

2 Answers 2

1

You've already extracted UUID from XML in FROM statement, and in select, figuratively speaking, you now only have four rows

<UUID> n test n </UUID>

So the correct way to extract that data be:

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
                '/UUID/text()') AS uuid
            FROM
              TABLE(
                XMLSEQUENCE(
                  EXTRACT(
                    X,
                    '//StatusUp/G_UUIDs/UUID')
                )
              ) p
           )
  LOOP
    DBMS_OUTPUT.PUT_LINE(r.uuid);
  END LOOP;
END;
Sign up to request clarification or add additional context in comments.

Comments

1

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;

it would be easy to spot the problem:

COLUMN_VALUE             UUID
------------------------ ----------
<UUID>1 test 1</UUID>
<UUID>2 test 2</UUID>
<UUID>3 test 3 </UUID> 
<UUID>4 test 4 </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 

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 '.');

9 Comments

thanks I had just figured out the issue with the extractvalue node being wrong and was about to answer my own question. I was unaware of XMLTABLE and will look at that.
I get an error when i run the xmltable suggestion, specifically: Error at line 1 ORA-19114: error during parsing the XQuery expression: java.lang.NoClassDefFoundError ORA-06512: at line 12
Are you getting that error with the code I gave you? Because that worked without error for me. If so, what version of Oracle are you running it on?
@bonesit I am on Oracle 10g and yes it was your exact code that I was trying to run.
Hmm. It definitely runs ok on 11.2.0.4. There's a query I'd like you to try, which is the equivalent of the stored proc which I've added to my question - when you run it, do you still get an error?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.