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

Tim C · Accepted Answer · 2016-03-16 17:39:30Z

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>

it was a difficult fight for matching node with namespace with my low skills in xslt (I'm using xslt every 5 years ...). Your solution works great and all information are flattened as expected.

Collectives™ on Stack Overflow

flatten XML nested node with XSLT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related