Home » SQL & PL/SQL » SQL & PL/SQL » Parsing XML and populating the table
Parsing XML and populating the table [message #183998] Mon, 24 July 2006 14:57 Go to next message
mrunalhshah
Messages: 15
Registered: June 2006
Junior Member
Can anybody please help me with parsing the following XML and inserting the values into a table

<?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>


Re: Parsing XML and populating the table [message #184011 is a reply to message #183998] Mon, 24 July 2006 21:12 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
What have you tried so far?

Showing you a way using SQL/XML to extract orderID. You can follow a similar approach for the rest.
SQL> DECLARE
  2  x XMLTYPE := XMLTYPE('<?xml version="1.0" encoding="utf-8" ?>
  3  <cXML version="1.2.014" payloadID="PayloadID*TBC*" timestamp="2006-04-20T18:39:09-08:00">
  4  <Header>
  5  <From>
  6  <Credential domain="NetworkId">
  7  <Identity>SX00</Identity>
  8  </Credential>
  9  </From>
 10  <To>
 11  <Credential domain="DUNS">
 12  <Identity>005003264</Identity>
 13  </Credential>
 14  </To>
 15  <Sender>
 16  <Credential domain="DUNS">
 17  <Identity>Biomni</Identity>
 18  <SharedSecret />
 19  </Credential>
 20  <UserAgent>sXGateway</UserAgent>
 21  </Sender>
 22  </Header>
 23  <Request deploymentMode="test">
 24  <OrderRequest>
 25  <OrderRequestHeader orderID="1247" orderType="regular" type="new"
 26  orderDate="2005-12-16T15:27:00" agreementID="IF3 FULLY">
 27  <Total>
 28  <Money currency="USD">0.00</Money>
 29  </Total>
 30  <BillTo>
 31  <Address>
 32  <Name>Address 1</Name>
 33  <PostalAddress>
 34  <DeliverTo>David Pomroy</DeliverTo>
 35  <Street>Address 2</Street>
 36  <Street>Address 3</Street>
 37  <City>Address 4</City>
 38  <State>Address 5</State>
 39  <PostalCode>Address 6</PostalCode>
 40  <Country isoCountryCode="CO" />
 41  </PostalAddress>
 42  </Address>
 43  </BillTo>
 44  <Contact>
 45  <Name/>
 46  </Contact>
 47  <Comments>None</Comments>
 48  </OrderRequestHeader>
 49  <ItemOut quantity="1" lineNumber="1">
 50  <ItemID>
 51  <SupplierPartID>BR549</SupplierPartID>
 52  <SupplierPartAuxiliaryID>BR549</SupplierPartAuxiliaryID>
 53  </ItemID>
 54  <Distribution>
 55  <Accounting name="Interface 3 Data Fields">
 56  <Segment type="Action" id="Add" description="Action" />
 57  <Segment type="Business Owner" id="Michelle Sawyer" description="Business Owner" />
 58  <Segment type="Catalog Item Ordered" id="HRD20677765" description="Catalog Item Ordered" />
 59  <Segment type="Asset Tag" id="0060123" description="Asset Tag" />
 60  </Accounting>
 61  <Charge>
 62  <Money currency="USD">0.00</Money>
 63  </Charge>
 64  </Distribution>
 65  </ItemOut>
 66  <ItemOut quantity="1" lineNumber="2">
 67  <ItemID>
 68  <SupplierPartID>BR549</SupplierPartID>
 69  <SupplierPartAuxiliaryID>BR549</SupplierPartAuxiliaryID>
 70  </ItemID>
 71  <Distribution>
 72  <Accounting name="Interface 3 Data Fields">
 73  <Segment type="Action" id="Add" description="Action" />
 74  <Segment type="Business Owner" id="Michelle Sawyer" description="Business Owner" />
 75  <Segment type="Catalog Item Ordered" id="HRD20688865" description="Catalog Item Ordered" />
 76  <Segment type="Asset Tag" id="0061444" description="Asset Tag" />
 77  </Accounting>
 78  <Charge>
 79  <Money currency="USD">0.00</Money>
 80  </Charge>
 81  </Distribution>
 82  </ItemOut>
 83  </OrderRequest>
 84  </Request>
 85  </cXML>');
 86  BEGIN
 87          FOR i IN (SELECT EXTRACTVALUE(VALUE(t)
 88                          , '/OrderRequest/OrderRequestHeader@orderID') orderID
 89                    FROM   TABLE(XMLSEQUENCE(EXTRACT(x,'cXML/Request/OrderRequest'))) t)
 90          LOOP
 91              DBMS_OUTPUT.PUT_LINE('Order Id: '||i.orderID);
 92          END LOOP;
 93* END;
 94  /
Order Id: 1247

PL/SQL procedure successfully completed.

You might also want to look into DBMS_XML* packages for alternative methods of XML parsing.
Re: Parsing XML and populating the table [message #184190 is a reply to message #184011] Tue, 25 July 2006 12:22 Go to previous message
mrunalhshah
Messages: 15
Registered: June 2006
Junior Member
Its working!!!!
Thanks
Previous Topic: Validate Email
Next Topic: Pass substitution variables from shell script to the SQL
Goto Forum:
  


Current Time: Mon Dec 05 02:28:17 CST 2016

Total time taken to generate the page: 0.06618 seconds