locked
Get Data from CRMOnline using TSQL RRS feed

  • Question

  • Hello, 

    The following statement retrieves data from the Account Entity in CRM. We are utilizing SSIS to get this data and then insert it into a SQL Server table so it can be accessed via SQL. 

    Is there a method where a TSQL Statement can be utilized against this fetchxml statement so that the data can be retrieved directly and live from CRM within a stored procedure without having to have the holding table which is maintained via SSIS ? 

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="account">
    <attribute name="accountcategorycode" />
    <attribute name="accountcategorycodename" />
    
    <attribute name="accountclassificationcodename" />
    <attribute name="accountid" />
    <attribute name="accountnumber" />
    
    
    
    
    
    <attribute name="address1_city" />
    
    <attribute name="address1_country" />
    <attribute name="address1_county" />
    <attribute name="address1_fax" />
    <attribute name="address1_freighttermscode" />
    
    
    <attribute name="address1_line1" />
    <attribute name="address1_line2" />
    
    
    
    <attribute name="address1_postalcode" />
    
    
    
    
    <attribute name="address1_stateorprovince" />
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    <attribute name="createdon" />
    
    
    
    <attribute name="creditlimit" />
    <attribute name="creditlimit_base" />
    <attribute name="creditonhold" />
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    <attribute name="modifiedon" />
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    <attribute name="name" />
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    </entity>
    </fetch>
    

    Tuesday, May 26, 2020 8:12 PM

