Hi all,

i am having .xml input file containing data as :

<USP_GetDataFromCacheResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
  <USP_GetDataFromCacheResult>
    <DataSet xmlns="http://schemas.datacontract.org/2004/07/System.Data">
      <xs:schema id="NewDataSet" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
        <xs:element msdata:IsDataSet="true" name="NewDataSet">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="NewTable">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element minOccurs="0" name="CDMXML" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:schema>
      <diffgr:diffgram xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
        <NewDataSet xmlns="">
          <NewTable>
            <CDMXML><![CDATA[<ns0:GetCreditScoreResponseMsg xmlns:ns0="http://abcgroup.com/xsd/CDM/v1"><ns0:MessageHeader><ns0:StatusCode>08000</ns0:StatusCode><ns0:StatusMessage>08000</ns0:StatusMessage><ns0:StatusMessage>0</ns0:StatusMessage><ns0:StatusMessage>SUCCESS</ns0:StatusMessage></ns0:MessageHeader><ns0:GetCreditScoreResponse><ns0:CreditScore><ns0:Subject><ns0:Id>232</ns0:Id><ns0:Name>Test</ns0:Name><ns0:PrimaryPhone>2241</ns0:PrimaryPhone><ns0:Fax>234</ns0:Fax><ns0:EntityType>C</ns0:EntityType><ns0:FirstName>Test FirstName</ns0:FirstName><ns0:LastName>TestLastName</ns0:LastName><ns0:MiddleName>Test MiddleName</ns0:MiddleName><ns0:BirthDate>2011-01-01</ns0:BirthDate><ns0:Address><ns0:AddressLine1>der</ns0:AddressLine1><ns0:AddressLine2>dert</ns0:AddressLine2><ns0:City>dert</ns0:City><ns0:State>UT</ns0:State><ns0:PostalCode>400097</ns0:PostalCode><ns0:Country>US</ns0:Country><ns0:GeoCode><ns0:Latititude>10.1</ns0:Latititude><ns0:Longitude>20.1</ns0:Longitude></ns0:GeoCode></ns0:Address><ns0:TaxIdNumber>123-12-1234</ns0:TaxIdNumber></ns0:Subject><ns0:Source>Transunion</ns0:Source><ns0:ScoreDate>2015-08-14</ns0:ScoreDate><ns0:Score>622</ns0:Score><ns0:Sign>-</ns0:Sign><ns0:Factors><ns0:Factor><ns0:Rank>1</ns0:Rank><ns0:Code>061</ns0:Code></ns0:Factor><ns0:Factor><ns0:Rank>2</ns0:Rank><ns0:Code>062</ns0:Code></ns0:Factor><ns0:Factor><ns0:Rank>3</ns0:Rank><ns0:Code>052</ns0:Code></ns0:Factor><ns0:Factor><ns0:Rank>4</ns0:Rank><ns0:Code>090</ns0:Code></ns0:Factor></ns0:Factors></ns0:CreditScore></ns0:GetCreditScoreResponse><ns0:ErrorDetails /></ns0:GetCreditScoreResponseMsg>]]></CDMXML>
          </NewTable>
        </NewDataSet>
      </diffgr:diffgram>
    </DataSet>
  </USP_GetDataFromCacheResult>
  <ReturnValue>0</ReturnValue>
</USP_GetDataFromCacheResponse>

I want to extract the value from CDMXML and i am looking for count of Factors.

what will be Xpath for that ? i get's value till CDMXML but as CDMXML having CDATA tag in it not able to go inside and check for Factors.

Load ur CDATA value to xml schema and use Xpath on that. Or u can xpath on the existing with correct xpath. Have u tried

Hi Nithin,

use the below xpath.

- extract CDMXML and load to new xml document message, load as string.

// To extract CDMXML, load this in new xmldocument instance 

