Xslt stylesheet to get the id and number value from the provided XML

Question

I have been provided a Soap XML file which contains multiple records from where I need to fetch the data of Id,CaseNumber etc. Also this records has multiple "records" tag within "sf:Case_Responses_GCC_r" tag. I need to create an XSLT file which could help me in transforming this SOAP XML into the plain XML with those values.

The SOAP XML

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns="urn:enter.soap.force.com"
  xmlns:sf="urn:sobject.enter.soap.force.com"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Body>
    <queryMoreResponse>
      <result>
        <done>false</done>
        <records xsi:type="sf:Case">
          <sf:Id>6896SDRGrt868</sf:Id>
          <sf:PolicyNumber>445353566</sf:PolicyNumber>
           <sf:Case_Responses_GCC__r>
             <records xsi:type="sf:Response_GCC__c">
               <sf:Id xsi:nil="true"/>
               <sf:Question_GCC__c>Available to question?</sf:Question_GCC__c>
               <sf:Response_GCC__c>Yes</sf:Response_GCC__c>
             </records>
             <records xsi:type="sf:Response_GCC__c">
              <sf:Id xsi:nil="true"/>
              <sf:Question_GCC__c>Relationship</sf:Question_GCC__c>
              <sf:Response_GCC__c>Self</sf:Response_GCC__c>
             </records>
           </sf:Case_Responses_GCC__r>
        </records>
        <records xsi:type="sf:Case">
          <sf:Id>5003L005UCcfVVS</sf:Id>
          <sf:PolicyNumber>87768978</sf:PolicyNumber>
           <sf:Case_Responses_GCC__r>
             <records xsi:type="sf:Response_GCC__c">
               <sf:Id xsi:nil="true"/>
               <sf:Question_GCC__c>Available to question?</sf:Question_GCC__c>
               <sf:Response_GCC__c>No</sf:Response_GCC__c>
             </records>
             <records xsi:type="sf:Response_GCC__c">
              <sf:Id xsi:nil="true"/>
              <sf:Question_GCC__c>Relationship</sf:Question_GCC__c>
              <sf:Response_GCC__c>Father</sf:Response_GCC__c>
             </records>
           </sf:Case_Responses_GCC__r>
        </records>
      </result>
    </queryMoreResponse>
  </soapenv:Body>
</soapenv:Envelope>

My XSLT

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="2.0" xpath-default-namespace="urn:enter.soap.force.com"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:urn="urn:enter.soap.force.com"
  xmlns:sf="urn:sobject.enter.soap.force.com"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" exclude-result-prefixes="xsi sf urn">
    <xsl:output method="html" doctype-public="XSLT-compat" omit-xml-declaration="yes" encoding="UTF-8" indent="yes" />
    <xsl:strip-space elements="*"/>
    <xsl:template match="/">
        <report>
           <reportid>
             <xsl:value-of select="//records/sf:Id"/>
           </reportid>
           <policynumber>
             <xsl:value-of select="//records/sf:PolicyNumber"/>
           </policynumber>
        </report>
    </xsl:template>
</xsl:stylesheet>

As, I don't have much understanding of this XSLT format data, still I was able to extract something, but still that does not make sense as it does not have the tag with them just plain.

I need an output in XML like this each record in an array , as I will be looping over them and converting it to JSON format.

<?xml version="1.0" encoding="UTF-8"?>
<lic lang="en">
   <report>
      <reportversion>1</reportversion>
      <reportid>6896SDRGrt868</reportid>
      <policynum>445353566</policynum>
      <primarysourcecountry>IN</primarysourcecountry>
      <client>
         <summary>
                Available to question?: Yes
                Relationship: Self
         </summary>
      </client>
   </report>
   <report>
      <reportversion>1</reportversion>
      <reportid>5003L005UCcfVVS</reportid>
      <policynum>87768978</policynum>
      <primarysourcecountry>IN</primarysourcecountry>
      <client>
         <summary>
           Available to question?: No
           Relationship: Father     
         </summary>
      </client>
   </report>
</lic>

Answer 1

Try a powershell script

using assembly System.Xml.Linq

$inputFilename = "c:\temp\test.xml"
$outputFilename = "c:\temp\test1.xml"

$doc = [System.Xml.Linq.XDocument]::Load($inputFilename)
$ns = $doc.Root.GetDefaultNamespace()
$nsSf = $doc.Root.GetNamespaceOfPrefix('sf')

$xmlText = '<?xml version="1.0" encoding="UTF-8"?><lic lang="en"></lic>'
$doc1 = [System.Xml.Linq.XDocument]::Parse($xmlText)
$lic = $doc1.Root

$result = $doc.Descendants($ns + "result")[0]
$records = $result.Elements($ns + "records")

foreach($record in $records)
{
   $report = [System.Xml.Linq.XElement]::new([System.Xml.Linq.Xname]::get('report'))
   $lic.Add($report)

   $reportVersion = [System.Xml.Linq.XElement]::new([System.Xml.Linq.Xname]::get('reportversion'),1)
   $report.Add($reportVersion)

   $id = $record.Element($nsSf + 'Id').Value
   $reportId = [System.Xml.Linq.XElement]::new([System.Xml.Linq.Xname]::get('reportid'),$id)
   $report.Add($xId)

   $policyNumber = $record.Element($nsSf + 'PolicyNumber').Value
   $xPolicynum = [System.Xml.Linq.XElement]::new([System.Xml.Linq.Xname]::get('policynum'),$policyNumber)
   $report.Add($xPolicynum)

   $xPrimarysourcecountry = [System.Xml.Linq.XElement]::new([System.Xml.Linq.Xname]::get('primarysourcecountry'),'IN')
   $report.Add($xprimarysourcecountry)

   $client = [System.Xml.Linq.XElement]::new([System.Xml.Linq.Xname]::get('client'))
   $report.Add($client)

   $summary = [System.Xml.Linq.XElement]::new([System.Xml.Linq.Xname]::get('summary'))
   $client.Add($summary)

   $writer = [System.IO.StringWriter]::new()
   $writer.WriteLine()
   $responses = $record.Descendants($ns + 'records')
   foreach($response in $responses)
   {
      $question = $response.Element($nsSf + 'Question_GCC__c').Value
      $answer = $response.Element($nsSf + 'Response_GCC__c').Value
      $writer.WriteLine($question + ': ' + $answer)
   }
   $innertext = $writer.ToString()
   $summary.AddFirst([string]$innertext)
}

$doc1.Save($outputFilename)

Answer 2

Try something like:

<xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sf="urn:sobject.enter.soap.force.com"
xpath-default-namespace="urn:enter.soap.force.com"
exclude-result-prefixes="#all">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/soapenv:Envelope">
    <lic lang="en">
        <xsl:for-each select="soapenv:Body/queryMoreResponse/result/records">
            <report>
                <reportversion>1</reportversion>
                <reportid>
                    <xsl:value-of select="sf:Id"/>
                </reportid>
                <policynum>
                    <xsl:value-of select="sf:PolicyNumber"/>
                </policynum>
                <primarysourcecountry>IN</primarysourcecountry>
                <client>
                    <summary>
                        <xsl:for-each select="sf:Case_Responses_GCC__r/records">
                            <xsl:value-of select="sf:Question_GCC__c"/>
                            <xsl:text>: </xsl:text>
                            <xsl:value-of select="sf:Response_GCC__c"/>
                            <xsl:text>&#10;</xsl:text>
                        </xsl:for-each>
                    </summary>
                </client>
            </report>
        </xsl:for-each>
    </lic>
</xsl:template>

</xsl:stylesheet>