none
BizTalk does not recognize the xml returned by WCF-Oracle Adapter RRS feed

  • Question

  • Hello,

    I wrote the following query to return nested xml from Oracle database.

    SELECT
    XMLELEMENT("RMB_AP",
    XMLAGG(
    XMLELEMENT("Transaction",
       XMLELEMENT("VCHR_BANK_STG", 
              XMLELEMENT("CONTEXT", B.CONTEXT),
              XMLELEMENT("BUSINESS_UNIT", B.BUSINESS_UNIT),
              XMLELEMENT("BANK_ID_QUAL", B.BANK_ID_QUAL),
              XMLELEMENT("BNK_ID_NBR", B.BNK_ID_NBR),
              XMLELEMENT("BRANCH_ID", B.BRANCH_ID),
              XMLELEMENT("BANK_ACCT_TYPE", B.BANK_ACCT_TYPE),
              XMLELEMENT("BANK_ACCOUNT_NUM", B.BANK_ACCOUNT_NUM)
             ) ,
             
       XMLELEMENT("VCHR_VNDR_STG", 
              XMLELEMENT("ADDR_FIELD1", V.ADDR_FIELD1),
              XMLELEMENT("ADDR_FIELD2", V.ADDR_FIELD2),
              XMLELEMENT("ADDR_FIELD3", V.ADDR_FIELD3),
              XMLELEMENT("COUNTY", V.COUNTY),
              XMLELEMENT("STATE", V.STATE),
              XMLELEMENT("POSTAL", V.POSTAL),
              XMLELEMENT("GEO_CODE", V.GEO_CODE),
              XMLELEMENT("IN_CITY_LIMIT", V.IN_CITY_LIMIT)         
            ),             
       XMLELEMENT("VCHR_PYMT_STG",           
              XMLELEMENT("PYMNT_METHOD", P.PYMNT_METHOD),
              XMLELEMENT("PYMNT_MESSAGE", P.PYMNT_MESSAGE),
              XMLELEMENT("PYMNT_VCHR_PCT", P.PYMNT_VCHR_PCT),         
              XMLELEMENT("EFT_LAYOUT_CD", P.EFT_LAYOUT_CD)
            ),
    
     XMLELEMENT("VCHR_HDR_STG",
           XMLELEMENT("LC_ID", H.LC_ID),       
           XMLELEMENT("CLAIM_NO", H.CLAIM_NO),
           XMLELEMENT("POLICY_NUM", H.POLICY_NUM),
           (SELECT
            XMLAGG(
                XMLELEMENT("VCHR_LINE_STG", 
                   XMLELEMENT("COUNTRY_LOC_BUYER", L.COUNTRY_LOC_BUYER),
                   XMLELEMENT("STATE_LOC_BUYER", L.STATE_LOC_BUYER),
                   XMLELEMENT("COUNTRY_LOC_SELLER", L.COUNTRY_LOC_SELLER),
                   XMLELEMENT("STATE_LOC_SELLER", L.STATE_LOC_SELLER),
                                       
                         (SELECT 
                          XMLAGG(XMLELEMENT
                              ("VCHR_DIST_STG", 
                               XMLELEMENT("VOUCHER_LINE_NUM", D.VOUCHER_LINE_NUM),                          
                               XMLELEMENT("D.DISTRIB_LINE_NUM", D.DISTRIB_LINE_NUM)
                              )
                             )
                          FROM CM_PSVCHRDIST_STG D
                          WHERE D.BATCH_DATE = L.BATCH_DATE AND D.CONTEXT = L.CONTEXT AND D.BUSINESS_UNIT = L.BUSINESS_UNIT AND D.VOUCHER_ID = L.VOUCHER_ID AND D.VOUCHER_LINE_NUM = L.VOUCHER_LINE_NUM                                                   
                          )         
            )
            
            )
            FROM CM_PSVCHRLINE_STG L WHERE H.BATCH_DATE = L.BATCH_DATE AND H.CONTEXT = L.CONTEXT AND H.BUSINESS_UNIT = L.BUSINESS_UNIT AND H.VOUCHER_ID = L.VOUCHER_ID
           ))
             
           )
           )
           )
           AS AP_DATA
    FROM
     CM_PSVCHRHDR_STG H 
      INNER JOIN CM_PSVCHRVNDR_STG V ON H.BATCH_DATE = V.BATCH_DATE AND H.CONTEXT = V.CONTEXT AND H.BUSINESS_UNIT = V.BUSINESS_UNIT AND V.VOUCHER_ID = H.VOUCHER_ID
      INNER JOIN CM_PSVCHRPYMT_STG P ON H.BATCH_DATE = P.BATCH_DATE AND H.CONTEXT = P.CONTEXT AND H.BUSINESS_UNIT = P.BUSINESS_UNIT AND H.VOUCHER_ID = P.VOUCHER_ID
      INNER JOIN CM_PSVCHRBANK_STG B ON H.BATCH_DATE = B.BATCH_DATE AND H.CONTEXT = B.CONTEXT AND H.BUSINESS_UNIT = B.BUSINESS_UNIT AND H.VOUCHER_ID = B.VOUCHER_ID
    WHERE
     H.BUSINESS_UNIT = '2'
    
    

    This query returns the following xml. This is the xml structure I would like to use in a map.

    <RMB_AP>
    	<Transaction>
    		<VCHR_BANK_STG>
    			<CONTEXT>RMPSAP</CONTEXT>
    			<BUSINESS_UNIT>2</BUSINESS_UNIT>
    			<BANK_ID_QUAL> </BANK_ID_QUAL>
    			<BNK_ID_NBR> </BNK_ID_NBR>
    			<BRANCH_ID> </BRANCH_ID>
    			<BANK_ACCT_TYPE> </BANK_ACCT_TYPE>
    			<BANK_ACCOUNT_NUM> </BANK_ACCOUNT_NUM>
    		</VCHR_BANK_STG>
    		<VCHR_VNDR_STG>
    			<ADDR_FIELD1> </ADDR_FIELD1>
    			<ADDR_FIELD2> </ADDR_FIELD2>
    			<ADDR_FIELD3> </ADDR_FIELD3>
    			<COUNTY> </COUNTY>
    			<STATE>FL  </STATE>
    			<POSTAL>32128    </POSTAL>
    			<GEO_CODE> </GEO_CODE>
    			<IN_CITY_LIMIT>N</IN_CITY_LIMIT>
    		</VCHR_VNDR_STG>
    		<VCHR_PYMT_STG>
    			<PYMNT_METHOD>CHK</PYMNT_METHOD>
    			<PYMNT_MESSAGE> </PYMNT_MESSAGE>
    			<PYMNT_VCHR_PCT>0</PYMNT_VCHR_PCT>
    			<EFT_LAYOUT_CD> </EFT_LAYOUT_CD>
    		</VCHR_PYMT_STG>
    		<VCHR_HDR_STG>
    			<LC_ID></LC_ID>
    			<CLAIM_NO>XXX-123456</CLAIM_NO>
    			<POLICY_NUM>XXX-123451</POLICY_NUM>
    			<VCHR_LINE_STG>
    				<COUNTRY_LOC_BUYER></COUNTRY_LOC_BUYER>
    				<STATE_LOC_BUYER></STATE_LOC_BUYER>
    				<COUNTRY_LOC_SELLER></COUNTRY_LOC_SELLER>
    				<STATE_LOC_SELLER></STATE_LOC_SELLER>
    				<VCHR_DIST_STG>
    					<VOUCHER_LINE_NUM>2</VOUCHER_LINE_NUM>
    					<D.DISTRIB_LINE_NUM>2</D.DISTRIB_LINE_NUM>
    				</VCHR_DIST_STG>
    				<VCHR_DIST_STG>
    					<VOUCHER_LINE_NUM>2</VOUCHER_LINE_NUM>
    					<D.DISTRIB_LINE_NUM>3</D.DISTRIB_LINE_NUM>
    				</VCHR_DIST_STG>
    			</VCHR_LINE_STG>
    			<VCHR_LINE_STG>
    				<COUNTRY_LOC_BUYER></COUNTRY_LOC_BUYER>
    				<STATE_LOC_BUYER></STATE_LOC_BUYER>
    				<COUNTRY_LOC_SELLER></COUNTRY_LOC_SELLER>
    				<STATE_LOC_SELLER></STATE_LOC_SELLER>
    			</VCHR_LINE_STG>
    		</VCHR_HDR_STG>
    	</Transaction>
    </RMB_AP>
    

    However, when I generate a schema with WCF-OracleDB adapter and set the polling statement to the sql query above, BizTalk generates a schema with a single node named AP_DATA (the alias I used in the query) which makes it difficult to use it in a map.

    On the other hand, if I generate a schema from the above xml with xsd, I can get the schema I want.

     

    My question is:

    How can I generate a schema from BizTalk that will recognize the structure of the xml returned by the polling statement query?

     

    Thank you,

    Alper

     

     

     

     

    Friday, April 29, 2011 4:40 PM

