Need help with writing from XML to a SQL Server database (detailed)

Question

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/"&gt;
  <SOAP-ENV:Body>
    <m:entrypoint_getSettlementsOperationResponse xmlns:m="http://j2ee.netbeans.org/wsdl/BorgunTestBPEL/entrypoint_getSettlements"&gt;
      <part1>
        <GetSettlementsByMerchantResponse xmlns="http://Borgun.Services.Gateway/2010/04/Settlement"&gt;
          <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"&gt;
            <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.

Answer 1

you will want to use JAXB. find the xjc tool in the /bin (this should be in your path already, then use it on the schema at http://schemas.xmlsoap.org/soap/envelope/. you should first download the schema, then run
xjc -d <directory> <schema-name>
the directory should be a source folder (e.g. src) and the schema-name is the filename of wherever you downloaded the schema to. this will generate a bunch of source files which correspond to the schema. you can then use the JAXB Unmarshaller tool like this:
JAXBContext ctx = JAXBContext.newInstance(Envelope.class.getPackage().getName();
Unmarshaller u = ctx.createUnmarshaller();
JAXBElement<Envelope> root = (JAXBElement<Envelope>) u.unmarshall(xmlStr);
Envelope envelope = root.getValue();

envelope will represent the root of the data structure which you will somehow have to write to SQL (I don't know the answer to that). xmlStr needs to be a StringBuffer with the contents of the xml. the docs for JAXB are at java.sun.com/javase/6/docs/api/javax/xml/bind/package-summary.html