locked
BizTalk Map: Grouping CRM Response XML based on the ID field in Custom XSLT RRS feed

  • Question

  • Hi,

    I will receive a CRM response as below. 

    <ns0:RetrieveMultipleResponse xmlns:ns0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns2="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns3="http://schemas.microsoft.com/xrm/2011/Metadata" xmlns:ns5="http://schemas.microsoft.com/2003/10/Serialization/" xmlns:ns1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlns:ns4="http://schemas.microsoft.com/xrm/2011/Contracts"><ns0:RetrieveMultipleResult>    <ns4:Entities>      <ns4:Entity>        <b:Attributes xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic">            <b:KeyValuePairOfstringanyType>            <c:key>ccx_datevaccineinfostatementpresented</c:key>            <c:value i:type="d:dateTime" xmlns:d="http://www.w3.org/2001/XMLSchema">2012-08-14T04:00:00Z</c:value>          </b:KeyValuePairOfstringanyType>          <b:KeyValuePairOfstringanyType>            <c:key>ccx_administeredamount</c:key>            <c:value i:type="d:decimal" xmlns:d="http://www.w3.org/2001/XMLSchema">0.75000000</c:value>          </b:KeyValuePairOfstringanyType>          <b:KeyValuePairOfstringanyType>            <c:key>ccx_immunizationid</c:key>            <c:value i:type="d:guid" xmlns:d="http://schemas.microsoft.com/2003/10/Serialization/">826c2b0e-a349-e411-866c-00155d1e1f77</c:value>          </b:KeyValuePairOfstringanyType>     </b:Attributes>  </ns4:Entity>   <ns4:Entity>        <b:Attributes xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic">            <b:KeyValuePairOfstringanyType>            <c:key>ccx_datevaccineinfostatementpresented</c:key>            <c:value i:type="d:dateTime" xmlns:d="http://www.w3.org/2001/XMLSchema">2012-08-14T04:00:00Z</c:value>          </b:KeyValuePairOfstringanyType>          <b:KeyValuePairOfstringanyType>            <c:key>ccx_administeredamount</c:key>            <c:value i:type="d:decimal" xmlns:d="http://www.w3.org/2001/XMLSchema">0.25000</c:value>          </b:KeyValuePairOfstringanyType>          <b:KeyValuePairOfstringanyType>            <c:key>ccx_immunizationid</c:key>            <c:value i:type="d:guid" xmlns:d="http://schemas.microsoft.com/2003/10/Serialization/">766c2b0e-a349-e411-866c-00155d1e1f77</c:value>          </b:KeyValuePairOfstringanyType>     </b:Attributes>  </ns4:Entity><ns4:Entity>        <b:Attributes xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic">            <b:KeyValuePairOfstringanyType>            <c:key>ccx_datevaccineinfostatementpresented</c:key>            <c:value i:type="d:dateTime" xmlns:d="http://www.w3.org/2001/XMLSchema">2012-08-14T04:00:00Z</c:value>          </b:KeyValuePairOfstringanyType>          <b:KeyValuePairOfstringanyType>            <c:key>ccx_administeredamount</c:key>            <c:value i:type="d:decimal" xmlns:d="http://www.w3.org/2001/XMLSchema">0.7500000000</c:value>          </b:KeyValuePairOfstringanyType>          <b:KeyValuePairOfstringanyType>            <c:key>ccx_immunizationid</c:key>            <c:value i:type="d:guid" xmlns:d="http://schemas.microsoft.com/2003/10/Serialization/">826c2b0e-a349-e411-866c-00155d1e1f77</c:value>          </b:KeyValuePairOfstringanyType>     </b:Attributes>  </ns4:Entity></Entities></ns0:RetrieveMultipleResult></ns0:RetrieveMultipleResponse>


    I have  to count the distinct Entities based on ccx_immunizationid value & also I have to group the fields if both entity has same immunization ID. If the xml file (without namespace) and the field is <ccx_immunizationid>2323</ccx_imunizationid>, I can use something like below,

    <xsl:template match="//Entity/Attributes[not(./ccx_immunizationid= preceding::ccx_immunizationid)]">

    but here, I have to look for the key 'ccx_immunizationid' and its value. 

    I am using custom xslt in BizTalk map. Please help. Thanks.

    Regards,

    Lakshmi

    Friday, January 9, 2015 1:28 PM

