Select XML attribute into pl/sql collection

[MikeAJ]

Hi,

I have a very simple xml structure that could have up 100 parts:

<PARTS>
	<PART part_number="331077-05"/>
	<PART part_number="331030-05"/>
	<PART part_number="331552-05"/>
</PARTS>

I also have a pl/sql collection like this:
TYPE myParts IS TABLE OF VARCHAR2(10);

Is it possible to BULK COLLECT each part number attribute into the myParts table?

This doesn't work, but I hope there's something like this that will:

SELECT t.xml.extract('//PART/@part_number/text()').getStringVal()
  BULK COLLECT
  INTO myParts
  FROM catalog t
 WHERE product = 'widget';

Is this possible?

Thanks for the help,
Mike

 

[taupirho]

You don't say which part doesn't work. Is it the selection of the data or the fact that it doesn't like the bulk collect part. Does the simple SELECT below actually produce data

SELECT t.xml.extract('//PART/@part_number/text()').getStringVal()
FROM catalog t
WHERE product = 'widget';



In order to understand recursion, you must first understand recursion.

 

[MikeAJ]

The problem is that the select statement returns ALL of the part number smashed together in one long string. So that statement returns 1 record and 1 column with something like "331077-05331030-05331552-05", and what I'm wanting to do is bulk collect all of part attributes into a collection. Does that make more sense?

Thanks,
Mike

 

[MikeAJ]

I figured out what I can do. If I select the xml into an xmlType, I can run the following query to bulk collect

   SELECT EXTRACT(VALUE(xml), '//PART/@part_number').getStringVal()
     BULK COLLECT  
     INTO v_part_list 
     FROM TABLE(XMLSEQUENCE((v_test.EXTRACT('//PART')))) xml;

Thanks,
Mike