Extracting value using Xpath

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.

August 31st, 2015 3:20am

Load ur CDATA value to xml schema and use Xpath on that.
Free Windows Admin Tool Kit Click here and download it now
August 31st, 2015 3:31am

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

August 31st, 2015 3:44am

Hi Nitin,

Please refer below article that will help you,

CDATA Mapping Experience inside BizTalk

Free Windows Admin Tool Kit Click here and download it now
August 31st, 2015 3:45am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics