Oracle PTO / BOM as XML

[MikeAJ]

Hello,

I'm trying to select an Oracle PTO/BOM as an xml document, and I need a little help. Here is my query:

SELECT XMLELEMENT("PTO",
		XMLAGG(
		   XMLELEMENT("OPTION_CLASS", 
			  XMLATTRIBUTES(msi.segment1 as "SEGMENT1",
				 msi.inventory_item_id as "INV_ITEM_ID"),
				 (
					SELECT XMLAGG(
							  XMLELEMENT("PART",
								 XMLFOREST(segment1 "segment1", 
										inventory_item_id "inv_item_id", 
										description "description")
							  )
						   )
					  FROM bom_inventory_components bic1,
						   bom_bill_of_materials bom1,
						   mtl_system_items msi1
					 WHERE bic1.bill_sequence_id = bom1.bill_sequence_id
					   AND bom1.assembly_item_id = msi.inventory_item_id
					   AND msi1.inventory_item_id = bic1.component_item_id
					   AND bic1.bom_item_type = 4
					   AND msi1.organization_id = 2
					   AND (bic1.disable_date IS NULL OR TRUNC(bic1.disable_date) > TRUNC(SYSDATE))
					   AND (bic1.implementation_date IS NOT NULL OR TRUNC(bic1.implementation_date) < TRUNC(SYSDATE))
				 )
		   )
		)
	  ) --.extract('/*').getClobVal()
 FROM bom_inventory_components bic,
	  bom_bill_of_materials bom,
	  mtl_system_items msi
WHERE bic.bill_sequence_id = bom.bill_sequence_id
  AND msi.inventory_item_id = bic.component_item_id
  AND msi.organization_id = 2
  AND (bic.disable_date IS NULL OR TRUNC(bic.disable_date) > TRUNC(SYSDATE))
  AND (bic.implementation_date IS NOT NULL OR TRUNC(bic.implementation_date) < TRUNC(SYSDATE))
  AND bic.bom_item_type = 2
  AND bom.assembly_item_id = 290367;

Which produces:

<PTO>
	<OPTION_CLASS SEGment1="OC FOO ACCESSORIES" INV_ITEM_ID="890319">
		<PART>
			<segment1>598015-08</segment1>
			<inv_item_id>888855</inv_item_id>
		</PART>
		<PART>
			<segment1>339435-05</segment1>
			<inv_item_id>843510</inv_item_id>
        </PART>
	</OPTION_CLASS>
	<OPTION_CLASS SEGment1="OC BAR ACCESSORIES" INV_ITEM_ID="890330">
		<PART>
			<segment1>331130-09</segment1>
			<inv_item_id>584388</inv_item_id>
		</PART>
		<PART>
			<segment1>331130V-0958</segment1>
			<inv_item_id>831508</inv_item_id>
		</PART>
	</OPTION_CLASS>
</PTO>

This works fine if parts are always inside an option class, but sometimes parts exist at the header level. A possible PTO could be:

<PTO>
	<OPTION_CLASS SEGment1="OC FOO ACCESSORIES" INV_ITEM_ID="890319">
		<PART>
			<segment1>598015-08</segment1>
			<inv_item_id>888855</inv_item_id>
		</PART>
		<PART>
			<segment1>339435-05</segment1>
			<inv_item_id>843510</inv_item_id>
        </PART>
	</OPTION_CLASS>
	<OPTION_CLASS SEGment1="OC BAR ACCESSORIES" INV_ITEM_ID="890330">
		<PART>
			<segment1>331130-09</segment1>
			<inv_item_id>584388</inv_item_id>
		</PART>
		<PART>
			<segment1>331130V-0958</segment1>
			<inv_item_id>831508</inv_item_id>
		</PART>
	</OPTION_CLASS>
	<PART>
		<segment1>541130-09</segment1>
		<inv_item_id>951388</inv_item_id>
	</PART>
	<PART>
		<segment1>423130-07</segment1>
		<inv_item_id>357951</inv_item_id>
	</PART>
</PTO>

I've tried to add a second sub query, to specifically select the header level parts, but I keep getting a "not a single group by function" error. Am I on the right track here, and if so, how can I select the parts at the header level? Or, is there a cleaner, recursive way to do this?

Thanks for your help!

 

[taupirho]

Instead of s second sub-query, how about trying a UNION


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