Passing XML as a parameter to a stored procedure [message #184499] |
Wed, 26 July 2006 16:48  |
mrunalhshah
Messages: 15 Registered: June 2006
|
Junior Member |
|
|
Can any one pls help me with thisss!!!!
How do i execute follwing stored procedure
I tried
exec('<?xml version="1.0" encoding="utf-8" ?>
<cXML version="1.2.014" payloadID="PayloadID*TBC*" timestamp="2006-04-20T18:39:09-08:00" xml:lang="en-US">
<Header>
<From>
<Credential domain="NetworkId">
<Identity>SX00</Identity>
</Credential>
</From>
<To>
<Credential domain="DUNS">
<Identity>005003264</Identity>
</Credential>
</To>
<Sender>
<Credential domain="DUNS">
<Identity>Biomni</Identity>
<SharedSecret />
</Credential>
<UserAgent>sXGateway</UserAgent>
</Sender>
</Header>
<Request deploymentMode="test">
<OrderRequest>
<OrderRequestHeader orderID="1247" orderType="regular" type="new" orderDate="2005-12-16T15:27:00" agreementID="IF3 FULLY">
<Total>
<Money currency="USD">0.00</Money>
</Total>
<BillTo>
<Address>
<Name xml:lang="en">Address 1</Name>
<PostalAddress>
<DeliverTo>David Pomroy</DeliverTo>
<Street>Address 2</Street>
<Street>Address 3</Street>
<City>Address 4</City>
<State>Address 5</State>
<PostalCode>Address 6</PostalCode>
<Country isoCountryCode="CO" />
</PostalAddress>
</Address>
</BillTo>
<Contact>
<Name xml:lang="en" />
</Contact>
<Comments>None</Comments>
</OrderRequestHeader>
<ItemOut quantity="1" lineNumber="1">
<ItemID>
<SupplierPartID>BR549</SupplierPartID>
<SupplierPartAuxiliaryID>BR549</SupplierPartAuxiliaryID>
</ItemID>
<Distribution>
<Accounting name="Interface 3 Data Fields">
<Segment type="Action" id="Add" description="Action" />
<Segment type="Business Owner" id="Michelle Sawyer" description="Business Owner" />
<Segment type="Catalog Item Ordered" id="HRD20677765" description="Catalog Item Ordered" />
<Segment type="Asset Tag" id="0060123" description="Asset Tag" />
</Accounting>
<Charge>
<Money currency="USD">0.00</Money>
</Charge>
</Distribution>
</ItemOut>
<ItemOut quantity="1" lineNumber="2">
<ItemID>
<SupplierPartID>BR549</SupplierPartID>
<SupplierPartAuxiliaryID>BR549</SupplierPartAuxiliaryID>
</ItemID>
<Distribution>
<Accounting name="Interface 3 Data Fields">
<Segment type="Action" id="Add" description="Action" />
<Segment type="Business Owner" id="Michelle Sawyer" description="Business Owner" />
<Segment type="Catalog Item Ordered" id="HRD20688865" description="Catalog Item Ordered" />
<Segment type="Asset Tag" id="0061444" description="Asset Tag" />
</Accounting>
<Charge>
<Money currency="USD">0.00</Money>
</Charge>
</Distribution>
</ItemOut>
</OrderRequest>
</Request>
</cXML>'
');
but this didnt worked
THE STORED procedue looks like
create or replace procedure insertxml(insert_xml IN XMLType)
is
xml_param XMLTYPE :=insert_xml;
orderID int(10);
lineNO int(10);
action varchar2(30);
catalog varchar2(30);
Cursor xml_cursor is Select EXTRACTVALUE(value(att), '/ItemOut/@lineNumber') from table(xmlsequence(extract(xml_param,
'/cXML/Request/OrderRequest/ItemOut'))) att;
Begin
Select EXTRACTVALUE(value(hdr), '/OrderRequest/OrderRequestHeader/@orderID') into orderID from table(xmlsequence(extract(xml_param, '/cXML/Request/OrderRequest'))) hdr;
open xml_cursor;
Loop
Fetch xml_cursor into lineNO;
Exit when xml_cursor%NOTFOUND;
SELECT EXTRACTVALUE(VALUE(t), '/ItemOut/Distribution/Accounting/Segment[@type="Action"]/@id') into action
FROM TABLE(XMLSEQUENCE(EXTRACT(xml_param, '/cXML/Request/OrderRequest/ItemOut[@lineNumber="' || lineNO || '"]'))) t;
SELECT EXTRACTVALUE(VALUE(x), '/ItemOut/Distribution/Accounting/Segment[@type="Catalog ItemOrdered"]/@id') into catalog
FROM TABLE(XMLSEQUENCE(EXTRACT(xml_param, '/cXML/Request/OrderRequest/ItemOut[@lineNumber="' || lineNO || '"]'))) x;
Insert into RX(RX_ATTRIBUTE_XML,RX_REQUEST_NUMBER,RX_RECORD_X,RX_ACTION,RX_CATALOG) values (xml_param,orderID,lineNO,action,catalog);
End Loop;
close xml_cursor;
End;
|
|
|
Re: Passing XML as a parameter to a stored procedure [message #184581 is a reply to message #184499] |
Thu, 27 July 2006 02:48   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You seem to be trying to execute the XML rather than the procedure.
Interesting approach but, I suspect, doomed to failure.
You want to try
exec insertxml('<lots of lovely XML goodness>');
or
begin
insertxml('<lots of lovely XML goodness>');
end;
As a hint, I'd make a code change.
You have
create or replace procedure insertxml(insert_xml IN XMLType)
is
xml_param XMLTYPE :=insert_xml;
...
Now, if the XML isn't valid, this is going to error before you even get to the BEGIN, and so it's going to avoid the EXCEPTION block in this proedure (which I'm sure you just ommited to keep your post small - no one woud really write procedures without ANY exception handling, would they?) and propogate back into the calling procedure. This sort of error can be a swine to pin down.
I'd just define xml_param in the DECLARE section, and assign it in the body of the code.
|
|
|
|