Home » SQL & PL/SQL » SQL & PL/SQL » Parsing XML
Parsing XML [message #184240] Tue, 25 July 2006 19:00 Go to next message
mrunalhshah
Messages: 15
Registered: June 2006
Junior Member
Can anyone please help me extract the following record
For <ItemOut quantity="1" lineNumber="1">
I need Id field of <Segment> tag if type field of <Segment> tag is "Action"?????


Create or Replace Procedure insertxml
is

insert_xml XMLTYPE := XMLTYPE('<?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>');

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(insert_xml, '/cXML/Request/OrderRequest/ItemOut'))) att;

Begin

Select EXTRACTVALUE(value(hdr), '/OrderRequest/OrderRequestHeader/@orderID') into orderID from table(xmlsequence(extract(insert_xml, '/cXML/Request/OrderRequest'))) hdr;

open xml_cursor;

Loop
Fetch xml_cursor into lineNO;
Exit when xml_cursor%NOTFOUND;
Insert into RX (RX_ATTRIBUTE_XML,RX_REQUEST_NUMBER,RX_RECORD_X) values (insert_xml,orderID,lineNO);
End Loop;

close xml_cursor;

End;
Re: Parsing XML [message #184260 is a reply to message #184240] Tue, 25 July 2006 22:49 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
SELECT EXTRACTVALUE(VALUE(t), '/ItemOut/Distribution/Accounting/Segment[@type="Action"]/@id')
FROM TABLE(
       XMLSEQUENCE
       (EXTRACT(insert_xml,
                '/cXML/Request/OrderRequest/ItemOut[@quantity="1" and @lineNumber="1"]'))
           ) t;

[Updated on: Tue, 25 July 2006 23:48]

Report message to a moderator

Re: Parsing XML [message #184262 is a reply to message #184240] Tue, 25 July 2006 22:53 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
If you can, avoid the use of XML attributes for data as they are difficult to manipulate programmatically. I would rather use child elements instead.
Previous Topic: Huge Update
Next Topic: TO_DATE Problem
Goto Forum:
  


Current Time: Sun Dec 04 02:37:30 CST 2016

Total time taken to generate the page: 0.04710 seconds