Home » SQL & PL/SQL » SQL & PL/SQL » Passing XML as a parameter to a stored procedure
Passing XML as a parameter to a stored procedure [message #184499] Wed, 26 July 2006 16:48 Go to next message
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 Go to previous messageGo to next message
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.
Re: Passing XML as a parameter to a stored procedure [message #184737 is a reply to message #184581] Thu, 27 July 2006 11:37 Go to previous message
mrunalhshah
Messages: 15
Registered: June 2006
Junior Member
Thankssss!!!!
The second options is working fine. The only change I had to do is


Begin
insertxml(XMLTYPE('<XML>');
End;

Previous Topic: executing a .sqlplus
Next Topic: Problem with a job in Replication Environment
Goto Forum:
  


Current Time: Sun Dec 04 19:08:20 CST 2016

Total time taken to generate the page: 0.08818 seconds