Answers

  • You need to get the distinct record filtered by "value" field whose "key" is "ccx_immunizationid" i.e <c:key>ccx_immunizationid</c:key>".

    So you need to use the XPath with syntax: 

    /*[not(@name = preceding::*/@name)]

    So in your case, the XPath value will be like the following:

    /*[local-name()='RetrieveMultipleResponse' and namespace-uri()='http://schemas.microsoft.com/xrm/2011/Contracts/Services']/*[local-name()='RetrieveMultipleResult' and namespace-uri()='http://schemas.microsoft.com/xrm/2011/Contracts/Services']/*[local-name()='Entities' and namespace-uri()='http://schemas.microsoft.com/xrm/2011/Contracts']/*[local-name()='Entity' and namespace-uri()='http://schemas.microsoft.com/xrm/2011/Contracts']/*[local-name()='Attributes' and namespace-uri()='http://schemas.microsoft.com/xrm/2011/Contracts']/*[local-name()='KeyValuePairOfstringanyType' and namespace-uri()='http://schemas.microsoft.com/xrm/2011/Contracts'][*[local-name()='key' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Collections.Generic']/text() ='ccx_immunizationid'][not(*[local-name()='value' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Collections.Generic'] = preceding::*[local-name()='value' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Collections.Generic'])]
    You may need to change the namespaces according to yours. This XPath would give you an idea of how to use the distinct XPath (1.0) snystax as shown above in your XML.



    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.

    • Marked as answer by Laxme Sunday, January 11, 2015 7:30 PM
    Sunday, January 11, 2015 7:27 PM
  • Also another thing, which i have to empahsis is.

    Once the distinct has been achvied, you need to group the record using Muenchian Grouping. Following article shall help to apply the Muenchian Grouping.

    http://blogs.msdn.com/b/chrisromp/archive/2008/07/31/muenchian-grouping-and-sorting-in-biztalk-maps.aspx

    https://code.msdn.microsoft.com/windowsdesktop/Muenchian-Grouping-and-790347d2


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.

    • Marked as answer by Laxme Sunday, January 11, 2015 7:30 PM
    Sunday, January 11, 2015 7:29 PM

All replies

  • You need to get the distinct record filtered by "value" field whose "key" is "ccx_immunizationid" i.e <c:key>ccx_immunizationid</c:key>".

    So you need to use the XPath with syntax: 

    /*[not(@name = preceding::*/@name)]

    So in your case, the XPath value will be like the following:

    /*[local-name()='RetrieveMultipleResponse' and namespace-uri()='http://schemas.microsoft.com/xrm/2011/Contracts/Services']/*[local-name()='RetrieveMultipleResult' and namespace-uri()='http://schemas.microsoft.com/xrm/2011/Contracts/Services']/*[local-name()='Entities' and namespace-uri()='http://schemas.microsoft.com/xrm/2011/Contracts']/*[local-name()='Entity' and namespace-uri()='http://schemas.microsoft.com/xrm/2011/Contracts']/*[local-name()='Attributes' and namespace-uri()='http://schemas.microsoft.com/xrm/2011/Contracts']/*[local-name()='KeyValuePairOfstringanyType' and namespace-uri()='http://schemas.microsoft.com/xrm/2011/Contracts'][*[local-name()='key' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Collections.Generic']/text() ='ccx_immunizationid'][not(*[local-name()='value' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Collections.Generic'] = preceding::*[local-name()='value' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Collections.Generic'])]
    You may need to change the namespaces according to yours. This XPath would give you an idea of how to use the distinct XPath (1.0) snystax as shown above in your XML.



    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.

    • Marked as answer by Laxme Sunday, January 11, 2015 7:30 PM
    Sunday, January 11, 2015 7:27 PM
  • Also another thing, which i have to empahsis is.

    Once the distinct has been achvied, you need to group the record using Muenchian Grouping. Following article shall help to apply the Muenchian Grouping.

    http://blogs.msdn.com/b/chrisromp/archive/2008/07/31/muenchian-grouping-and-sorting-in-biztalk-maps.aspx

    https://code.msdn.microsoft.com/windowsdesktop/Muenchian-Grouping-and-790347d2


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.

    • Marked as answer by Laxme Sunday, January 11, 2015 7:30 PM
    Sunday, January 11, 2015 7:29 PM