Announcement

Collapse
No announcement yet.

Get output of a SELECT SQL Statement in XML format using DB.xml

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Get output of a SELECT SQL Statement in XML format using DB.xml

    OTMers,

    How to get an output of a SELECT SQL Statement in XML format using DB.xml from OTM

    Basically, request would come from Integration Layer in XML format and it will hit OTM database to get out of a SQL SELECT statement passed in that XML file

    And in-turn it will send output of that SQL SELECT statement in XML format to Integration Layer

    Has anyone done this kind of scenario in OTM using DB.xml? If yes, then please provide some insights to that

    Sample SQL as shown below,


    SELECT
    'USERSYNCRESPONSE' AS "RESPONSE",
    US.GL_USER_GID AS "USERID",
    CON.FIRST_NAME AS "FIRSTNAME",
    CON.LAST_NAME AS "LASTNAME",
    CON.EMAIL_ADDRESS AS "EMAILADDR",
    CON.PHONE1 AS "TELNUMBER",
    '' AS "DEPARTMENT",
    US.DOMAIN_NAME AS "USERGROUP",
    CON.FAX AS "TELEXTENS",
    '' AS "DELETIONFLAG",
    '' AS "IS_EXPIRED",
    '' AS "ISLOCKED",
    US.IS_EXTERNAL AS "USER_TYPE",
    US.EFFECTIVE_DATE AS "VALID_FROM",
    US.EXPIRATION_DATE AS "VALID_TO",
    '' AS "MANAGER_ID"
    FROM GL_USER US,
    CONTACT CON
    WHERE
    US.GL_USER_GID = CON.GL_USER_GID
    AND US.GL_USER_GID = 'GL_USER_GID'
    AND US.GL_USER_GID is not null
    ORDER BY US.GL_USER_GID


    Thank you


    -Prasad-
    Last edited by Prasad Chandane; November 17th, 2017, 06:28.
    Let's connect on LinkedIn
    Mobile: +91 973 049 6886
    E-mail: prasad.chandane@gmail.com

    If my post was helpful, then click on the Like button

  • #2
    Hi Prasad,

    I think you can run the RemoteQuery to return Contact table for the GL_USER_GID.

    SELECT contact_gid FROM contact where GL_USER_GID = 'your id'

    Remote query should return the OTM objects, you should be able to see the object/structure of sample in GLogXML.xsd.

    Thanks and Regards,
    Anurag Saini
    -------
    Thanks and Regards
    Anurag Saini
    MavenWire

    Comment


    • #3
      Thank you Anurag

      Parameters in WHERE clause of SELECT statements are dynamic and tables involved might be more than 4-5
      Also, the limitation is that we can not use
      OS level scripts e.g. dbxml.sh and sql/Location.sql (as mentioned in OTM Data Management Guide) due to restricted access
      Last edited by Prasad Chandane; November 17th, 2017, 06:30.
      Let's connect on LinkedIn
      Mobile: +91 973 049 6886
      E-mail: prasad.chandane@gmail.com

      If my post was helpful, then click on the Like button

      Comment


      • #4
        OTMers,

        We can post below XML in SOAP UI and got a response in XML format using http://localhost/GC3/glog.integratio...mand=xmlExport along with required parameters





        We can also get output of complex SELECT query with multiple tables involved

        PS: Sample response is mentioned in OTM Data Management Guide

        Thank you


        -Prasad-
        Attached Files
        Last edited by Prasad Chandane; November 17th, 2017, 06:31.
        Let's connect on LinkedIn
        Mobile: +91 973 049 6886
        E-mail: prasad.chandane@gmail.com

        If my post was helpful, then click on the Like button

        Comment

        Working...
        X