Answers

  • Hi vsdla,

    Here is how to generate a dynamic SQL, based on your XML.

    XQuery with its FLWOR expression makes it an easy task.

    SQL:

    DECLARE @x XML = '<?xml version="1.0"?>
    <fetch version="1.0" output-format="xml-platform" mapping="logical"
           distinct="false">
    	<entity name="account">
    		<attribute name="accountcategorycode"/>
    		<attribute name="accountcategorycodename"/>
    		<attribute name="accountclassificationcodename"/>
    		<attribute name="accountid"/>
    		<attribute name="accountnumber"/>
    		<attribute name="address1_city"/>
    		<attribute name="address1_country"/>
    		<attribute name="address1_county"/>
    		<attribute name="address1_fax"/>
    		<attribute name="address1_freighttermscode"/>
    		<attribute name="address1_line1"/>
    		<attribute name="address1_line2"/>
    		<attribute name="address1_postalcode"/>
    		<attribute name="address1_stateorprovince"/>
    		<attribute name="createdon"/>
    		<attribute name="creditlimit"/>
    		<attribute name="creditlimit_base"/>
    		<attribute name="creditonhold"/>
    		<attribute name="modifiedon"/>
    		<attribute name="name"/>
    	</entity>
    </fetch>';
    
    DECLARE @sql NVARCHAR(MAX) = N'SELECT '
       , @CrLf CHAR(2) = CHAR(13) + CHAR(10)
       , @separator CHAR(1) = ',';
    
    -- XQuery with FLWOR
    SET @sql +=  @x.query('
    	for $r in /fetch/entity/attribute/@name
    	return if ($r is (/fetch/entity/attribute[last()]/@name)[1]) then string($r)
                else concat($r, sql:variable("@separator"), sql:variable("@CrLf"))
    ').value('.', 'NVARCHAR(MAX)');
    
    SET @sql +=  @CrLf + 'FROM ' + @x.value('(/fetch/entity/@name)[1]','VARCHAR(30)') + ';';
    
    -- just to see it
    PRINT @sql;
    
    EXEC sp_executesql @stmt = @sql, @params = N'@x xml', @x = @x;


    • Edited by Yitzhak Khabinsky Wednesday, May 27, 2020 4:28 PM
    • Marked as answer by vsdla Wednesday, May 27, 2020 5:01 PM
    Wednesday, May 27, 2020 4:01 PM

All replies

  • Hi vsdla,

    It is possible. It seems that all what you need is the Account table data. Please confirm.

    You can just write a stored procedure to retrieve its data.

    SELECT ... FROM Account;

    Better formatted XML:

    <?xml version="1.0"?>
    <fetch version="1.0" output-format="xml-platform" mapping="logical"
           distinct="false">
    	<entity name="account">
    		<attribute name="accountcategorycode"/>
    		<attribute name="accountcategorycodename"/>
    		<attribute name="accountclassificationcodename"/>
    		<attribute name="accountid"/>
    		<attribute name="accountnumber"/>
    		<attribute name="address1_city"/>
    		<attribute name="address1_country"/>
    		<attribute name="address1_county"/>
    		<attribute name="address1_fax"/>
    		<attribute name="address1_freighttermscode"/>
    		<attribute name="address1_line1"/>
    		<attribute name="address1_line2"/>
    		<attribute name="address1_postalcode"/>
    		<attribute name="address1_stateorprovince"/>
    		<attribute name="createdon"/>
    		<attribute name="creditlimit"/>
    		<attribute name="creditlimit_base"/>
    		<attribute name="creditonhold"/>
    		<attribute name="modifiedon"/>
    		<attribute name="name"/>
    	</entity>
    </fetch>


    Tuesday, May 26, 2020 9:43 PM
  • Thank you - yes, that is fine, just the Account data. 

    I understand fetch portion, since we are doing this in SSIS. What I don't understand is how to integrate the TSQL Statement to retrieve the fetch statement in one command, so it is retrieved in either a View or a Stored Procedure or both.... something akin to a CTE statement, perhaps ? 

    Also - how to make the connection to the CRM365 datasource? I'm thinking this is a LinkedServer, and if so, how is that configured, since the CRM Server is not on premise?

    And yes, the ultimate result would be: 

    SELECT ... FROM Account;

    Even if Account is defined using the Fetch Statement (which I think it needs to be)


    • Edited by vsdla Tuesday, May 26, 2020 10:29 PM
    Tuesday, May 26, 2020 10:22 PM
  • Hi vsdla,

    1. It seems that dynamic SQL, generated from the XML will do the job.
      XML's attributes will become a column name list for the SELECT clause.
      Please confirm that it is what you need.
    2. "...Also - how to make the connection to the CRM365 datasource? I'm thinking this is a LinkedServer, and if so, how is that configured, since the CRM Server is not on premise?..."
      This is one million dollar question. And if it is unknown, the item #1 above is a moot point.
      It seems you need to investigate how SSIS is making connection to the CRM365.

    Tuesday, May 26, 2020 10:43 PM
  • This seems to step through the item # 2 you noted above...

    https://www.cdata.com/kb/tech/dynamicscrm-odbc-linked-server.rst

    Hopefully it doesn't cost a million dollars

    Wednesday, May 27, 2020 3:33 PM
  • How does the dynamics syntax work, exactly. Can you provide an example ? Is it as asked above, a CTE so you have something like the following:

    Select accountcategorycode from 
    
    <?xml version="1.0"?>
    <fetch version="1.0" output-format="xml-platform" mapping="logical"
           distinct="false">
    	<entity name="account">
    		<attribute name="accountcategorycode"/>
    		<attribute name="accountcategorycodename"/>
    		<attribute name="accountclassificationcodename"/>
    		<attribute name="accountid"/>
    		<attribute name="accountnumber"/>
    		<attribute name="address1_city"/>
    		<attribute name="address1_country"/>
    		<attribute name="address1_county"/>
    		<attribute name="address1_fax"/>
    		<attribute name="address1_freighttermscode"/>
    		<attribute name="address1_line1"/>
    		<attribute name="address1_line2"/>
    		<attribute name="address1_postalcode"/>
    		<attribute name="address1_stateorprovince"/>
    		<attribute name="createdon"/>
    		<attribute name="creditlimit"/>
    		<attribute name="creditlimit_base"/>
    		<attribute name="creditonhold"/>
    		<attribute name="modifiedon"/>
    		<attribute name="name"/>
    	</entity>
    </fetch>
    
    

    And if this is the case, then presumably the <fetch> statement would need to be wrapped in some sort of tsql construct

    Wednesday, May 27, 2020 3:37 PM
  • Hi vsdla,

    Here is how to generate a dynamic SQL, based on your XML.

    XQuery with its FLWOR expression makes it an easy task.

    SQL:

    DECLARE @x XML = '<?xml version="1.0"?>
    <fetch version="1.0" output-format="xml-platform" mapping="logical"
           distinct="false">
    	<entity name="account">
    		<attribute name="accountcategorycode"/>
    		<attribute name="accountcategorycodename"/>
    		<attribute name="accountclassificationcodename"/>
    		<attribute name="accountid"/>
    		<attribute name="accountnumber"/>
    		<attribute name="address1_city"/>
    		<attribute name="address1_country"/>
    		<attribute name="address1_county"/>
    		<attribute name="address1_fax"/>
    		<attribute name="address1_freighttermscode"/>
    		<attribute name="address1_line1"/>
    		<attribute name="address1_line2"/>
    		<attribute name="address1_postalcode"/>
    		<attribute name="address1_stateorprovince"/>
    		<attribute name="createdon"/>
    		<attribute name="creditlimit"/>
    		<attribute name="creditlimit_base"/>
    		<attribute name="creditonhold"/>
    		<attribute name="modifiedon"/>
    		<attribute name="name"/>
    	</entity>
    </fetch>';
    
    DECLARE @sql NVARCHAR(MAX) = N'SELECT '
       , @CrLf CHAR(2) = CHAR(13) + CHAR(10)
       , @separator CHAR(1) = ',';
    
    -- XQuery with FLWOR
    SET @sql +=  @x.query('
    	for $r in /fetch/entity/attribute/@name
    	return if ($r is (/fetch/entity/attribute[last()]/@name)[1]) then string($r)
                else concat($r, sql:variable("@separator"), sql:variable("@CrLf"))
    ').value('.', 'NVARCHAR(MAX)');
    
    SET @sql +=  @CrLf + 'FROM ' + @x.value('(/fetch/entity/@name)[1]','VARCHAR(30)') + ';';
    
    -- just to see it
    PRINT @sql;
    
    EXEC sp_executesql @stmt = @sql, @params = N'@x xml', @x = @x;


    • Edited by Yitzhak Khabinsky Wednesday, May 27, 2020 4:28 PM
    • Marked as answer by vsdla Wednesday, May 27, 2020 5:01 PM
    Wednesday, May 27, 2020 4:01 PM
  • Thank you, Yitzhak.

    I'm not completely certain I can access the CRM365 Server via Linked Servers without purchasing the product mentioned above - 

    I really do appreciate your help!

    Wednesday, May 27, 2020 5:01 PM
  • Hi vsdla,

    Glad to hear that I was able to help.

    Please connect with me on LinkedIn.

    Wednesday, May 27, 2020 5:03 PM
  • If anyone can help provide guidance on how to create a linked server to an CRM365 instance/database that would most appreciated, too !

    Thank you!

    Wednesday, May 27, 2020 7:31 PM