Home » Developer & Programmer » JDeveloper, Java & XML » Help me Please
icon4.gif  Help me Please [message #162476] Fri, 10 March 2006 07:33 Go to next message
askshirsagar
Messages: 9
Registered: February 2006
Location: India
Junior Member

Dear Gurus,

Please help me. Following is my XML and PLS and records has to be inserted into SDITEM (Parent Table) and SDITEMBOMITEM (Child Table) table. I have successfully loaded records
into SDITEM table but please let me know where I am doing wrong while inserting records into SDITEMBOMITEM table.

1. After looking XML file, is BOMITEMNAME and Quantity are parent-child relation


DECLARE
x XMLTYPE := XMLTYPE('<Items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/Items.xsd">
<Item>
<SourceSystem>Maconomy</SourceSystem>
<ItemName>MLNCHMP3552101315IN-1</ItemName>
<ItemDataDescription>Mains Connection to new property >HDPE M/P 355mm dia pipe SDR 21in Open trench (lay only) connx to 315mm exist mains with an Insert tee (one item of)</ItemDataDescription>
<ItemBOMItems>
<ItemBOMItem>
<BOMItem>
<BOMItemName>1375</BOMItemName>
</BOMItem>
<ItemBOMItemQuantity>10</ItemBOMItemQuantity>
</ItemBOMItem>
<ItemBOMItem>
<BOMItem>
<BOMItemName>2G1ER</BOMItemName>
</BOMItem>
<ItemBOMItemQuantity>20</ItemBOMItemQuantity>
</ItemBOMItem>
<ItemBOMItem>
<BOMItem>
<BOMItemName>3382</BOMItemName>
</BOMItem>
<ItemBOMItemQuantity>10</ItemBOMItemQuantity>
</ItemBOMItem>
</ItemBOMItems>
</Item>
</Items>');
BEGIN
FOR i IN ( SELECT
EXTRACTVALUE(VALUE(t), '/Item/SourceSystem') T_SourceSystem,
EXTRACTVALUE(VALUE(t), '/Item/ItemName') T_ItemName,
EXTRACTVALUE(VALUE(t), '/Item/ItemDataDescription') T_ItemDataDescription,
EXTRACT(VALUE(t), '/Item/ItemBOMItems') BOMItem_xml
FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/Items/Item'))) t )
LOOP
INSERT INTO SDITEM ( ITEMNAME, ITEMDATADESCRIPTION, SOURCESYSTEM ) VALUES
( i.T_ItemName,i.T_ItemDataDescription ,i.T_SourceSystem);

FOR k IN ( SELECT EXTRACTVALUE(VALUE(t), '/BOMItem/BOMItemName') T_BOMItemName,
EXTRACT(VALUE(t), '/Item/ItemBOMItems') BOMQuantityItem_xml
FROM TABLE(XMLSEQUENCE(EXTRACT(i.BOMItem_xml, '/ItemBOMItems/ItemBOMItem/BOMItem'))) t )
LOOP
DBMS_OUTPUT.PUT_LINE('BOM Name ' || k.T_BOMItemName);
END LOOP;
FOR l IN ( SELECT EXTRACTVALUE(VALUE(t), '/ItemBOMItem/ItemBOMItemQuantity') T_ItemBOMItemQuantity
FROM TABLE(XMLSEQUENCE(EXTRACT(i.BOMItem_xml, '/ItemBOMItems/ItemBOMItem'))) t )
LOOP
DBMS_OUTPUT.PUT_LINE('BOM Quantity ' || l.T_ItemBOMItemQuantity);
END LOOP;
END LOOP;
END;
/



OUTPUT is Coming like this

BOM Name 1375
BOM Name 2G1ER
BOM Name 3382
BOM Quantity 10
BOM Quantity 20
BOM Quantity 10


BUT I WANT IT SHOULD BE

BOM Name 1375
BOM Quantity 10
BOM Name 2G1ER
BOM Quantity 20
BOM Name 3382
BOM Quantity 10

Thanks for your help.

Rgds,
Avinash
Re: Help me Please [message #162597 is a reply to message #162476] Sat, 11 March 2006 13:40 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

You've just got to reorganise your code a little, i.e. you're not getting them in the right order because you've got two seperate LOOPs, what you need is something like :
SQL> DECLARE
  2  x XMLTYPE := XMLTYPE('<Items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamesp
aceSchemaLocation="http://www.oracle.com/Items.xsd">
  3  <Item>
  4  <SourceSystem>Maconomy</SourceSystem>
  5  <ItemName>MLNCHMP3552101315IN-1</ItemName>
  6  <ItemDataDescription>Mains Connection to new property >HDPE M/P 355mm dia pipe SDR 21in Open tr
ench (lay only) connx to 315mm exist mains with an Insert tee (one item of)</ItemDataDescription>
  7  <ItemBOMItems>
  8  <ItemBOMItem>
  9  <BOMItem>
 10  <BOMItemName>1375</BOMItemName>
 11  </BOMItem>
 12  <ItemBOMItemQuantity>10</ItemBOMItemQuantity>
 13  </ItemBOMItem>
 14  <ItemBOMItem>
 15  <BOMItem>
 16  <BOMItemName>2G1ER</BOMItemName>
 17  </BOMItem>
 18  <ItemBOMItemQuantity>20</ItemBOMItemQuantity>
 19  </ItemBOMItem>
 20  <ItemBOMItem>
 21  <BOMItem>
 22  <BOMItemName>3382</BOMItemName>
 23  </BOMItem>
 24  <ItemBOMItemQuantity>10</ItemBOMItemQuantity>
 25  </ItemBOMItem>
 26  </ItemBOMItems>
 27  </Item>
 28  </Items>');
 29  BEGIN
 30  FOR i IN ( SELECT
 31               EXTRACTVALUE(VALUE(t), '/Item/SourceSystem') T_SourceSystem,
 32               EXTRACTVALUE(VALUE(t), '/Item/ItemName') T_ItemName,
 33               EXTRACTVALUE(VALUE(t), '/Item/ItemDataDescription') T_ItemDataDescription,
 34               EXTRACT(VALUE(t), '/Item/ItemBOMItems') BOMItem_xml
 35             FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/Items/Item'))) t )
 36  LOOP
 37  --  INSERT INTO SDITEM ( ITEMNAME, ITEMDATADESCRIPTION, SOURCESYSTEM ) VALUES
 38  --  ( i.T_ItemName,i.T_ItemDataDescription ,i.T_SourceSystem);
 39    FOR k IN ( SELECT EXTRACTVALUE(VALUE(t), '/ItemBOMItem/ItemBOMItemQuantity') ItemBOMItemQuant
ity,
 40                      EXTRACT(VALUE(t), '/ItemBOMItem/BOMItem') BOMItem_xml
 41                 FROM TABLE(XMLSEQUENCE(EXTRACT(i.BOMItem_xml, '/ItemBOMItems/ItemBOMItem'))) t )

 42    LOOP
 43      FOR l IN ( SELECT EXTRACTVALUE(VALUE(t), '/BOMItem/BOMItemName') BOMItemName
 44                   FROM TABLE(XMLSEQUENCE(EXTRACT(k.BOMItem_xml, '/BOMItem'))) t )
 45      LOOP
 46        dbms_output.put_line('BOMItem : ' || l.BOMItemName);
 47      END LOOP;
 48      dbms_output.put_line('Quantity : ' || k.ItemBOMItemQuantity);
 49    END LOOP;
 50  END LOOP;
 51  END;
 52  /
BOMItem : 1375
Quantity : 10
BOMItem : 2G1ER
Quantity : 20
BOMItem : 3382
Quantity : 10

PL/SQL procedure successfully completed.

Rgds
Re: Help me Please [message #162665 is a reply to message #162597] Sun, 12 March 2006 23:23 Go to previous message
askshirsagar
Messages: 9
Registered: February 2006
Location: India
Junior Member

A Guru ,

Great.... thanks for pointing out my mistake.

Thanks for your help.

Rgds,
Avinash
Previous Topic: how register schema xml in oracle 10g
Next Topic: How to fetch tag data from XML string?
Goto Forum:
  


Current Time: Thu Mar 28 15:47:54 CDT 2024