Read XML apply XSLT and format the data

navik_pathak · April 27th, 2009, 05:50 PM

Hi All,

I am new to this forum as well as XSLT. Please help me out in following.

I have data coming using a Web Service Call as following

Code:

 
<?xml version="1.0" encoding="UTF-8"?>
- <AccountWS_AccountQueryPage_Output xmlns="urn:crmondemand/ws/account/10/2004" xmlns:p0="urn:crmondemand/ws/account/10/2004" xmlns:xsi="(URL address blocked: See forum rules)" xsi:type="p0:AccountWS_AccountQueryPage_Output">
<ns:LastPage xmlns:ns="urn:crmondemand/ws/account/10/2004">true</ns:LastPage>
- <ListOfAccount xmlns="urn:/crmondemand/xml/account" xmlns:ns="urn:crmondemand/ws/account/10/2004">
- <Account>
<AccountId>AAFA-2M15SU</AccountId>
<CurrencyCode>USD</CurrencyCode>
<AccountName>Big Industry Inc.</AccountName>
- <ListOfAddress>
- <Address>
<ExternalSystemId/>
<AddressId>AAFA-2M15SW</AddressId>
<City>Capital City</City>
<Country>USA</Country>
<County/>
<ZipCode>11111</ZipCode>
<Province/>
<StateProvince>CA</StateProvince>
<Address>746 Industry Way</Address>
<StreetAddress2/>
<StreetAddress3/>
</Address>
- <Address>
<ExternalSystemId/>
<AddressId>AAFA-2M15SY</AddressId>
<City>Capital City</City>
<Country>USA</Country>
<County/>
<ZipCode>11111</ZipCode>
<Province/>
<StateProvince>CA</StateProvince>
<Address>746 Industry Way</Address>
<StreetAddress2/>
<StreetAddress3/>
</Address>
</ListOfAddress>
</Account>

So here one Account can have multiple addresses. I need to add AccountId into Address repeating Block so that i can have following structure

1)
<AccountId>AAFA-2M15SU</AccountId>
<AddressId>AAFA-2M15SW</AddressId>
<Country>USA</Country>
<County/>
<Address>746 Industry Way</Address>
<StreetAddress2/>
<StreetAddress3/>
<City>Capital City</City>
<StateProvince>CA</StateProvince>
<ZipCode>11111</ZipCode>
<Province/>
<ExternalSystemId/>
2)
<AccountId>AAFA-2M15SU</AccountId>
<AddressId>AAFA-2M15SY</AddressId>
<Country>USA</Country>
<County/>
<Address>746 Industry Way</Address>
<StreetAddress2/>
<StreetAddress3/>
<City>Capital City</City>
<StateProvince>CA</StateProvince>
<ZipCode>11111</ZipCode>
<Province/>
<ExternalSystemId/>

how can make a XSLT file which will take the input on XML and produce to 2 records with AccountId in them.

Thanks in advance.....

mhkay · April 27th, 2009, 06:13 PM

Try:

Code:

<xsl:template match="Address">
  <Address>
     <xsl:copy-of select="../AccountId"/>
     <xsl:copy-of select="*"/>
  </Address>
</xsl:template>

navik_pathak · April 27th, 2009, 06:25 PM

Thanks mhkay for reply but it is not coping my xslt looks like following let me know if some thing looks wrong

Code:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet exclude-result-prefixes="exslt saxon bpws cis ihmap p0 p1" version="2.0" xmlns:bpws="http://schemas.xmlsoap.org/ws/2003/03/business-process/" xmlns:cis="http://www.approuter.com/schemas/2003/1/UserCallouts/" xmlns:exslt="http://exslt.org/common" xmlns:ihmap="http://www.approuter.com/xmlns/2002/Mapping" xmlns:p0="urn:crmondemand/ws/account/10/2004" xmlns:p1="urn:/crmondemand/xml/account" xmlns:saxon="http://saxon.sf.net/" xmlns:xml="http://www.w3.org/XML/1998/namespace" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output encoding="UTF-8" indent="yes" method="xml"/>
 <xsl:variable name="srcDoc1" select="bpws:getVariableData('Address_Of_Accounts')"/>
