Home » Developer & Programmer » JDeveloper, Java & XML » Help me Please
 Help me Please [message #162476] |
Fri, 10 March 2006 07:33  |
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  |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 04:20:57 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
