BizTalk Envelope Processing with the Oracle Adapter in BizTalk 2006

 

The envelope processor is fantastic, it will allow you to receive a document is several records in it, and split it out automatically, into several little messages. All you need to do is define your envelope, and then your child message schema.

I have used this extensively, with the SQL adapter in the past, as well as many other types of messages from very different sources.

I now need to use this with Oracle. I have a test table in Oracle with three fields:             

                ID, SOMEFIELD, ROWID.

I needed to get these out of Oracle, and send them as single messages through BizTalk for parallel processing.

The message I get out of oracle looked like:

<TMP_BIZTALK_TAB:TableChangeEventxmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:exposed="http://schemas.microsoft.com"

      xmlns:TMP_BIZTALK_TAB="http://schemas.microsoft.com/[OracleDb://OracleTest/BIZTALK/Tables/TMP_BIZTALK_TAB]">

      <TMP_BIZTALK_TAB:UpdatedRows>

            <TMP_BIZTALK_TAB:EventRecord>

                  <TMP_BIZTALK_TAB:ID>101</TMP_BIZTALK_TAB:ID>

                  <TMP_BIZTALK_TAB:SOMEFIELD>SampleField</TMP_BIZTALK_TAB:SOMEFIELD>

                  <TMP_BIZTALK_TAB:ROWID>CustomRowID</TMP_BIZTALK_TAB:ROWID>

            </TMP_BIZTALK_TAB:EventRecord>

      </TMP_BIZTALK_TAB:UpdatedRows>

            ....repeated....

The two things to note here, the child message “EventRecord” does not have a namespace of its own, it will inherit the name space of the parent.

The second thing is it’s buried in the repeated element “UpdatedRows” This does not present much of a problem, other than to confuse you.

Document Schema

I created my child schema for Event Record, it had the same namespace as the parent, as seen below:

<?xmlversion="1.0"encoding="utf-16"?>

<xsd:schemaxmlns="http://schemas.microsoft.com/[OracleDb://OracleTest/BIZTALK/Tables/TMP_BIZTALK_TAB]"xmlns:b="http://schemas.microsoft.com/BizTalk/2003"elementFormDefault="qualified"targetNamespace="http://schemas.microsoft.com/[OracleDb://OracleTest/BIZTALK/Tables/TMP_BIZTALK_TAB]"xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <xsd:complexTypename="EventRecord">

    <xsd:sequence>

      <xsd:elementname="ID">

        <xsd:simpleType>

          <xsd:restrictionbase="xsd:string">

            <xsd:patternvalue="\s*(\+|\-)?\d{0,126}(\.\d{0,129})?(e(\+|\-)?\d+)?\s*"/>

            <xsd:maxLengthvalue="137"/>

          </xsd:restriction>

        </xsd:simpleType>

      </xsd:element>

      <xsd:elementname="SOMEFIELD">

        <xsd:simpleType>

          <xsd:restrictionbase="xsd:string">

            <xsd:maxLengthvalue="100"/>

          </xsd:restriction>

        </xsd:simpleType>

      </xsd:element>

      <xsd:elementname="ROWID">

        <xsd:simpleType>

          <xsd:restrictionbase="xsd:string">

            <xsd:maxLengthvalue="18"/>

          </xsd:restriction>

        </xsd:simpleType>

      </xsd:element>

    </xsd:sequence>

  </xsd:complexType>

  <xsd:elementname="EventRecord"type="EventRecord"/>

</xsd:schema>

Envelope Schema

I then created the schema for the envelope, which is a cut down schema from the message I was receiving, note that I was not able to import the child schema, as it contained the same name space and conflicted. I get around this by using an any tag with skip on the process contents, this basically means I don’t care what is in this any tag, just let it through… So it let my child payload all the way though.

<?xmlversion="1.0"encoding="utf-16"?>

<xsd:schemaxmlns:TMP_BIZTALK_TAB="http://schemas.microsoft.com/[OracleDb://OracleTest/BIZTALK/Tables/TMP_BIZTALK_TAB]"xmlns:b="http://schemas.microsoft.com/BizTalk/2003"elementFormDefault="qualified"targetNamespace="http://schemas.microsoft.com/[OracleDb://OracleTest/BIZTALK/Tables/TMP_BIZTALK_TAB]"xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <xsd:annotation>

    <xsd:appinfo>

      <b:schemaInfois_envelope="yes"xmlns:b="http://schemas.microsoft.com/BizTalk/2003"/>

    </xsd:appinfo>

  </xsd:annotation>

  <xsd:complexTypename="EventRecord">

    <xsd:sequence>

      <xsd:anyminOccurs="0"maxOccurs="unbounded"processContents="skip"/>

    </xsd:sequence>

  </xsd:complexType>

  <xsd:elementname="TableChangeEvent">

    <xsd:annotation>

      <xsd:appinfo>

        <b:recordInfobody_xpath="/*[local-name()='TableChangeEvent' and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OracleTest/BIZTALK/Tables/TMP_BIZTALK_TAB]']/*[local-name()='UpdatedRows' and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OracleTest/BIZTALK/Tables/TMP_BIZTALK_TAB]']"/>

      </xsd:appinfo>

    </xsd:annotation>

    <xsd:complexType>

      <xsd:sequence>

        <xsd:elementminOccurs="0"maxOccurs="unbounded"name="UpdatedRows"type="TMP_BIZTALK_TAB:EventRecord"/>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

</xsd:schema>

The Result

I received several destination messages all looking like the message below, each of which  was submitted to BizTalk as a single message. Do note, I did NOT have to use a special pipeline, that identified the envelope and document schema, I just used the standard xml pipeline that looked up the envelope schema, and the document  schema for me.

<TMP_BIZTALK_TAB:EventRecordxmlns:TMP_BIZTALK_TAB="http://schemas.microsoft.com/[OracleDb://OracleTest/BIZTALK/Tables/TMP_BIZTALK_TAB]">

                  <TMP_BIZTALK_TAB:ID>101</TMP_BIZTALK_TAB:ID>

                  <TMP_BIZTALK_TAB:SOMEFIELD>SampleField</TMP_BIZTALK_TAB:SOMEFIELD>

                  <TMP_BIZTALK_TAB:ROWID>CustomRowID</TMP_BIZTALK_TAB:ROWID>

</TMP_BIZTALK_TAB:EventRecord>

 

I feel pleased I have conquered Oracle in this respect, now to insert Records into Oracle…. The fun continues…. Stay tuned…