<xsl:template match="/">
  <xsl:element name="ReadAddressCSV" namespace="">
    <xsl:for-each select="$srcDoc1/p0:AccountWS_AccountQueryPage_Output/p1:ListOfAccount/p1:Account">
     <xsl:if test="count(.) > 0">
      <xsl:element name="Account" namespace="">
       <xsl:if test="p1:ListOfAddress">
        <xsl:element name="ListOfAddress" namespace="">
         <xsl:for-each select="p1:ListOfAddress/p1:Address">
          <xsl:if test="count(.) > 0">
           <xsl:if test="p1:AccountId">
            <xsl:element name="AccountId" namespace="">
             <xsl:copy-of select="$srcDoc1/p0:AccountWS_AccountQueryPage_Output/p1:ListOfAccount/p1:Account/p1:RowId"/>
           </xsl:element>
           </xsl:if>
           <xsl:if test="p1:AddressId">
            <xsl:element name="AddressId" namespace="">
             <xsl:value-of select="p1:AddressId"/>
            </xsl:element>
           </xsl:if>
           <xsl:if test="p1:Country">
            <xsl:element name="Country" namespace="">
             <xsl:value-of select="p1:Country"/>
            </xsl:element>
           </xsl:if>
           <xsl:if test="p1:County">
            <xsl:element name="County" namespace="">
             <xsl:value-of select="p1:County"/>
            </xsl:element>
           </xsl:if>
           <xsl:if test="p1:Address">
            <xsl:element name="Address" namespace="">
             <xsl:value-of select="p1:Address"/>
            </xsl:element>
           </xsl:if>
           <xsl:if test="p1:StreetAddress2">
            <xsl:element name="StreetAddress2" namespace="">
             <xsl:value-of select="p1:StreetAddress2"/>
            </xsl:element>
           </xsl:if>
           <xsl:if test="p1:StreetAddress3">
            <xsl:element name="StreetAddress3" namespace="">
             <xsl:value-of select="p1:StreetAddress3"/>
            </xsl:element>
           </xsl:if>
           <xsl:if test="p1:City">
            <xsl:element name="City" namespace="">
             <xsl:value-of select="p1:City"/>
            </xsl:element>
           </xsl:if>
           <xsl:if test="p1:StateProvince">
            <xsl:element name="StateProvince" namespace="">
             <xsl:value-of select="p1:StateProvince"/>
            </xsl:element>
           </xsl:if>
           <xsl:if test="p1:ZipCode">
            <xsl:element name="ZipCode" namespace="">
             <xsl:value-of select="p1:ZipCode"/>
            </xsl:element>
           </xsl:if>
           <xsl:if test="p1:Province">
            <xsl:element name="Province" namespace="">
             <xsl:value-of select="p1:Province"/>
            </xsl:element>
           </xsl:if>
           <xsl:if test="p1:ExternalSystemId">
            <xsl:element name="ExternalSystemId" namespace="">
             <xsl:value-of select="p1:ExternalSystemId"/>
            </xsl:element>
           </xsl:if>
          </xsl:if>
         </xsl:for-each>
        </xsl:element>
       </xsl:if>
      </xsl:element>
     </xsl:if>
    </xsl:for-each>
  </xsl:element>
 </xsl:template>
</xsl:stylesheet>

mhkay · April 28th, 2009, 03:26 AM

I would strongly recommend that you write your code in terms of template rules rather than a single monolithic template with lots of deeply nested xsl:for-each and xsl:if instructions. That makes it much easier to modify the code when the requirements or the source document changes.

Also, using literal result elements rather than xsl:element makes the code much more readable.

The key thing you don't seem to have grasped about the code I gave you was the use of .. to select the parent of the context node. You seem to be trying to select downwards from the root, which is wrong - it will give you the first account number, rather than the one associated with the current address.

mrame · April 28th, 2009, 05:12 AM

Try the below stylesheet:

Code:

<xsl:stylesheet exclude-result-prefixes="exslt saxon bpws cis ihmap p0 p1" version="2.0" ...>
<xsl:output encoding="UTF-8" indent="yes" method="xml"/>
<!--<xsl:variable name="srcDoc1" select="bpws:getVariableData('Address_Of_Accounts')"/>-->
<xsl:template match="/">
<xsl:element name="ReadAddressCSV" namespace="">
<xsl:for-each select="p0:AccountWS_AccountQueryPage_Output/p1:ListOfAccount/p1:Account">
<xsl:if test="count(.) > 0">
<xsl:element name="Account" namespace="">
<xsl:if test="p1:ListOfAddress">
<xsl:element name="ListOfAddress" namespace="">
<xsl:for-each select="p1:ListOfAddress/p1:Address">
<xsl:if test="count(.) > 0">
<xsl:if test="../../p1:AccountId">
<xsl:element name="AccountId" namespace="">
<xsl:value-of select="../../p1:AccountId"/>
</xsl:element>
</xsl:if>
...All the other code are same as yours.

1. I have discarded the variable srcDoc1, because I could not find Address_Of_Accounts in the input xml you supplied.
2. So I deleted all reference to $srcDoc1.
3. Then I changed <xsl:if test="p1:AccountId"> to <xsl:if test="../../p1:AccountId">
4. Atlast I changed <xsl:copy-of select="p0:AccountWS_AccountQueryPage_Output/p1:ListOfAccount/p1:Account/p1:RowId"/> to <xsl:value-of select="../../p1:AccountId"/>

navik_pathak · April 28th, 2009, 11:43 AM

Thanks Rummy for the reply Address_Of_Account is input to this XSLT. This is like chain of operation i am doing and when the time comes to format data into specific format write them in flat file that why i need the data in flat structure rather than XML format.