 ORA-19025: EXTRACTVALUE returns value of only [message #161183] |
Thu, 02 March 2006 07:38  |
askshirsagar
Messages: 9
Registered: February 2006
Location: India
|
Junior Member |
 
|
|
Dear Gurus,
Please help in following code, I am inserting records from xml to database and there are parent and child tables involved. I can successfully inserting records into parent table but while insert into child table (SDPOSTCODE and SDSUPPLIERPOST) I am getting an error ORA-19025: EXTRACTVALUE returns value of only
one node. We are having ORACLE 9.2.0.3.0 also please let me know how to retrive and insert CreateDateStamp and LastModifiedDateStamp value
Thanks a lot for your help
Regards,
Avinash
DECLARE
x XMLTYPE := XMLTYPE('
<Suppliers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/Suppliers.xsd"
CreateDateStamp="2006-01-12T07:03:43.0Z" LastModifiedDateStamp="2006-01-12T07:03:43.0Z">
<Supplier CreateDateStamp="2006-01-12T06:00:39.0Z" LastModifiedDateStamp="2006-01-12T06:00:39.0Z">
<SourceSystem>Maconomy</SourceSystem>
<SupplierName>Berminghams Yorkshire</SupplierName>
<Organisation>
<OrganisationName>Berminghams</OrganisationName>
</Organisation>
</Supplier>
<Supplier CreateDateStamp="2006-01-12T06:00:39.0Z" LastModifiedDateStamp="2006-01-12T06:00:39.0Z">
<SourceSystem>Maconomy</SourceSystem>
<SupplierName>Berminghams East Midlands</SupplierName>
<Organisation>
<OrganisationName>Berminghams</OrganisationName>
</Organisation>
</Supplier>
<Supplier CreateDateStamp="2006-01-12T06:00:39.0Z" LastModifiedDateStamp="2006-01-12T06:00:39.0Z">
<SourceSystem>Maconomy</SourceSystem>
<SupplierName>DCE East Anglia</SupplierName>
<Organisation>
<OrganisationName>DCE</OrganisationName>
</Organisation>
</Supplier>
<Supplier CreateDateStamp="2006-01-12T06:00:39.0Z" LastModifiedDateStamp="2006-01-12T06:00:39.0Z">
<SourceSystem>Maconomy</SourceSystem>
<SupplierName>McNicholas plc South East</SupplierName>
<Organisation>
<OrganisationName>McNicholas plc</OrganisationName>
</Organisation>
<SupplierContractCode>MN002</SupplierContractCode>
<PostCodes>
<PostCode>CT11 7NY</PostCode>
<PostCode>CT11 7NZ</PostCode>
<PostCode>CT11 7PA</PostCode>
<PostCode>CT11 7PB</PostCode>
<PostCode>CT11 7PD</PostCode>
<PostCode>CT11 7PE</PostCode>
<PostCode>CT11 7PF</PostCode>
<PostCode>CT11 7PG</PostCode>
<PostCode>CT11 7PH</PostCode>
<PostCode>CT11 7PJ</PostCode>
<PostCode>CT11 7PL</PostCode>
<PostCode>CT11 7PP</PostCode>
<PostCode>CT11 7PQ</PostCode>
</PostCodes>
</Supplier>
<Supplier CreateDateStamp="2006-01-12T06:00:39.0Z" LastModifiedDateStamp="2006-01-12T06:00:39.0Z">
<SourceSystem>Maconomy</SourceSystem>
<SupplierName>Alcho North Wales</SupplierName>
<Organisation>
<OrganisationName>Alcho</OrganisationName>
</Organisation>
</Supplier>
</Suppliers>');
BEGIN
FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/Supplier/SupplierName') T_SupplierName,
EXTRACTVALUE(VALUE(t), '/Supplier/SourceSystem') T_SourceSystem,
EXTRACTVALUE(VALUE(t), '/Supplier/SupplierContractCode') T_SupplierContractCode,
EXTRACT(VALUE(t), '/Supplier/PostCodes') PostCodes_xml
FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/Suppliers/Supplier'))) t )
LOOP
INSERT INTO SDSUPPLIER VALUES (SEQ_SUPPLIER_ID.NEXTVAL,
i.T_SupplierName,
Null,
i.T_SupplierContractCode,
'DM',
SYSDATE,
'DM',
SYSDATE,
0,
NULL,
NULL);
FOR j IN ( SELECT EXTRACTVALUE(VALUE(t), '/PostCodes/PostCode') T_PostCode
FROM TABLE(XMLSEQUENCE(EXTRACT(i.PostCodes_xml, '/PostCodes'))) t )
LOOP
DBMS_OUTPUT.PUT_LINE('Post Code ' || j.T_PostCode);
INSERT INTO SDPOSTCODE values (j.T_PostCode,NULL);
INSERT INTO SDSUPPLIERPOSTCODE values ( SEQ_SUPPLIER_ID.CURRVAL,j.T_PostCode,NULL);
END LOOP;
END LOOP;
END;
/
|
|
|
|
| Re: ORA-19025: EXTRACTVALUE returns value of only [message #161214 is a reply to message #161183] |
Thu, 02 March 2006 12:08  |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Hello.
You're too "high" a level in your xpath extract within XMLSEQUENCE,
since your initial extract is to /PostCodes, i.e.
EXTRACT(VALUE(t), '/Supplier/PostCodes') PostCodes_xml
But, then you do :
FOR j IN ( SELECT EXTRACTVALUE(VALUE(t), '/PostCodes/PostCode') T_PostCode
FROM TABLE(XMLSEQUENCE(EXTRACT(i.PostCodes_xml, '/PostCodes'))) t )
What you should be doing is :
FOR j IN ( SELECT EXTRACTVALUE(VALUE(t), '/PostCode') T_PostCode
FROM TABLE(XMLSEQUENCE(EXTRACT(i.PostCodes_xml, '/PostCodes/PostCode'))) t )
To ensure that XMLSEQUENCE correctly corresponds to each PostCode
element, not to their parent.
Rgds
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