All replies

  • Hi, this is an easy one fortunately.  Use the Add Generated Items wizard in Visual Studio and select Generate Schema ->Well Formed XML.  If it's not installed yet, it will tell you what script to run.  Then, after this is generated add an import to the WCF-Oracle generated schema and make the node below AP_DATA RMB_AP (it can be a reference to the included schema).  That looks a lot like PeopleSoft data... there is a PeopleSoft adapter for BizTalk too. 

    Kind Regards,

    -Dan


    If this answers your question, please Mark as Answer
    Friday, April 29, 2011 6:41 PM
  • Hi Dan,

    I generated the schemas 

    • RMB_AP (generated from Well-formed xml (returned by the query))
    • WCF_ORACLE_POLLINGSTMTRMPSAP (generated by WCF-OracleDB adapter)

     

    Can you give a little more detail on " add an import to the WCF-Oracle generated schema and make the node below AP_DATA RMB_AP (it can be a reference to the included schema)." ?

     

    I can add an import to the WCF-Oracle generated schema

    • Open WCF-Oracle generated schema in the VS editor
    • Click on Schema node
    • In the properties window click on Imports 
    • In the Imports dialog box leave Import New Schema As: XSD Import
    • Click Add
    • In the Select schemas window find the schema generated from well-formed xml. Click OK
    • This adds the schema generated from well-formed xml to the import list. (Prefix and Namespace is blank)

     

    Next step is to make the node below AP_DATA (I changed the alias to RMB_AP to match) RMB_AP. 

    However, there is not a node below AP_DATA, do I need to create one?

     

    Here is a screenshot. http://f.imgtmp.com/OPVb3.jpg

    Schema on the left is generated from xml, the one the right is generated from WCF-Oracle Adapter.

     

    Thanks again,

    Alper

    Friday, April 29, 2011 7:48 PM
  • Delete RMB_AP.  Add it back as a child record, not an elemnt.  After doing this browse to the Data Structure Type property and browse there for the type RMB_AP, which should now be visible.  If it's not you can go to the imported schema and select that node then type in a name in the Data Structure Type property, like RMB_AP_Type.  This will convert the record (element) into a reuseable data type.  Hope this helps.

    -Dan


    If this answers your question, please Mark as Answer
    Saturday, April 30, 2011 1:37 PM
  • Hi Dan,

    I think I have followed your instructions correctly

    1. Generated schema via WCF-OracleDB adapter with the above sql as the PollingStatement
    2. Generated a schema from XML returned from the above sql
    3. Deleted existing RMB_AP element under POLLINGSTMTRECORD in the WCF-OracleDB generated schema
    4. Added a child record named RMB_AP under POLLINGSTMTRECORD in the WCF-OracleDB generated schema
    5. Built project => Warning target namespace is empty for the schema generated from XML
    6. Schema generated from XML is not available in the Data Structure Type list
    7. Imported schema generated from XML into theWCF-OracleDB generated schema
    8. Built project
    9. RMB_AP (Reference) -> the schema generated from XML is now available in the Data Structrure Type list
    10. Cannot select RMB_AP (reference) from the Data Structrure Type list
    11. Added target namespace to the schema generated from XML.
    12. Error namespace in the import should match etc..
    13. Removed and re-imported schema generated from XML into the WCF-OracleDB generated schema
    14. Built project -> No warnings
    15. In the WCF-OracleDB generated schema, set the manually added RMB_AP child record's Data Structure Type to ns0:RNB_AP (reference)
    16. Built, deployed
    17. Created a receive and a send port to poll the Oracle database and write the results to file.
    18. The file output is like this:

    <POLLINGSTMT xmlns="http://Microsoft.LobServices.OracleDB/2007/03/POLLINGSTMTRMPSAP">
    <POLLINGSTMTRECORD>
    <POLLINGSTMTRECORD>
      <RMB_AP>
        &lt;RMB_AP&gt;
         ..........
        &lt;/RMB_AP&gt;
      </RMB_AP>
    </POLLINGSTMTRECORD>
    </POLLINGSTMTRECORD>
    </POLLINGSTMT>
    

    Note the duplicate RMB_AP elements. Naturally, this structure does not play well with my map.

    I have to name the child record I add to WCF-OracleDB generated schema something (I named it RMB_AP)

    and I have to have a top level element (RMB_AP) in the XML generated by the query otherwise I can't generate a schema.

    Therefore, the document returned by the pollingstatement ends up with duplicate elements.

    Any ideas on how to resolve this?

     

    Thanks for your patience,

    Alper

    Monday, May 2, 2011 10:30 PM