XSLT Transform in SSIS XML task to output only certain nodes of XML document

Question

Given the following XML document.

<?xml version="1.0" encoding="UTF-8"?>
<TrainingCenterDatabase
  xsi:schemaLocation="http://www.garmin.com/xmlschemas/TrainingCenterDatabase/v2 http://www.garmin.com/xmlschemas/TrainingCenterDatabasev2.xsd"
  xmlns:ns5="http://www.garmin.com/xmlschemas/ActivityGoals/v1"
  xmlns:ns3="http://www.garmin.com/xmlschemas/ActivityExtension/v2"
  xmlns:ns2="http://www.garmin.com/xmlschemas/UserProfile/v2"
  xmlns="http://www.garmin.com/xmlschemas/TrainingCenterDatabase/v2"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns4="http://www.garmin.com/xmlschemas/ProfileExtension/v1">
  <Activities>
    <Activity Sport="Other">
      <Id>2017-01-13T19:26:50.000Z</Id>
      <Lap StartTime="2017-01-13T19:26:50.000Z">
        <TotalTimeSeconds>600.0</TotalTimeSeconds>
        <DistanceMeters>0.0</DistanceMeters>
        <Calories>42</Calories>
        <AverageHeartRateBpm>
          <Value>89</Value>
        </AverageHeartRateBpm>
        <MaximumHeartRateBpm>
          <Value>100</Value>
        </MaximumHeartRateBpm>
        <Intensity>Active</Intensity>
        <TriggerMethod>Manual</TriggerMethod>
        <Track>
          <Trackpoint>
            <Time>2017-01-13T19:26:50.000Z</Time>
            <AltitudeMeters>-51.599998474121094</AltitudeMeters>
            <HeartRateBpm>
              <Value>84</Value>
            </HeartRateBpm>
            <Extensions>
              <ns3:TPX/>
            </Extensions>
          </Trackpoint>
          <Trackpoint>
            <Time>2017-01-13T19:26:51.000Z</Time>
            <AltitudeMeters>-51.599998474121094</AltitudeMeters>
            <HeartRateBpm>
              <Value>84</Value>
            </HeartRateBpm>
            <Extensions>
              <ns3:TPX/>
            </Extensions>
          </Trackpoint>
          <Trackpoint>
            <Time>2017-01-13T19:26:54.000Z</Time>
            <AltitudeMeters>-0.6000000238418579</AltitudeMeters>
            <HeartRateBpm>
              <Value>84</Value>
            </HeartRateBpm>
            <Extensions>
              <ns3:TPX/>
            </Extensions>
          </Trackpoint>
        </Track>
        <Extensions>
          <ns3:LX/>
        </Extensions>
      </Lap>
      <Creator xsi:type="Device_t">
        <Name>Garmin Forerunner 910XT</Name>
        <UnitId>3881635667</UnitId>
        <ProductID>1328</ProductID>
        <Version>
          <VersionMajor>3</VersionMajor>
          <VersionMinor>20</VersionMinor>
          <BuildMajor>0</BuildMajor>
          <BuildMinor>0</BuildMinor>
        </Version>
      </Creator>
    </Activity>
  </Activities>
  <Author xsi:type="Application_t">
    <Name>Garmin Connect API</Name>
    <Build>
      <Version>
        <VersionMajor>16</VersionMajor>
        <VersionMinor>23</VersionMinor>
        <BuildMajor>0</BuildMajor>
        <BuildMinor>0</BuildMinor>
      </Version>
    </Build>
    <LangID>en</LangID>
    <PartNumber>006-D2449-00</PartNumber>
  </Author>
</TrainingCenterDatabase>

And the following XSLT. (I've been trying all kinds of different versions, but I can't even get it to export anything)

    <?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:gar="http://www.garmin.com/xmlschemas/TrainingCenterDatabasev2.xsd">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<xsl:copy-of select="gar:TrainingCenterDatabase/gar:Activities/gar:Activity/gar:Track"/>
