locked
XMLDocument in the Orchestration RRS feed

  • Question

  • In the Orchestration, I am creating a XML Document to Select all the records from the view in the Oracle Database. Below is the

    xmlVarMsg = new System.Xml.XmlDocument();
    xmlVarMsg .LoadXml(@"<ns0:Select xmlns:ns0='http://Microsoft.LobServices.OracleDB/2007/03/View/HMO'><ns0:COLUMN_NAMES>*</ns0:COLUMN_NAMES><ns0:FILTER></ns0:FILTER></ns0:Select>");
    Message_NonHMOSelect =xmlVarMsg;

    But if I want to add order by in the Query but not sure how to add here in the XMLDocument.

    Thursday, January 19, 2017 3:56 PM

Answers

All replies

  • Hi, where and order by clause can be specified in filter. Refer: https://msdn.microsoft.com/en-us/library/cc185500(v=bts.10).aspx

    Rachit Sikroria (Microsoft Azure MVP)

    Thursday, January 19, 2017 4:15 PM
    Moderator
  • I tried 

    <ns0:Select xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/View/HMO"><ns0:COLUMN_NAMES>*</ns0:COLUMN_NAMES><ns0:FILTER>Order by Employee_ID</ns0:FILTER></ns0:Select> .

    But getting erro saying Details:"Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-00936: missing expression ---> Oracle.DataAccess.Client.OracleException: ORA-00936: missing expression

    Thursday, January 19, 2017 5:11 PM
  • Yes thats correct. A SELECT query is performed on the target table using the WHERE clause specified in the FILTER element. You cannot specify Order By in filter element, it is only for where clause.

    And below syntax is wrong:

    <Select xmlns="http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES">
    <COLUMN_NAMES>*</COLUMN_NAMES>
    <FILTER>where LAST_NAME='King'</FILTER>
    </Select>

    The correct systax should be;

    <Select xmlns="http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES">
    <COLUMN_NAMES>*</COLUMN_NAMES>
    <FILTER>LAST_NAME='King'</FILTER>
    </Select>





    Rachit Sikroria (Microsoft Azure MVP)

    Thursday, January 19, 2017 5:36 PM
    Moderator
  • Do you say order by cannot be used. I am confused with the first answer and this one.
    Thursday, January 19, 2017 5:58 PM
  • Do you say order by cannot be used. I am confused with the first answer and this one.

    You can ignore my first post, the select schema only has a provision to specify the WHERE clause in the FILTER element.

    You can always sort the records using Inline XSLT in map.

    Refer: https://saranmeena.wordpress.com/2012/09/11/sorting-records-within-biztalk-map-xslt/

    https://social.technet.microsoft.com/wiki/contents/articles/22059.biztalk-server-grouping-and-sorting-operations-inside-biztalk-maps-using-the-muenchian-method.aspx

    http://btsguru.blogspot.in/2011/12/sorting-and-grouping-records-nodes-in.html


    Rachit Sikroria (Microsoft Azure MVP)

    • Marked as answer by vdha Thursday, January 19, 2017 10:13 PM
    Thursday, January 19, 2017 6:19 PM
    Moderator