We help IT Professionals succeed at work.

How do I receive xml post and get it into Oracle Table or Procedure?

aaroncjudd
aaroncjudd asked
on
1,448 Views
Last Modified: 2013-12-19
Our company is going to begin receiving orders from a third party in XML through an http post. My question is how to recieve this XML and get the XML loaded into the database for processing? What are the best practices / technologies for performing this function with Oracle?
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
As steveberzins hinted at:  It all depends on how you want to use the data.  I assume you already have an order processing database and you are looking for a way to get the incoming XML into the existing system?

If so then I'd probably go with steveberzins's suggestion and use the DOM and/or simple XPATH queries to rip apart the doc for simple SQL inserts.

I'd also make sure you are at least on 9.2.0.4.  XML in Oracle didn't really become stable or quick enough to use until that patchset.  I strongly encourage you to get on 10g if you are going to do a lot with XML.  Pretty much the whole XMLDB piece was rewritten in 10g for performance reasons (it was also rewritten between early and late 9i for the same reasons).

Author

Commented:
Thanks to both of you for responding. We are on 9.2.0.6 and I am looking forward to 10g, but it may still be a while for our company to upgrade still.

I probably need to expand on my question (which is usually the case).

I have already written some test procedures to parse the xml and update our contact and order tables. Initially when I wrote this code I saved a test xml file out on the server (unix) and used the following code to insert the entire xml document into an xmlType field in an Oracle table.

-- insert an xml document
insert into judd_xml_test (
  file_name,
  xml_file )
values (
  'XmlTest3',
  xmltype(cmf_xml.read_from_xml_file('/usr/tmp/', 'XmlTest3.xml')) )

The cmf_xml.read_from_xml_file is a fucntion that uses utl_file to read the xml file on the server into a clob and returns the clob. From here I could parse and or select from the document like so:

select extractValue( x.xml_file, '/IomegaOrder/BillingAddress/FirstName' ) FirstName,
  extractValue( x.xml_file, '/IomegaOrder/BillingAddress/Surname' ) LastName,
  extractValue( x.xml_file, '/IomegaOrder/BillingAddress/Company' ) Company,
  extractValue( x.xml_file, '/IomegaOrder/BillingAddress/City' ) City
from judd_xml_test x
where File_Name = 'XmlTest2'

What I need now is to set up the http post where we will recieve the xml stream from our partner and get it into the table.  What would be ideal is if Oracle had a http (listener) that could receive the order xml (which is real time by the way) and pass the xml to a stored procedure. Does Oracle have the ability to do this? Is this the best way?

We have also looked at using Java or .NET to set up a web service. We could recieve the xml and simply pass it to a stored procedure for processing (if that is possible). If Java, we could put the document out on the server and then call a stored procedure to pull the document into the database, as I have done above. Or we could parse the xml externally and update the oracle tables with Java or .NET.

I am looking for the best way to get the xml from our partner and into our database. Also, I am really new to web based stuff, so forgive me if I am using terms incorrectly or am tough to follow.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks slightwv, I am going to work down the CLOB parameter path. That makes the most sense to me and it is nice to here that your company uses the same approach. Also, I am only working with the middle tier tables. We have a different set of procedures that load from these middle tables to Oracles main tables.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.