Extract from XML multi node a text file using SISS XML task editor

Question

I try without success to extract or also have e XML more simple to elaborate with a SSIS from a multi-node XML source.
I try this parse with XML task Editor...

From this XML

<?xml version="1.0" encoding="UTF-8"?>
<Feed xmlns="http://www.bazaarvoice.com/xs/PRR/SyndicationFeed/14.7" name="ciaotu" extractDate="2018-06-08T00:00:00.000Z">
    <Product disabled="false" removed="false" id="100002652">
        <ImageUrl>https://images.ciaotu.it/nobrand/0/lightbox/652/100002652/n94103.jpg</ImageUrl>
        <Name>HR 101 Carta Fotografica A3 106 gr / m2 100 Fogli</Name>
        <ExternalId>100002652</ExternalId>
        <Description>HR 101 Carta Fotografica ad Alta Risoluzione Formato A3 106 gr / m2 100 Fogli</Description>
        <Brand disabled="false">
            <ExternalId>2079</ExternalId>
            <Name>CANON</Name>
            <Attributes>
                <Attribute id="NAME">
                    <Value>CANON</Value>
                </Attribute>
            </Attributes>
        </Brand>
        <Source>ciaotu</Source>
        <ProductPageUrl>https://www.ciaotu.it/carta-fotografica-CANON/d-94103</ProductPageUrl>
        <NativeReviewStatistics>
            <AverageOverallRating>5.0</AverageOverallRating>
            <NotRecommendedCount>0</NotRecommendedCount>
            <OverallRatingRange>5</OverallRatingRange>
            <RatingsOnlyReviewCount>0</RatingsOnlyReviewCount>
            <RecommendedCount>1</RecommendedCount>
            <TotalReviewCount>1</TotalReviewCount>
            <AverageRatingValues>
                <AverageRatingValue id="Quality">
                    <AverageRating>5.0</AverageRating>
                    <RatingDimension displayType="NORMAL" selectedValueInDisplayEnabled="false" id="Quality">
                        <ExternalId>Quality</ExternalId>
                        <Label>Qualità</Label>
                        <Label1>Come valuteresti la qualità del prodotto?</Label1>
                        <RatingRange>5</RatingRange>
                    </RatingDimension>
                </AverageRatingValue>
                <AverageRatingValue id="Appearance1">
                    <AverageRating>5.0</AverageRating>
                    <RatingDimension displayType="NORMAL" selectedValueInDisplayEnabled="false" id="Appearance1">
                        <ExternalId>Appearance1</ExternalId>
                        <Label>Aspetto</Label>
                        <Label1>Come valuteresti l'aspetto del prodotto?</Label1>
                        <RatingRange>5</RatingRange>
                    </RatingDimension>
                </AverageRatingValue>
                <AverageRatingValue id="facilitaDuso">
                    <AverageRating>5.0</AverageRating>
                    <RatingDimension displayType="NORMAL" selectedValueInDisplayEnabled="false" id="facilitaDuso">
                        <ExternalId>facilitaDuso</ExternalId>
                        <Label>Facilità d'uso</Label>
                        <Label1>Come valuteresti la facilità d'uso?</Label1>
                        <RatingRange>5</RatingRange>
                    </RatingDimension>
                </AverageRatingValue>
                <AverageRatingValue id="Value">
                    <AverageRating>5.0</AverageRating>
                    <RatingDimension displayType="NORMAL" selectedValueInDisplayEnabled="false" id="Value">
                        <ExternalId>Value</ExternalId>
                        <Label>Rapporto qualità-prezzo</Label>
                        <Label1>Come valuteresti il rapporto qualità-prezzo del prodotto?</Label1>
                        <RatingRange>5</RatingRange>
                    </RatingDimension>
                </AverageRatingValue>
            </AverageRatingValues>
            <RatingDistribution>
                <RatingDistributionItem>
                    <RatingValue>5</RatingValue>
                    <Count>1</Count>
                </RatingDistributionItem>
            </RatingDistribution>
        </NativeReviewStatistics>
        <NumAnswers>0</NumAnswers>
        <NumNativeAnswers>0</NumNativeAnswers>
        <NumNativeQuestions>0</NumNativeQuestions>
        <NumQuestions>0</NumQuestions>
        <NumReviews>1</NumReviews>
        <ProductReviewsUrl>http://c7.ugc.bazaarvoice.com/17950/100002652/reviews.htm</ProductReviewsUrl>
        <ReviewStatistics>
            <AverageOverallRating>5.0</AverageOverallRating>
            <NotRecommendedCount>0</NotRecommendedCount>
            <OverallRatingRange>5</OverallRatingRange>
            <RatingsOnlyReviewCount>0</RatingsOnlyReviewCount>
            <RecommendedCount>1</RecommendedCount>
            <TotalReviewCount>1</TotalReviewCount>
            <AverageRatingValues>
                <AverageRatingValue id="Quality">
                    <AverageRating>5.0</AverageRating>
                    <RatingDimension displayType="NORMAL" selectedValueInDisplayEnabled="false" id="Quality">
                        <ExternalId>Quality</ExternalId>
                        <Label>Qualità</Label>
                        <Label1>Come valuteresti la qualità del prodotto?</Label1>
                        <RatingRange>5</RatingRange>
                    </RatingDimension>
                </AverageRatingValue>
                <AverageRatingValue id="Appearance1">
                    <AverageRating>5.0</AverageRating>
                    <RatingDimension displayType="NORMAL" selectedValueInDisplayEnabled="false" id="Appearance1">
                        <ExternalId>Appearance1</ExternalId>
                        <Label>Aspetto</Label>
                        <Label1>Come valuteresti l'aspetto del prodotto?</Label1>
                        <RatingRange>5</RatingRange>
                    </RatingDimension>
                </AverageRatingValue>
                <AverageRatingValue id="facilitaDuso">
                    <AverageRating>5.0</AverageRating>
                    <RatingDimension displayType="NORMAL" selectedValueInDisplayEnabled="false" id="facilitaDuso">
                        <ExternalId>facilitaDuso</ExternalId>
                        <Label>Facilità d'uso</Label>
                        <Label1>Come valuteresti la facilità d'uso?</Label1>
                        <RatingRange>5</RatingRange>
                    </RatingDimension>
                </AverageRatingValue>
                <AverageRatingValue id="Value">
                    <AverageRating>5.0</AverageRating>
                    <RatingDimension displayType="NORMAL" selectedValueInDisplayEnabled="false" id="Value">
                        <ExternalId>Value</ExternalId>
                        <Label>Rapporto qualità-prezzo</Label>
                        <Label1>Come valuteresti il rapporto qualità-prezzo del prodotto?</Label1>
                        <RatingRange>5</RatingRange>
                    </RatingDimension>
                </AverageRatingValue>
            </AverageRatingValues>
            <RatingDistribution>
                <RatingDistributionItem>
                    <RatingValue>5</RatingValue>
                    <Count>1</Count>
                </RatingDistributionItem>
            </RatingDistribution>
        </ReviewStatistics>
        <CategoryItems>
            <CategoryItem id="6431">
                <ExternalId>6431</ExternalId>
                <CategoryName>Carta Fotografica</CategoryName>
                <CategoryId>1660316</CategoryId>
            </CategoryItem>
            <CategoryItem id="5812">
                <ExternalId>5812</ExternalId>
                <CategoryName>Toner, Cartucce &amp; Consumabili</CategoryName>
                <CategoryId>1634308</CategoryId>
            </CategoryItem>
            <CategoryItem id="5767">
                <ExternalId>5767</ExternalId>
                <CategoryName>Office &amp; Consumabili</CategoryName>
                <CategoryId>1633507</CategoryId>
            </CategoryItem>
        </CategoryItems>
        <EANs>
            <EAN>4960999868554</EAN>
        </EANs>
        <ManufacturerPartNumbers>
            <ManufacturerPartNumber>1033A005AB</ManufacturerPartNumber>
        </ManufacturerPartNumbers>
    </Product>