</xsl:template>
</xsl:stylesheet>

I'm tearing my hair out trying to export only certain nodes of the XML.

EDIT. Added in the namespaces from the XML. EDIT. Added in the namespace and alias.

What I want is this.

  <Trackpoint>
    <Time>2017-01-13T19:26:54.000Z</Time>
    <HeartRateBpm>84</HeartRateBpm>
  </Trackpoint>
  <Trackpoint>
    <Time>2017-01-13T19:26:54.000Z</Time>
    <HeartRateBpm>84</HeartRateBpm>
  </Trackpoint>
  <Trackpoint>
    <Time>2017-01-13T19:27:54.000Z</Time>
    <HeartRateBpm>85</HeartRateBpm>
  </Trackpoint>
  <Trackpoint>
    <Time>2017-01-13T19:28:54.000Z</Time>
    <HeartRateBpm>90</HeartRateBpm>
  </Trackpoint>
  etc.

Any help is appreciated.

Looks like you're having trouble with namespaces. The answers to [this question](http://stackoverflow.com/questions/1730875/xslt-transform-xml-with-namespaces) may help. — teppic, Jan 16 '17 at 19:30
I updated the XLST with the namesspaces. It works when I use "*" in the copy-of select. But anytime I change it to look for anything else nada. — Dayton Brown, Jan 16 '17 at 21:37
You need to use a prefix - see: http://stackoverflow.com/a/34762628/3016153 Note that most of the namespace declarations in your XML (and subsequently, in your stylesheet), are unused and as such redundant. -- Note also that the output you show is not well-formed XML (has no single root element). — michael.hor257k, Jan 16 '17 at 22:18
Am I getting any closer? I added in the prefix and nothing. Thanks for the note on well formed xml. I'll fix that once I get it to actually export something :-) — Dayton Brown, Jan 16 '17 at 22:45

score 4 · Accepted Answer · answered Jan 16 '17 at 23:35

There are two reasons why your current attempt doesn't work:

The default namespace used by your input XML is "http://www.garmin.com/xmlschemas/TrainingCenterDatabase/v2", not "http://www.garmin.com/xmlschemas/TrainingCenterDatabasev2.xsd" as you're declaring in your stylesheet;
The path to the Track element is incorrect - you've missed the Lap step.

Fixing these two will get you a result that is a deep copy of the Track element.

However, I doubt that is the result you want, since it will be copied as is - including the default namespace. In order to get a result that is in no-namespace, you need to create new elements instead of copying from the source. Try, for example:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:gar="http://www.garmin.com/xmlschemas/TrainingCenterDatabase/v2"
exclude-result-prefixes="gar">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/gar:TrainingCenterDatabase">
    <Track>
        <xsl:for-each select="gar:Activities/gar:Activity/gar:Lap/gar:Track/gar:Trackpoint">
            <Trackpoint>
                <Time>
                    <xsl:value-of select="gar:Time"/>                   
                </Time>
                <HeartRateBpm>
                    <xsl:value-of select="gar:HeartRateBpm/gar:Value"/>
                </HeartRateBpm>
            </Trackpoint>
        </xsl:for-each>
    </Track>
</xsl:template>

</xsl:stylesheet>

Applied to your input example, the result will be:

<?xml version="1.0" encoding="UTF-8"?>
<Track>
   <Trackpoint>
      <Time>2017-01-13T19:26:50.000Z</Time>
      <HeartRateBpm>84</HeartRateBpm>
   </Trackpoint>
   <Trackpoint>
      <Time>2017-01-13T19:26:51.000Z</Time>
      <HeartRateBpm>84</HeartRateBpm>
   </Trackpoint>
   <Trackpoint>
      <Time>2017-01-13T19:26:54.000Z</Time>
      <HeartRateBpm>84</HeartRateBpm>
   </Trackpoint>
</Track>

XSLT Transform in SSIS XML task to output only certain nodes of XML document

1 Answers1