1 2 Previous Next 23 Replies Latest reply: Dec 1, 2009 12:10 AM by 595538 RSS

    Data enrichment in Oracle ESB

    548158
      Hi,

      Is anyone able to provide some I am looking for some (hopefully detailed) guidance please on how to combine datasets in an ESB routing service.

      For example: Where you have an incoming payload and you wish to (again for example) call a Web service using an item of data from the incoming payload. Once you have obtained the Web service result you want to append the result to the original incoming data (in order to enrich the payload) before you pass it on to an adapter service.

      There is an example available that does this (albeit they call a database and not a Web service) but it is not obvious in the example as to how it is achieved. I understand it may involve XSL param and copy-of commands but XSLT is new to me?

      Many thanks in advance.

      Regards
      Allan.
        • 1. Re: Data enrichment in Oracle ESB
          Dave Berry
          There are 1 major thing that differentiates this use case from plain old req/response. To forward a response as shown in the samples on otn.oracle.com/goto/esb, you have to create an inbound routing service that does not provide a repsonse which enables you to choose a new target operation for the response of an outbound req/response. To do this, you have several options.

          1) use the RS from an inbound 1way adapter such as File, JMS, DB etc...
          2) create your routing service using a schema and provide only a req schema with no reply schema
          3) create your RS from an existing 1way wsdl that does not provide a reply

          Once you have this in place, you can add a routing rule to a req/resp service from the 1way RS. Then expand the exisiting routing rule and click on the target operation on the right to invoke a different service with the response.

          The final trick is when you create an xsl for the response, you select the option to include the original payload which creates a $ESBREQUEST XSL variable for you to use to access the orginal reuest. This whole use case is delivered in the "Forward and Route Response" sample.
          • 2. Re: Data enrichment in Oracle ESB
            548158
            Many thanks for the reply Dave, Much appreciated.

            Interestingly enough when you look at the example use case you cannot see the XSL diagrammatically - you can only view the source. This implies that the XSL file might have been manually amended?

            Anyway, I'm sure I am very close - here's where I am...

            I have a File Adapter service polling for a file which has created a Routing Service for the File Poller (as you'd expect).

            I have a SOAP service defined using the WSDL from my .Net Web service.

            I have created a Routing Service (for my Web service) that stands between the Polling File Adapter Routing Service and my .Net Web service. The interface for this Routing Service is the same as the incoming Polling File Adapter interface only it contains one additional field (to be filled/enriched by the .Net Web service reply).

            On the every XSL return I have opted to "include the request in the reply".

            As you rightly point out this has created for me (in the 2nd XSL document within the Routing rule for the Routing Service for the polling File Adapter) a parameter called
            <xsl:param name="ESBREQUEST"/>

            So I can get this far but what is <xsl:param name="ESBREQUEST"/> doing for me?
            What's the final bit? How do I use it to get to the original incoming data I seem to have lost? All I seem to have access to is the Web service reply.

            The document looks like this:
            <xsl:stylesheet version="1.0"
            xmlns:bpws="http://schemas.xmlsoap.org/ws/2003/03/business-process/"
            xmlns:plt="http://schemas.xmlsoap.org/ws/2003/05/partner-link/"
            xmlns:ehdr="http://www.oracle.com/XSL/Transform/java/oracle.tip.esb.server.headers.ESBHeaderFunctions"
            xmlns:jca="http://xmlns.oracle.com/pcbpel/wsdl/jca/"
            xmlns:ns0="http://www.w3.org/2001/XMLSchema"
            xmlns:hwf="http://xmlns.oracle.com/bpel/workflow/xpath"
            xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"
            xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
            xmlns:ora="http://schemas.oracle.com/xpath/extension"
            xmlns:out1="http://TargetNamespace.com/ServiceName"
            xmlns:tns="http://oracle.com/esb/namespaces/DefaultSystem"
            xmlns:ns1="http://xmlns.oracle.com/pcbpel/adapter/file/WriteFile/"
            xmlns:ids="http://xmlns.oracle.com/bpel/services/IdentityService/xpath"
            xmlns:orcl="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc"
            xmlns:hdr="http://xmlns.oracle.com/pcbpel/adapter/file/"
            xmlns:imp1="http://TargetNamespace.com/WriteFile"
            exclude-result-prefixes="xsl ns0 out1 tns plt jca ns1 hdr imp1 bpws ehdr hwf xp20 ora ids orcl">
            <xsl:param name="ESBREQUEST"/>
            <xsl:template match="/">
            <imp1:Root-Element>
            <imp1:FullEmployee>
            <imp1:FirstName>
            <xsl:value-of select="/out1:Root-Element/out1:employee/out1:FirstName"/>
            </imp1:FirstName>
            <imp1:LastName>
            <xsl:value-of select="/out1:Root-Element/out1:employee/out1:LastName"/>
            </imp1:LastName>
            <imp1:Email>
            <xsl:value-of select="/out1:Root-Element/out1:employee/out1:Email"/>
            </imp1:Email>
            <imp1:PhoneNumber>
            <xsl:value-of select="/out1:Root-Element/out1:employee/out1:PhoneNumber"/>
            </imp1:PhoneNumber>
            <imp1:LocationCode>
            <xsl:value-of select="/out1:Root-Element/out1:employee/out1:LocationCode"/>
            </imp1:LocationCode>
            <imp1:GrossSalary>
            <xsl:value-of select="/out1:Root-Element/out1:employee/out1:GrossSalary"/>
            </imp1:GrossSalary>
            <imp1:NetSalary>
            <xsl:value-of select="/out1:Root-Element/out1:employee/out1:NetSalary"/>
            </imp1:NetSalary>
            </imp1:FullEmployee>
            </imp1:Root-Element>
            </xsl:template>
            </xsl:stylesheet>

            Thanks again
            Allan.
            • 3. Re: Data enrichment in Oracle ESB
              Dave Berry
              As you noted, the copy-of xsl operation is not supported by the UI so you have to manually edit the xsl source. To use the $ESBREQUEST variable, do something like below as shown in the existing sample.

              <imp1:NetSalary>
              <xsl:copy-of select="$ESBREQUEST/"/>
              </imp1:NetSalary>
              • 4. Re: Data enrichment in Oracle ESB
                548158
                Thanks Dave,

                I followed your advice to the letter but received an error. However, not dismayed (because I know I'm close) I looked again at the example use case and noticed they put the copy-of line here:

                <xsl:param name="ESBREQUEST"/>
                <xsl:template match="/">
                <xsl:for-each select="/out1:ShippingAddressCollection">
                <inp1:Shipment>
                <xsl:copy-of select="$ESBREQUEST"/>
                <inp1:ShippingAddress>
                <inp1:Address>
                <xsl:value-of select="out1:ShippingAddress/out1:address"/>
                </inp1:Address>
                     ...etc

                So I put it in a similar place in my XSL file. It still produced an error but this time the error was more interesting in that the error payload reported this

                Payload

                <imp1:Root-Element xmlns:imp1="http://TargetNamespace.com/WriteFile">
                <imp1:FullEmployee>
                <out1:Root-Element xmlns:out1="http://TargetNamespace.com/ServiceName">
                <out1:employee>
                <out1:FirstName>Bobby</out1:FirstName>
                <out1:LastName>Dazzle</out1:LastName>
                <out1:Email>bob.daz@google.co.uk</out1:Email>
                <out1:PhoneNumber>01412223456</out1:PhoneNumber>
                <out1:LocationCode>Glasgow</out1:LocationCode>
                <out1:GrossSalary>20000</out1:GrossSalary>
                </out1:employee>
                </out1:Root-Element>
                <imp1:FirstName/>
                <imp1:LastName/>
                <imp1:Email/>
                <imp1:PhoneNumber/>
                <imp1:LocationCode/>
                <imp1:GrossSalary/>
                <imp1:NetSalary>16500</imp1:NetSalary>
                </imp1:FullEmployee>
                </imp1:Root-Element>

                So all the out1 elements contained the data from my incoming request while the imp1 bit at the end had the NetSalary value from my Web service request.

                At the risk of being a complete nuisance (but this is a learning curve for me!) does this tell you more than it tells me about how to correctly manipulate the XSL document listed in my previous response in order to get all the data together?

                Thanks for your help and patience
                • 5. Re: Data enrichment in Oracle ESB
                  Dave Berry
                  One thing, you can use value-of but it requires a few more selects. copy-of is used to access an entire tree while value-of only works for a single leaf element.

                  Given that, using FirstName as an example, try this.

                  <imp1:FirstName>
                  <xsl:value-of select="$ESBREQUEST/out1:Root-Element/out1:employee/out1:FirstName"/>
                  </imp1:FirstName>

                  When you get it working, please post the entire use case and example back to this thread for others to see.
                  • 6. Re: Data enrichment in Oracle ESB
                    548158
                    Hi Dave,

                    Rest assured, as soon as this is resolved complete details will be posted...

                    Of course, ideally Oracle should have posted a full and detailed set of guidelines with their own use case which may have helped?

                    In theory, what we are doing here should be simple and intuitive due to it being a common integration requirement? Must even be common within BPEL which I understand uses the same XSL mapper.

                    Anyway, back to the problem at hand...

                    I tried this line as suggested...

                    <xsl:value-of select="$ESBREQUEST/out1:Root-Element/out1:employee/out1:FirstName"/>

                    This didn't produce an error but resulted in the CSV output (from the File Adapter) looking like this

                    ,,,,,,16500

                    The expected output of course should look like this...

                    Bobby,Dazzle,bob.daz@google.co.uk,01412223456,Glasgow,20000,16500

                    I also tried this line (and many other variations)....

                    <xsl:value-of select="$ESBREQUEST/employee/FirstName"/>


                    Again, no error but the same result as before

                    ,,,,,,16500

                    I'm stumped!

                    Best regards
                    Allan.
                    • 7. Re: Data enrichment in Oracle ESB
                      552064
                      I have been able to make the ESBREQUEST parameter work successfully.

                      1. Assume the $ESBREQUEST is the root ELEMENT, not document of the request XML. If your request document looks as follows:

                      <a:requestXML attr1="asdf" xmlns:a="myURI">
                      <a:foo/>
                      <a:bar/>
                      </a:requestXML>

                      Then you will access these fields from the parameter using the following XPaths:
                      <xsl:value-of select="$ESBREQUEST/@attr1"/>
                      <xsl:value-of select="$ESBREQUEST/a:foo"/>
                      <xsl:value-of select="$ESBREQUEST/a:bar"/>

                      <xsl:apply-templates select="$ESBREQUEST"/> will match on <xsl:template match="a:requestXML"/>.

                      2. You need to ensure that your service is actually receiving the request XML in the response transformation. Open the service ".esbsvc" file using a XML/text editor. Look for the <replyHandler> in the operation you are worknig on. It should look something like this:

                      <replyHandler>
                      <transformation attachRequestPayload="false">
                      <xslFileURL>myTransformation.xslt</xslFileURL>
                      </transformation>
                      </replyHandler>

                      If the attribute attachRequestPayload is set to false, the ESBREQUEST parameter will not work. You need to set this to true.

                      On this note this is a major gripe of mine with JDeveloper. The only time that you may set this option is if you use the visual XSLT designer and only on creation. If you miss it the first time round or use a pre-authored stylesheet you can't set this through the HMI (or at least it seems that way). Thus I have to hack the .esbsvc file.

                      As far as your stylesheet is concerned, have you declared what prefix will be used with your namespace in the return transformation stylesheet for the request XML? You must use this prefix appropriately as the XML in the ESBREQUEST is typed.

                      Hope this helps.

                      Cheers,
                      Alastair
                      • 8. Re: Data enrichment in Oracle ESB
                        548158
                        Hi Alastair,

                        It's great to hear that you have got this to work.
                        I now have mixed feelings of "great this can be done" and "Blimey, why can't I get this to work!" :-)

                        I have checked the .esbsvc file and the attachRequestPayload is set to true

                        The XSL mapping file I have looks as follows with out1: being the left hand side of the mapper for the RS and imp1 being the right hand side of the mapper for my File Adapter service, "FileWrite".

                        <xsl:param name="ESBREQUEST"/>
                        <xsl:template match="/">
                        <imp1:Root-Element>
                        <imp1:FullEmployee>
                        <imp1:FirstName>
                        <xsl:value-of select="/out1:Root-Element/out1:employee/out1:FirstName"/>
                        </imp1:FirstName>
                        <imp1:LastName>
                        <xsl:value-of select="/out1:Root-Element/out1:employee/out1:LastName"/>
                        </imp1:LastName>
                        <imp1:Email>
                        <xsl:value-of select="/out1:Root-Element/out1:employee/out1:Email"/>
                        </imp1:Email>
                        <imp1:PhoneNumber>
                        <xsl:value-of select="/out1:Root-Element/out1:employee/out1:PhoneNumber"/>
                        </imp1:PhoneNumber>
                        <imp1:LocationCode>
                        <xsl:value-of select="/out1:Root-Element/out1:employee/out1:LocationCode"/>
                        </imp1:LocationCode>
                        <imp1:GrossSalary>
                        <xsl:value-of select="/out1:Root-Element/out1:employee/out1:GrossSalary"/>
                        </imp1:GrossSalary>
                        <imp1:NetSalary>
                        <xsl:value-of select="/out1:Root-Element/out1:employee/out1:NetSalary"/>
                        </imp1:NetSalary>
                        </imp1:FullEmployee>
                        </imp1:Root-Element>
                        </xsl:template>
                        </xsl:stylesheet>

                        I changed the file to this (basically preceding the above selects with a reference to the ESBREQUEST variable) - is this right as didn't work? You'll see I left the last select as it was because this select works and outputs the NetSalary value as expected.

                        <xsl:param name="ESBREQUEST"/>
                        <xsl:template match="/">
                        <imp1:Root-Element>
                        <imp1:FullEmployee>
                        <imp1:FirstName>
                        <xsl:value-of select="$ESBREQUEST/out1:Root-Element/out1:employee/out1:FirstName"/>
                        </imp1:FirstName>
                        <imp1:LastName>
                        <xsl:value-of select="$ESBREQUEST/out1:Root-Element/out1:employee/out1:LastName"/>
                        </imp1:LastName>
                        <imp1:Email>
                        <xsl:value-of select="$ESBREQUEST/out1:Root-Element/out1:employee/out1:Email"/>
                        </imp1:Email>
                        <imp1:PhoneNumber>
                        <xsl:value-of select="$ESBREQUEST/out1:Root-Element/out1:employee/out1:PhoneNumber"/>
                        </imp1:PhoneNumber>
                        <imp1:LocationCode>
                        <xsl:value-of select="$ESBREQUEST/out1:Root-Element/out1:employee/out1:LocationCode"/>
                        </imp1:LocationCode>
                        <imp1:GrossSalary>
                        <xsl:value-of select="$ESBREQUEST/out1:Root-Element/out1:employee/out1:GrossSalary"/>
                        </imp1:GrossSalary>
                        <imp1:NetSalary>
                        <xsl:value-of select="/out1:Root-Element/out1:employee/out1:NetSalary"/>
                        </imp1:NetSalary>
                        </imp1:FullEmployee>
                        </imp1:Root-Element>
                        </xsl:template>
                        </xsl:stylesheet>

                        I'm wondering if there is something fundamentally wrong in the way I have approached this whole ESB service?

                        I'll try and explain what I am seeing to see if you can spot any flaws if you don't mind?

                        From left to right, I have a polling FileAdapter service which has a FileRead_RS.
                        In the FileRead_RS routing rule it maps, from left to right, imp1:employee to out1:employee in my WebService_RS

                        I created WebService_RS to front-end my Web service Adapter service.

                        The first routing rule in in my WebService_RS maps out1:GrossSalary to tns:salary in my Web Service Adapter service method

                        The second leg of this WebService_RS routing rule (with attachRequestPayload set to true) maps the Web service method return value tns:calcNetSalaryResult to out:NetSalary (I'm assuming at this stage that all other out1 data items still persist such as out1:FirstName etc)

                        Then lastly to where I am, the second leg of the FileRead_RS routing rule (with attachRequestPayload also set to true) where I want to pass all the out1:employee data elements including the appended out1:NetSalary to the FileWrite Adapter service.

                        In practise all I have is the NetSalary value - I know the rest of the data is in there somewhere though!

                        Best regards
                        Allan.
                        • 9. Re: Data enrichment in Oracle ESB
                          552064
                          With a Syncrhonous routing service on the ESB there are really four XML Schema's that you need to deal with.

                          1. The input XML Schema to the routing service.
                          2. The XML Schema that you must map your input to to its target service.
                          3. The response XML Schema from the target service.
                          4. The response XML Schema that comes out of your routing service.

                          In the reply XSLT transformation in the routing service you have two XML instances available to you (#1 as ESBREQUEST and #3 as the XML that the transformation is being applied to) as well as the output you are creating #4. In the response message you should not care about #2 as it was generated from #1 and thus if you really did need it you could construct it from $ESBREQUEST by applying the same transformation.

                          To get an idea of what is what, try the following simple template in yoru stylesheet:

                          <xsl:template match="/">
                          <response>
                          <esbrequest><xsl:copy-of select="$ESBREQUEST"/></esbrequest>
                          <input><xsl:copy-of select="*"/></input>
                          </response>
                          </xsl:template>

                          This will give you an idea of all XML data that you have available to you when constructing your <imp1:Root-Element/>.

                          Also a quick note: there is a fundamental different between the xpath "/out1:Root-Element/out1:employee/out1:FirstName" and "$ESBREQUEST/out1:Root-Element/out1:employee/out1:FirstName". I am not surprised that just prefixing the XPath with $ESBREQUEST doesn't work. These are two completely different data sets (for latter extracting data from #1 and the former extracting data from #3).

                          Does this help?
                          • 10. Re: Data enrichment in Oracle ESB
                            548158
                            Many thanks for the speedy response Alastair,

                            This all helps, alot! I'm fairly to new to the tooling (3-4 weeks!) and this is the first time I've come unstuck. I think the real issue might be a lack of working knowledge of XSLT.

                            I know this because when I apply the code you kindly supplied (thanks) i see the output below. This is great because I can see all the data. What is not so great is that I'm not sure how to now manually build the XSLT in order to cherry pick out the various items of data I see below.

                            More importantly though, you come across as someone who has a good, strong working knowledge of XSLT - are you able to point me in the right direction please and suggest any sources that would bring me up the learning curve?

                            <response>
                            <esbrequest>
                            <out1:Root-Element xmlns:out1="http://TargetNamespace.com/ServiceName">
                            <out1:employee>
                            <out1:FirstName>Bobby</out1:FirstName>
                            <out1:LastName>Dazzle</out1:LastName>
                            <out1:Email>bob.daz@scottishpower.co.uk</out1:Email>
                            <out1:PhoneNumber>07753622421</out1:PhoneNumber>
                            <out1:LocationCode>Yoker</out1:LocationCode>
                            <out1:GrossSalary>20000</out1:GrossSalary>
                            </out1:employee>
                            </out1:Root-Element>
                            </esbrequest>
                            <input>
                            <out1:Root-Element xmlns:out1="http://TargetNamespace.com/ServiceName">
                            <out1:employee>
                            <out1:NetSalary>16500</out1:NetSalary>
                            </out1:employee>
                            </out1:Root-Element>
                            </input>
                            </response>
                            • 11. Re: Data enrichment in Oracle ESB
                              552064
                              XSLT Reference: http://www.topxml.com/xsl/xsltref.asp
                              XPath Reference: http://www.topxml.com/xsl/XPathRef.asp

                              If you just want to add the net salary to your input request document the following will work fine (and you should be able to see it in the mapping view):

                              <xsl:template match="/">
                              <imp1:Root-Element>
                              <imp1:FullEmployee>
                              <imp1:FirstName>
                              <xsl:value-of select="$ESBREQUEST/out1:employee/out1:FirstName"/>
                              </imp1:FirstName>
                              <imp1:LastName>
                              <xsl:value-of select="$ESBREQUEST/out1:employee/out1:LastName"/>
                              </imp1:LastName>
                              <imp1:Email>
                              <xsl:value-of select="$ESBREQUEST/out1:employee/out1:Email"/>
                              </imp1:Email>
                              <imp1:PhoneNumber>
                              <xsl:value-of select="$ESBREQUEST/out1:employee/out1:PhoneNumber"/>
                              </imp1:PhoneNumber>
                              <imp1:LocationCode>
                              <xsl:value-of select="$ESBREQUEST/out1:employee/out1:LocationCode"/>
                              </imp1:LocationCode>
                              <imp1:GrossSalary>
                              <xsl:value-of select="$ESBREQUEST/out1:employee/out1:GrossSalary"/>
                              </imp1:GrossSalary>
                              <imp1:NetSalary>
                              <xsl:value-of select="/out1:Root-Element/out1:employee/out1:NetSalary"/>
                              </imp1:NetSalary>
                              </imp1:FullEmployee>
                              </imp1:Root-Element>
                              </xsl:template>

                              So your mistake before was you were going $ESBREQUEST/out1:Root-Element. Well ESBREQUEST is the out1:Root-Element. Also, the NetSalary is not in the ESBREQUEST, its in the XML document being applied. Therefore you grab some of your values from the ESBREQUEST, then the net salary from the XML being input into the stylesheet transformation.

                              Cheers.
                              • 12. Re: Data enrichment in Oracle ESB
                                548158
                                Alastair,

                                As predicted, this worked perfectly (easy when you know how eh?)
                                You should see the smile I have on my face - I'm delighted :-)

                                Many thanks for all your help and taking the time out to not only explain this to me but also to guide me in the right direction, it's really decent of you and very much appreciated.

                                Have a great Christmas and best wishes for the New Year!
                                Allan.
                                • 13. Re: Data enrichment in Oracle ESB
                                  Dave Berry
                                  I'm glad this is working. I spent a bit of time today on it as well. Sorry I lead you down the wrong path of putting the root element in there. To help me debug this, I did a couple of things. I changed the routing rule to invoke a plain outbound file adapter without validation on and it just dumped the contents into the filesystem for me to easily view the results. I also made use of a few params and xpaths to help with debugging. See the snippets below which showed the name, namespace and count of elements. Also using "$ESBREQUEST/*" showed the child names and clued me in to the fact I didn't need the Root-Element.

                                  <xsl:param name="ESBREQUEST"/>
                                  <xsl:param name="ename"> <xsl:value-of select="name($ESBREQUEST)"/> </xsl:param>
                                  <xsl:param name="ensuri"> <xsl:value-of select="namespace-uri($ESBREQUEST)"/> </xsl:param>
                                  <xsl:param name="ecount"> <xsl:value-of select="count($ESBREQUEST)"/> </xsl:param>

                                  <xsl:template match="/">
                                  <inp1:Shipment>
                                  <ESBREQUESTINFO>
                                  <ename><xsl:value-of select="$ename"/></ename>
                                  <ensuri><xsl:value-of select="$ensuri"/></ensuri>
                                  <ecount><xsl:value-of select="$ecount"/></ecount>
                                  <StoreID><xsl:value-of select="$ESBREQUEST/inp1:StoreID"/></StoreID>
                                  <star><xsl:copy-of select="$ESBREQUEST/*"/></star>
                                  </ESBREQUESTINFO>

                                  We're going to publish on OTN a ppt or word doc showing how these more advanced samples were build. Stay tuned.
                                  • 14. Re: Data enrichment in Oracle ESB
                                    548158
                                    Dave,

                                    This is great. I think this thread now provides some pretty detailed and very helpful explanation for this use-case.

                                    I'm really looking forward to the advanced samples. It would be great if all ultimately all this effort and information led to the provision of an ESB cookbook similar to that currently available on OTN for BPEL.

                                    Incidentally I additionally changed my XSL to additionally include a Domain Value Map lookup (again, just to show a little more added value)

                                    <imp1:LocationCode>
                                    <xsl:value-of select="orcl:lookup-dvm('LocationDVM','LocationName',$ESBREQUEST/out1:employee/out1:LocationCode,'LocationAbbreviation','Z')"/>
                                    </imp1:LocationCode>

                                    As an aside and possibly for another thread. When you say you created a "routing rule to invoke a plain outbound file adapter without validation". In terms of the tooling can you explain what you mean by "without validation" please? The reason I ask is that I am also currently trying ascertain what might be considered "best practice" with regards to exception handling. For example I noticed that there is an Error topic on the installed OJMS so I created an ESB service to listen for anything published to that topic and then write it out to my desktop (I had to make both end points opaque as I have no idea if there is an Error or Exception schema provided and available to me). This works fine but what usage patterns did Oracle expect to see when they created this? (Of course just let me know if you want me to post this separately)

                                    Best regards
                                    Allan.
                                    1 2 Previous Next