</Feed>

and using this XSLT...
I think something here is wrong...
I need these 5 XML values...

ExternalId;  
averageOverallRating;  
overallRatingRange;    
totalReviewCount;  
ratingsOnlyReviewCount;  
recommendedCount

I tried below many combinations to get a value, but nothing is output:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions">
  <xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>
  <xsl:template match="/">
<xsl:text>ExternalId;averageOverallRating;overallRatingRange;totalReviewCount;ratingsOnlyReviewCount;recommendedCount</xsl:text>
    <xsl:text>&#13;&#10;</xsl:text>
    <xsl:for-each select="Feed/Product/ReviewStatistics">
      <xsl:text>"</xsl:text>
        <xsl:value-of select="../../Product/ExternalId"/>
      <xsl:text>";"</xsl:text>
        <xsl:value-of select="../../../Product/ExternalId"/>
      <xsl:text>";"</xsl:text>
        <xsl:value-of select="../ExternalId"/>
      <xsl:text>"</xsl:text>
      <xsl:text>";"</xsl:text>
        <xsl:value-of select="ExternalId"/>
      <xsl:text>"</xsl:text>
      <xsl:text>&#13;&#10;</xsl:text>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

My result is always a header file without values...
Please let me know something.
ALen, Italy

Try adding `xpath-default-namespace="http://www.bazaarvoice.com/xs/PRR/SyndicationFeed/14.7"` to `xsl:stylesheet`. (Assuming SISS really supports XSLT 2.0. I'm not familiar with it.) — Daniel Haley, Jun 08 '18 at 22:32
SSIS is a Microsoft product, and almost certainly only supports XSLT 1.0. The issue is your XML has a default namespace in (the `xmlns=" xmlns="http://www.bazaarvoice.com..."` on the root `Feed` element), but you have not accounted for that namespace in your XSLT. See https://stackoverflow.com/questions/1344158/xslt-with-xml-source-that-has-a-default-namespace-set-to-xmlns for an example of how to handle a default namespace by associating it with a prefix in XSLT 1.0. — Tim C, Jun 09 '18 at 08:16
If your XLST, you will need to define the namespace prefix as `xmlns:my=" http://www.bazaarvoice.com/xs/PRR/SyndicationFeed/14.7"`. It is the namespace URI that must match the XML. The prefix you choose in the XSLT can actually be anything. — Tim C, Jun 10 '18 at 08:01

Extract from XML multi node a text file using SISS XML task editor

0 Answers0