string(/*[local-name()='USP_GetDataFromCacheResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='USP_GetDataFromCacheResult' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='DataSet' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Data']/*[local-name()='diffgram' and namespace-uri()='urn:schemas-microsoft-com:xml-diffgram-v1']/*[local-name()='NewDataSet' and namespace-uri()='']/*[local-name()='NewTable' and namespace-uri()='']/*[local-name()='CDMXML' and namespace-uri()=''])

- extract the count using factor count xpath.

// To Get the count of factors from the CData section 

count(/*[local-name()='GetCreditScoreResponseMsg' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='GetCreditScoreResponse' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='CreditScore' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factors' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factor' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1'])

Refer the below thread to know how to get xpath : Click here

Thanks, SMSVikasK

• Proposed as answer by Rachit Sikroria 22 hours 2 minutes ago

Hi Nitin,

Please refer below article that will help you,

CDATA Mapping Experience inside BizTalk

What you are seeing is correct since all the CDATA content is treated as one value, is not seen as Xml.

However, that is not really the problem you have.  One problem here is that you are using an UnTyped response which is almost never the right thing to do.

Are you or your team also the author of the USP_GetDataFromCache Stored Procedure?  If so, the much better way to handle this is to refactor that sp to return GetCreditScoreREsponseMsg as first class Xml, not a string value.

• Proposed as answer by Abhishek0127[Abhishek kumar]MVP 19 hours 50 minutes ago

Hi,

You can use below expression inside your expression shape

Steps: Create a variable of type Int name it as intFactorCount

intFactorCount =xpath(GetCreditScoreResponseMsg,count(/*[local-name()='GetCreditScoreResponseMsg' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='GetCreditScoreResponse' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='CreditScore' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factors' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factor' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1'])

Now for getting CDATA section from the Xpath node use below code

strvar= xpath(ResponseMessage,string(/*[local-name()='USP_GetDataFromCacheResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='USP_GetDataFromCacheResult' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='DataSet' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Data']/*[local-name()='diffgram' and namespace-uri()='urn:schemas-microsoft-com:xml-diffgram-v1']/*[local-name()='NewDataSet' and namespace-uri()='']/*[local-name()='NewTable' and namespace-uri()='']/*[local-name()='CDMXML' and namespace-uri()='']))

were strvar is a variable of type string in Orchestration

Thanks

Abhishek

Hi Nithin,

use the below xpath.

- extract CDMXML and load to new xml document message, load as string.

// To extract CDMXML, load this in new xmldocument instance 

string(/*[local-name()='USP_GetDataFromCacheResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='USP_GetDataFromCacheResult' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='DataSet' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Data']/*[local-name()='diffgram' and namespace-uri()='urn:schemas-microsoft-com:xml-diffgram-v1']/*[local-name()='NewDataSet' and namespace-uri()='']/*[local-name()='NewTable' and namespace-uri()='']/*[local-name()='CDMXML' and namespace-uri()=''])

- extract the count using factor count xpath.

// To Get the count of factors from the CData section 

count(/*[local-name()='GetCreditScoreResponseMsg' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='GetCreditScoreResponse' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='CreditScore' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factors' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factor' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1'])

Refer the below thread to know how to get xpath : Click here

Thanks, SMSVikasK

• Proposed as answer by Rachit Sikroria Monday, August 31, 2015 9:03 AM

Hi Nithin,

use the below xpath.

- extract CDMXML and load to new xml document message, load as string.

// To extract CDMXML, load this in new xmldocument instance 

string(/*[local-name()='USP_GetDataFromCacheResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='USP_GetDataFromCacheResult' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='DataSet' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Data']/*[local-name()='diffgram' and namespace-uri()='urn:schemas-microsoft-com:xml-diffgram-v1']/*[local-name()='NewDataSet' and namespace-uri()='']/*[local-name()='NewTable' and namespace-uri()='']/*[local-name()='CDMXML' and namespace-uri()=''])

- extract the count using factor count xpath.

// To Get the count of factors from the CData section 

count(/*[local-name()='GetCreditScoreResponseMsg' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='GetCreditScoreResponse' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='CreditScore' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factors' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factor' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1'])

Refer the below thread to know how to get xpath : Click here

Thanks, SMSVikasK

• Proposed as answer by Rachit Sikroria Monday, August 31, 2015 9:03 AM

Hi Nithin,

use the below xpath.

- extract CDMXML and load to new xml document message, load as string.

// To extract CDMXML, load this in new xmldocument instance 

string(/*[local-name()='USP_GetDataFromCacheResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='USP_GetDataFromCacheResult' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='DataSet' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Data']/*[local-name()='diffgram' and namespace-uri()='urn:schemas-microsoft-com:xml-diffgram-v1']/*[local-name()='NewDataSet' and namespace-uri()='']/*[local-name()='NewTable' and namespace-uri()='']/*[local-name()='CDMXML' and namespace-uri()=''])

- extract the count using factor count xpath.

// To Get the count of factors from the CData section 

count(/*[local-name()='GetCreditScoreResponseMsg' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='GetCreditScoreResponse' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='CreditScore' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factors' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factor' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1'])

Refer the below thread to know how to get xpath : Click here

Thanks, SMSVikasK

• Proposed as answer by Rachit Sikroria Monday, August 31, 2015 9:03 AM

What you are seeing is correct since all the CDATA content is treated as one value, is not seen as Xml.

However, that is not really the problem you have.  One problem here is that you are using an UnTyped response which is almost never the right thing to do.

Are you or your team also the author of the USP_GetDataFromCache Stored Procedure?  If so, the much better way to handle this is to refactor that sp to return GetCreditScoreREsponseMsg as first class Xml, not a string value.

• Proposed as answer by Abhishek0127[Abhishek kumar]MVP Monday, August 31, 2015 11:15 AM

What you are seeing is correct since all the CDATA content is treated as one value, is not seen as Xml.

However, that is not really the problem you have.  One problem here is that you are using an UnTyped response which is almost never the right thing to do.

Are you or your team also the author of the USP_GetDataFromCache Stored Procedure?  If so, the much better way to handle this is to refactor that sp to return GetCreditScoreREsponseMsg as first class Xml, not a string value.

• Proposed as answer by Abhishek0127[Abhishek kumar]MVP Monday, August 31, 2015 11:15 AM

What you are seeing is correct since all the CDATA content is treated as one value, is not seen as Xml.

However, that is not really the problem you have.  One problem here is that you are using an UnTyped response which is almost never the right thing to do.

Are you or your team also the author of the USP_GetDataFromCache Stored Procedure?  If so, the much better way to handle this is to refactor that sp to return GetCreditScoreREsponseMsg as first class Xml, not a string value.

• Proposed as answer by Abhishek0127[Abhishek kumar]MVP Monday, August 31, 2015 11:15 AM
• Marked as answer by Angie xuMicrosoft contingent staff, Moderator 18 hours 35 minutes ago

What you are seeing is correct since all the CDATA content is treated as one value, is not seen as Xml.

However, that is not really the problem you have.  One problem here is that you are using an UnTyped response which is almost never the right thing to do.

Are you or your team also the author of the USP_GetDataFromCache Stored Procedure?  If so, the much better way to handle this is to refactor that sp to return GetCreditScoreREsponseMsg as first class Xml, not a string value.

• Proposed as answer by Abhishek0127[Abhishek kumar]MVP Monday, August 31, 2015 11:15 AM
• Marked as answer by Angie xuMicrosoft contingent staff, Moderator Tuesday, September 08, 2015 12:26 PM

Hi,

You can use below expression inside your expression shape

Steps: Create a variable of type Int name it as intFactorCount

intFactorCount =xpath(GetCreditScoreResponseMsg,count(/*[local-name()='GetCreditScoreResponseMsg' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='GetCreditScoreResponse' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='CreditScore' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factors' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factor' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1'])

Now for getting CDATA section from the Xpath node use below code

strvar= xpath(ResponseMessage,string(/*[local-name()='USP_GetDataFromCacheResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='USP_GetDataFromCacheResult' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='DataSet' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Data']/*[local-name()='diffgram' and namespace-uri()='urn:schemas-microsoft-com:xml-diffgram-v1']/*[local-name()='NewDataSet' and namespace-uri()='']/*[local-name()='NewTable' and namespace-uri()='']/*[local-name()='CDMXML' and namespace-uri()='']))

were strvar is a variable of type string in Orchestration

Thanks

Abhishek

• Proposed as answer by Angie xuMicrosoft contingent staff, Moderator 3 hours 43 minutes ago

Hi,

You can use below expression inside your expression shape

Steps: Create a variable of type Int name it as intFactorCount

intFactorCount =xpath(GetCreditScoreResponseMsg,count(/*[local-name()='GetCreditScoreResponseMsg' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='GetCreditScoreResponse' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='CreditScore' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factors' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factor' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1'])

Now for getting CDATA section from the Xpath node use below code

strvar= xpath(ResponseMessage,string(/*[local-name()='USP_GetDataFromCacheResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='USP_GetDataFromCacheResult' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='DataSet' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Data']/*[local-name()='diffgram' and namespace-uri()='urn:schemas-microsoft-com:xml-diffgram-v1']/*[local-name()='NewDataSet' and namespace-uri()='']/*[local-name()='NewTable' and namespace-uri()='']/*[local-name()='CDMXML' and namespace-uri()='']))

were strvar is a variable of type string in Orchestration

Thanks

Abhishek

• Proposed as answer by Angie xuMicrosoft contingent staff, Moderator Monday, September 07, 2015 3:19 AM

Hi,

You can use below expression inside your expression shape

Steps: Create a variable of type Int name it as intFactorCount

intFactorCount =xpath(GetCreditScoreResponseMsg,count(/*[local-name()='GetCreditScoreResponseMsg' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='GetCreditScoreResponse' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='CreditScore' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factors' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factor' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1'])

Now for getting CDATA section from the Xpath node use below code

strvar= xpath(ResponseMessage,string(/*[local-name()='USP_GetDataFromCacheResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='USP_GetDataFromCacheResult' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='DataSet' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Data']/*[local-name()='diffgram' and namespace-uri()='urn:schemas-microsoft-com:xml-diffgram-v1']/*[local-name()='NewDataSet' and namespace-uri()='']/*[local-name()='NewTable' and namespace-uri()='']/*[local-name()='CDMXML' and namespace-uri()='']))

were strvar is a variable of type string in Orchestration

Thanks

Abhishek

• Proposed as answer by Angie xuMicrosoft contingent staff, Moderator Monday, September 07, 2015 3:19 AM
• Marked as answer by Angie xuMicrosoft contingent staff, Moderator 18 hours 35 minutes ago

Hi,

You can use below expression inside your expression shape

Steps: Create a variable of type Int name it as intFactorCount

intFactorCount =xpath(GetCreditScoreResponseMsg,count(/*[local-name()='GetCreditScoreResponseMsg' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='GetCreditScoreResponse' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='CreditScore' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factors' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1']/*[local-name()='Factor' and namespace-uri()='http://abcgroup.com/xsd/CDM/v1'])

Now for getting CDATA section from the Xpath node use below code

strvar= xpath(ResponseMessage,string(/*[local-name()='USP_GetDataFromCacheResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='USP_GetDataFromCacheResult' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='DataSet' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Data']/*[local-name()='diffgram' and namespace-uri()='urn:schemas-microsoft-com:xml-diffgram-v1']/*[local-name()='NewDataSet' and namespace-uri()='']/*[local-name()='NewTable' and namespace-uri()='']/*[local-name()='CDMXML' and namespace-uri()='']))

were strvar is a variable of type string in Orchestration

Thanks

Abhishek

• Proposed as answer by Angie xuMicrosoft contingent staff, Moderator Monday, September 07, 2015 3:19 AM
• Marked as answer by Angie xuMicrosoft contingent staff, Moderator Tuesday, September 08, 2015 12:26 PM

HI Abhishek,

I am doing Unittest for this and trying to call this from C# file - code.

Is there a way to call this from C# code?

Hi Nitin,

If you want to validate the xpath result through external assembly then you can call the assembly function through expression shape  and validate the result.

Now if you want to do unit testing for your Orchestration then its totally a different discussion thread required :)

Thanks

Abhishek

Yeah, it's totally different.  There's really no point in unit testing this with external code.

What exactly are you trying to accomplish?