flatten XML nested node with XSLT

Question

goal : import a CRM dynamics odata xml file into SSIS XML transformation block.

SSIS contraints :

• only one namespace
• no nested nodes
• remove "un necessary" link node

simplified xml source

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml:base="https://XXXXXXXXXX/XRMServices/2011/OrganizationData.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
  <title type="text">allianceSet</title>
  <entry>
    <link rel="edit" title="be_alliance" href="be_allianceSet(guid'429352cb-deca-e311-80c0-00155d5682af')" />
    <link rel="XXXX" type="application/atom+xml;type=entry" title="lk_be_alliance_createdby" href="be_allianceSet/lk_be_alliance_createdby" />
    <link rel="XXXX" type="application/atom+xml;type=entry" title="lk_be_alliance_createdonbehalfby" href="be_allianceSet(/lk_be_alliance_createdonbehalfby" />
    <content type="application/xml">
      <m:properties>
        <d:ModifiedOn m:type="Edm.DateTime">2015-10-30T13:06:31Z</d:ModifiedOn>
        <d:CreatedBy m:type="Microsoft.Crm.Sdk.Data.Services.EntityReference" m:null="true">
          <d:Id m:type="Edm.Guid">40f20074-ede3-48cc-aafc-750a1275b99b</d:Id>
          <d:LogicalName>systemuser</d:LogicalName>
        </d:CreatedBy>
        <d:statecode m:type="Microsoft.Crm.Sdk.Data.Services.OptionSetValue">
          <d:Value m:type="Edm.Int32">0</d:Value>
        </d:statecode>
      </m:properties>
    </content>
  </entry>
</feed>

XSL input 1.0

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    <xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>

    <xsl:template match="*[name() != 'link']">
        <xsl:element name="{local-name()}">
            <xsl:apply-templates select="@* | node()"/>
        </xsl:element>
    </xsl:template>

    <!-- template to copy attributes -->
    <xsl:template match="@*">
        <xsl:attribute name="{local-name()}">
            <xsl:value-of select="."/>
        </xsl:attribute>
    </xsl:template>
</xsl:stylesheet>

above xsl perfectly remove namespaces. however I'm trying to transform/flatten each node of type="Microsoft.Crm.Sdk.Data.Services.EntityReference" with only one guid value

...    
<CreatedBy type="Microsoft.Crm.Sdk.Data.Services.EntityReference" null="true">
    <Id type="Edm.Guid">40f20074-ede3-48cc-aafc-750a1275b99b</Id>
    <LogicalName>systemuser</LogicalName>
    </CreatedBy>
...

into

...
    <CreatedBy>40f20074-ede3-48cc-aafc-750a1275b99b</CreatedBy>
...

so far without any luck ... any help appreciated

Answer 1

It looks like you are avoiding trying to reference any specific namespace in your XSLT. If so, to match node with a a type attribute of "Microsoft.Crm.Sdk.Data.Services.EntityReference", you would have a template match like this:

<xsl:template match="*[@*[local-name() = 'type']='Microsoft.Crm.Sdk.Data.Services.EntityReference']">

Try this XSLT

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>

    <xsl:template match="*">
        <xsl:element name="{local-name()}">
            <xsl:apply-templates select="@* | node()"/>
        </xsl:element>
    </xsl:template>

    <xsl:template match="*[local-name() = 'link']" />

    <xsl:template match="*[@*[local-name() = 'type']='Microsoft.Crm.Sdk.Data.Services.EntityReference']">
        <xsl:element name="{local-name()}">
            <xsl:value-of select="*[local-name() = 'Id']" />
        </xsl:element>
    </xsl:template>

    <!-- template to copy attributes -->
    <xsl:template match="@*">
        <xsl:attribute name="{local-name()}">
            <xsl:value-of select="."/>
        </xsl:attribute>
    </xsl:template>
</xsl:stylesheet>

Note how I have created a separate template to remove the link elements. With your current XSLT, the link element will actually be matched by XSLT's built-in templates, which would output any text nodes under link if there were any (which might not be the case for you, but might be better to code for it).

Of course, if you were dealing with XML documents with the same namespaces all the time, you could reference these in your XSLT, like so, which is slightly neater:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" 
    xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
    xmlns:atom="http://www.w3.org/2005/Atom">

    <xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>

    <xsl:template match="*">
        <xsl:element name="{local-name()}">
            <xsl:apply-templates select="@* | node()"/>
        </xsl:element>
    </xsl:template>

    <xsl:template match="atom:link" />

    <xsl:template match="*[@m:type='Microsoft.Crm.Sdk.Data.Services.EntityReference']">
        <xsl:element name="{local-name()}">
            <xsl:value-of select="d:Id" />
        </xsl:element>
    </xsl:template>

    <!-- template to copy attributes -->
    <xsl:template match="@*">
        <xsl:attribute name="{local-name()}">
            <xsl:value-of select="."/>
        </xsl:attribute>
    </xsl:template>
</xsl:stylesheet>