Need help with writing from XML to a SQL Server database (detailed)
- by fogedi
I'm a bit of a newbie with XML and WebServices and stuff like that.
I'm working on a project using GlassFish OpenESB to schedule a process to get some information from a webservice and then store in a database.
The criteria is basically that i have to use GlassFish OpenESB or EJB modules where i can expose webservices or something along those lines, AND i have to use SQL Server 2005.
So far I've been able to talk to the webservice: and receive something along those lines
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.xmlsoap.org/soap/envelope/ http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Body>
<m:entrypoint_getSettlementsOperationResponse xmlns:m="http://j2ee.netbeans.org/wsdl/BorgunTestBPEL/entrypoint_getSettlements">
<part1>
<GetSettlementsByMerchantResponse xmlns="http://Borgun.Services.Gateway/2010/04/Settlement">
<GetSettlementsByMerchantResult xmlns:a="http://schemas.datacontract.org/2004/07/Borgun.Library.Common" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:msgns="http://Borgun.Services.Gateway/2010/04/Settlement" xmlns:ns0="http://j2ee.netbeans.org/wsdl/BorgunTestBPEL/entrypoint_getSettlements">
<a:CreditCardSettlement>
<a:amexAmount>XXX</a:amexAmount>
<a:amount>XXXX</a:amount>
<a:batches>
<a:CreditCardBatch>
<a:batchdate>xxx</a:batchdate>
<a:batchnumber>XXXX</a:batchnumber>
<a:currencyCode>xxxx</a:currencyCode>
<a:merchantnumber>xxxx</a:merchantnumber>
<a:settlementRunNumber>xx4</a:settlementRunNumber>
<a:settlementdate>2010-04-06T00:00:00</a:settlementdate>
<a:slips>2</a:slips>
<a:sum>xxxx</a:sum>
</a:CreditCardBatch>
<a:CreditCardBatch>
<a:batchdate>xxx</a:batchdate>
<a:batchnumber>xxxxx</a:batchnumber>
<a:currencyCode>xxxx</a:currencyCode>
<a:merchantnumber>xxxx</a:merchantnumber>
<a:settlementRunNumber>xxxx</a:settlementRunNumber>
<a:settlementdate>xxxx</a:settlementdate>
<a:slips>x</a:slips>
<a:sum>xxx</a:sum>
</a:CreditCardBatch>
</a:batches>
<a:commission>xx</a:commission>
<a:currencyCode>xxx</a:currencyCode>
<a:deduction>-xxx</a:deduction>
<a:deductionItems>
<a:CrediCardSettlementDeduction>
<a:amount>-xxx</a:amount>
<a:code>VIÐSKF</a:code>
<a:currencyCode>ISK</a:currencyCode>
<a:merchantnumber>xxx</a:merchantnumber>
<a:settlementrunnumber>xxx</a:settlementrunnumber>
<a:text>Afsláttur v/ekorta</a:text>
</a:CrediCardSettlementDeduction>
<a:CrediCardSettlementDeduction>
<a:amount>-335.00</a:amount>
<a:code>ÁLAGKREK</a:code>
<a:currencyCode>ISK</a:currencyCode>
<a:merchantnumber>xxx</a:merchantnumber>
<a:settlementrunnumber>xxx</a:settlementrunnumber>
<a:text>xxx</a:text>
</a:CrediCardSettlementDeduction>
</a:deductionItems>
</a:CreditCardSettlement>
</GetSettlementsByMerchantResult>
</GetSettlementsByMerchantResponse>
</part1>
</m:entrypoint_getSettlementsOperationResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
I have access to the SQL Server 2005 server which is remote and i know i can insert into it but given that now i have a one-to-many relationship i want to be able to rollback if something fails.
So in short how can I insert from this XML into the DB preferably without manually walking through the XML tree?
I'm pretty sure I'm supposed to be able to use Entity and Session Beans or maybe JAXB bindings but I'm simply not being successful.
One of the reasons might have something to do with the fact that the soap response contains an array of CreditCardSettlements and each of which contains an array of Batches and DeductionItems
It would be best if someone can help me do this via a BPEL in GlassFish OpenESB but any hint at a java solution is much appreciated.
