1

This question's solution makes hard-coded transformation of a sample XML tree into a flat delimited text file:

string orderXml = 
@"<?xml version='1.0' encoding='utf-8'?>
<Order id='79223510'>
<Status>new</Status>
<ShipMethod>Standard International</ShipMethod>
<ToCity>Tokyo</ToCity>
<Items>
    <Item>
    <SKU>SKU-1234567890</SKU>
    <Quantity>1</Quantity>
    <Price>99.95</Price>
    </Item>
    <Item>
    <SKU>SKU-1234567899</SKU>
    <Quantity>1</Quantity>
    <Price>199.95</Price>
    </Item>
</Items>
</Order>";

StringReader str = new StringReader(orderXml);

var xslt = new XmlTextReader(new StringReader(

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

    <xsl:output method='text' indent='no' media-type='text/plain' />
    <xsl:variable name='newline'><xsl:text>&#13;&#10;</xsl:text></xsl:variable>
    <xsl:variable name='delimiter'>|</xsl:variable>

    <!-- by default, don't copy any nodes to output -->
    <xsl:template match='node()|@*'>
        <xsl:apply-templates select='node()|@*'/>
    </xsl:template>

    <xsl:template match='/Order/Items/Item'>
    <xsl:value-of
        select='concat(
        ../../@id, $delimiter,
        ../../Status, $delimiter,
        ../../ShipMethod, $delimiter,
        ../../ToCity, $delimiter,
        SKU, $delimiter,
        Quantity, $delimiter,
        Price,
        $newline)'
        />
    </xsl:template>
    </xsl:stylesheet>"

                ));

var xDoc = new XPathDocument(str);
var xTr = new System.Xml.Xsl.XslCompiledTransform();
xTr.Load(xslt);

StringBuilder sb = new StringBuilder();
StringWriter writer = new StringWriter(sb);
xTr.Transform(xDoc, null, writer);

string[] lines = sb.ToString().Split(new string[] {"\n"}, StringSplitOptions.RemoveEmptyEntries);    

lines.ToList().ForEach(System.Console.Write);

producing output as the following:

79223510|new|Standard International|Tokyo|SKU-1234567890|1|99.95
79223510|new|Standard International|Tokyo|SKU-1234567899|1|199.95

Is there a way to produce the same output using generic XSL-transformation traversing source XML tree and concatenating parent nodes and attributes values to the child ones?

Notes:

  1. If there are any attributes of a node then that node will not have a value to concatenate.

  2. If there are several attributes of a node then their values should be concatenated using slash character.

  3. The true generic solution should work and flatten XML trees with more than two hierarchy levels.

Here is another sample document with additional parent node with two attributes:

<Order id='79223510'>
<Status>new</Status>
<ShipMethod>Standard International</ShipMethod>
<ToCity>Tokyo</ToCity>
<Marketplace id="123-45678-9089808" name="MyBooks" />
<Items>
    <Item>
    <SKU>SKU-1234567890</SKU>
    <Quantity>1</Quantity>
    <Price>99.95</Price>
    </Item>
    <Item>
    <SKU>SKU-1234567899</SKU>
    <Quantity>1</Quantity>
    <Price>199.95</Price>
    </Item>
</Items>
</Order>

And here is a desired delimited flattened text file output:

79223510|new|Standard International|Tokyo|123-45678-908980/MyBooks|SKU-1234567890|1|99.95
79223510|new|Standard International|Tokyo|123-45678-908980/MyBooks|SKU-1234567899|1|199.95

Solution by Dimitre Novatchev working well here for both original sample document and XML document with higher level nodes hierarchy.

    string orderXml = 
//      @"<?xml version='1.0' encoding='utf-8'?>
//      <Order id='79223510'>
//      <Status>new</Status>
//      <ShipMethod>Standard International</ShipMethod>
//      <ToCity>Tokyo</ToCity>
//      <Marketplace id='123-45678-9089808' name='MyBooks'/>
//      <Items>
//          <Item>
//          <SKU>SKU-1234567890</SKU>
//          <Quantity>1</Quantity>
//          <Price>99.95</Price>
//          </Item>
//          <Item>
//          <SKU>SKU-1234567899</SKU>
//          <Quantity>1</Quantity>
//          <Price>199.95</Price>
//          </Item>
//      </Items>
//      </Order>";

@"<?xml version='1.0' encoding='utf-8'?>
<Order id='79223510'>
    <Status>new</Status>
    <ShipMethod>Standard International</ShipMethod>
    <ToCity>Tokyo</ToCity>
    <Marketplace id=""123-45678-9089808"" name=""MyBooks"" />
    <Items>
        <Item>
        <X>
            <SKU>SKU-1234567890</SKU>
            <Quantity>1</Quantity>
            <Price>99.95</Price>
        </X>
        <X>
            <SKU>SKU-1234554321</SKU>
            <Quantity>1</Quantity>
            <Price>199.95</Price>
        </X>
        </Item>
        <Item>
        <Y>
            <SKU>SKU-0987654321</SKU>
            <Quantity>1</Quantity>
            <Price>299.95</Price>
        </Y>
        <Y>
            <SKU>SKU-0987667890</SKU>
            <Quantity>1</Quantity>
            <Price>399.95</Price>
        </Y>
        </Item>
    </Items>
</Order>";

StringReader str = new StringReader(orderXml);

var xslt = new XmlTextReader(new StringReader(   
    @"<xsl:stylesheet version='1.0'
        xmlns:xsl='http://www.w3.org/1999/XSL/Transform'
        xmlns:ext='http://exslt.org/common'>
    <xsl:output method='text'/>
    <xsl:strip-space elements='*'/>

        <xsl:param name='pLeafNodes' select=
        '//*[not(*[*])
            and
            (
            name() = name(following-sibling::*[1])
            or
            name() = name(preceding-sibling::*[1])
            )
            ]'/>

        <xsl:template match='/'>
        <xsl:variable name='vrtfPass1'>
            <t>
                <xsl:call-template name='StructRepro'/>
            </t>
        </xsl:variable>

        <xsl:apply-templates mode='pass2'
            select='ext:node-set($vrtfPass1)/*/*' />
        </xsl:template>

    <xsl:template match='Order' mode='pass2'>
    <xsl:apply-templates select='.//@* | .//text()' mode='pass2'/>
    <xsl:text>&#xA;</xsl:text>
    </xsl:template>

    <xsl:template match='@*|text()' mode='pass2'>
    <xsl:if test='not(position()=1) and not(self::text())'>/</xsl:if>
    <xsl:if test='not(position()=1) and self::text()'>|</xsl:if>
    <xsl:value-of select='.'/>
    </xsl:template>

        <xsl:template name='StructRepro'>
        <xsl:param name='pLeaves' select='$pLeafNodes'/>

        <xsl:for-each select='$pLeaves'>
            <xsl:apply-templates mode='build' select='/*'>
            <xsl:with-param name='pChild' select='.'/>
            <xsl:with-param name='pLeaves' select='$pLeaves'/>
            </xsl:apply-templates>
        </xsl:for-each>
        </xsl:template>

        <xsl:template mode='build' match='node()|@*'>
            <xsl:param name='pChild'/>
            <xsl:param name='pLeaves'/>

            <xsl:copy>
            <xsl:apply-templates mode='build' select='@*'/>

            <xsl:variable name='vLeafChild' select=
                '*[count(.|$pChild) = count($pChild)]'/>

            <xsl:choose>
                <xsl:when test='$vLeafChild'>
                <xsl:apply-templates mode='build'
                    select='$vLeafChild
                            |
                            node()[not(count(.|$pLeaves) = count($pLeaves))]'>
                    <xsl:with-param name='pChild' select='$pChild'/>
                    <xsl:with-param name='pLeaves' select='$pLeaves'/>
                </xsl:apply-templates>
                </xsl:when>
                <xsl:otherwise>
                <xsl:apply-templates mode='build' select=
                'node()[not(.//*[count(.|$pLeaves) = count($pLeaves)])
                        or
                        .//*[count(.|$pChild) = count($pChild)]
                        ]
                '>

                    <xsl:with-param name='pChild' select='$pChild'/>
                    <xsl:with-param name='pLeaves' select='$pLeaves'/>
                </xsl:apply-templates>
                        </xsl:otherwise>
                    </xsl:choose>
                    </xsl:copy>
                </xsl:template>
                <xsl:template match='text()'/>
            </xsl:stylesheet>"
                ));


//
// White space cannot be stripped from input documents that have already been loaded. 
// Provide the input document as an XmlReader instead. 
//+
//var xDoc = new XPathDocument(str);
XmlReaderSettings settings;
settings = new XmlReaderSettings();
settings.ConformanceLevel = ConformanceLevel.Document;
var xDoc = XmlReader.Create(str, settings);
//-

var xTr = new System.Xml.Xsl.XslCompiledTransform();
xTr.Load(xslt);

StringBuilder sb = new StringBuilder();
StringWriter writer = new StringWriter(sb);
xTr.Transform(xDoc, null, writer);

string[] lines = sb.ToString().Split(new string[] {"\n"}, StringSplitOptions.RemoveEmptyEntries);    

lines.ToList().ForEach(System.Console.Write);     

//  test output 1
//  79223510|new|Standard International|Tokyo/123-45678-9089808/MyBooks|SKU-1234567890|1|99.95
//  79223510|new|Standard International|Tokyo/123-45678-9089808/MyBooks|SKU-1234567899|1|199.95

// test output 2
//  79223510|new|Standard International|Tokyo/123-45678-9089808/MyBooks|SKU-1234567890|1|99.95
//  79223510|new|Standard International|Tokyo/123-45678-9089808/MyBooks|SKU-1234554321|1|199.95
//  79223510|new|Standard International|Tokyo/123-45678-9089808/MyBooks|SKU-0987654321|1|299.95
//  79223510|new|Standard International|Tokyo/123-45678-9089808/MyBooks|SKU-0987667890|1|399.95
Community
  • 1
  • 1
ShamilS
  • 1,410
  • 2
  • 20
  • 40
  • Can you please produce a sample of the desired output and given import (with attributes). – AxelEckenberger Jun 29 '12 at 22:59
  • @Obalix: I have added to the P.S. of my original posting the second sample XML document with additional node with two attributes as well as desired flattened delimited text file output. Thank you. – ShamilS Jun 29 '12 at 23:11

1 Answers1

0

This transformation:

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:ext="http://exslt.org/common">
     <xsl:output method="text"/>
     <xsl:strip-space elements="*"/>

     <xsl:param name="pLeafNodes" select=
     "//*[not(*[*])
        and
         (
          name() = name(following-sibling::*[1])
         or
          name() = name(preceding-sibling::*[1])
          )
         ]"/>

     <xsl:template match="/">
      <xsl:variable name="vrtfPass1">
          <t>
            <xsl:call-template name="StructRepro"/>
          </t>
      </xsl:variable>

      <xsl:apply-templates mode="pass2"
         select="ext:node-set($vrtfPass1)/*/*" />
     </xsl:template>

 <xsl:template match="Order" mode="pass2">
  <xsl:apply-templates select=".//@* | .//text()" mode="pass2"/>
  <xsl:text>&#xA;</xsl:text>
 </xsl:template>

 <xsl:template match="@*|text()" mode="pass2">
  <xsl:if test="not(position()=1) and not(self::text())">/</xsl:if>
  <xsl:if test="not(position()=1) and self::text()">|</xsl:if>
  <xsl:value-of select="."/>
 </xsl:template>


     <xsl:template name="StructRepro">
       <xsl:param name="pLeaves" select="$pLeafNodes"/>

       <xsl:for-each select="$pLeaves">
         <xsl:apply-templates mode="build" select="/*">
          <xsl:with-param name="pChild" select="."/>
          <xsl:with-param name="pLeaves" select="$pLeaves"/>
         </xsl:apply-templates>
       </xsl:for-each>
     </xsl:template>

      <xsl:template mode="build" match="node()|@*">
          <xsl:param name="pChild"/>
          <xsl:param name="pLeaves"/>

         <xsl:copy>
           <xsl:apply-templates mode="build" select="@*"/>

           <xsl:variable name="vLeafChild" select=
             "*[count(.|$pChild) = count($pChild)]"/>

           <xsl:choose>
            <xsl:when test="$vLeafChild">
             <xsl:apply-templates mode="build"
                 select="$vLeafChild
                        |
                          node()[not(count(.|$pLeaves) = count($pLeaves))]">
                 <xsl:with-param name="pChild" select="$pChild"/>
                 <xsl:with-param name="pLeaves" select="$pLeaves"/>
             </xsl:apply-templates>
            </xsl:when>
            <xsl:otherwise>
             <xsl:apply-templates mode="build" select=
             "node()[not(.//*[count(.|$pLeaves) = count($pLeaves)])
                    or
                     .//*[count(.|$pChild) = count($pChild)]
                    ]
             ">

                 <xsl:with-param name="pChild" select="$pChild"/>
                 <xsl:with-param name="pLeaves" select="$pLeaves"/>
             </xsl:apply-templates>
            </xsl:otherwise>
           </xsl:choose>
         </xsl:copy>
     </xsl:template>
     <xsl:template match="text()"/>
</xsl:stylesheet>

when applied on the provided XML document:

<Order id='79223510'>
    <Status>new</Status>
    <ShipMethod>Standard International</ShipMethod>
    <ToCity>Tokyo</ToCity>
    <Marketplace id="123-45678-9089808" name="MyBooks" />
    <Items>
        <Item>
            <SKU>SKU-1234567890</SKU>
            <Quantity>1</Quantity>
            <Price>99.95</Price>
        </Item>
        <Item>
            <SKU>SKU-1234567899</SKU>
            <Quantity>1</Quantity>
            <Price>199.95</Price>
        </Item>
    </Items>
</Order>

produces the wanted, correct result:

79223510|new|Standard International|Tokyo/123-45678-9089808/MyBooks|SKU-1234567890|1|99.95
79223510|new|Standard International|Tokyo/123-45678-9089808/MyBooks|SKU-1234567899|1|199.95

Here is a more complicated XML document, which I created based on the provided one:

<Order id='79223510'>
    <Status>new</Status>
    <ShipMethod>Standard International</ShipMethod>
    <ToCity>Tokyo</ToCity>
    <Marketplace id="123-45678-9089808" name="MyBooks" />
    <Items>
        <Item>
         <X>
            <SKU>SKU-1234567890</SKU>
            <Quantity>1</Quantity>
            <Price>99.95</Price>
         </X>
         <X>
            <SKU>SKU-1234554321</SKU>
            <Quantity>1</Quantity>
            <Price>199.95</Price>
         </X>
        </Item>
        <Item>
         <Y>
            <SKU>SKU-0987654321</SKU>
            <Quantity>1</Quantity>
            <Price>299.95</Price>
         </Y>
         <Y>
            <SKU>SKU-0987667890</SKU>
            <Quantity>1</Quantity>
            <Price>399.95</Price>
         </Y>
        </Item>
    </Items>
</Order>

When the same transformation is applied on this second document, again the wanted, correct result is produced:

79223510|new|Standard International|Tokyo/123-45678-9089808/MyBooks|SKU-1234567890|1|99.95
79223510|new|Standard International|Tokyo/123-45678-9089808/MyBooks|SKU-1234554321|1|199.95
79223510|new|Standard International|Tokyo/123-45678-9089808/MyBooks|SKU-0987654321|1|299.95
79223510|new|Standard International|Tokyo/123-45678-9089808/MyBooks|SKU-0987667890|1|399.95

Explanation:

  1. This is a two-pass transformation.

  2. The first pass transforms the source XML document into a shredded one. The solution for generic shredding from this answer is used. Here the most important thing is to specify correctly the "leaf" nodes for the shredding. These are any element node that: 1) doesn't have children elements that themselves have children element; and 2) whose name is the same as the name of its immediate preceding-sibling element or is the same as the name of its immediate following sibling.

The intermediate result is: 

<t>
   <Order id="79223510">
      <Status>new</Status>
      <ShipMethod>Standard International</ShipMethod>
      <ToCity>Tokyo</ToCity>
      <Marketplace id="123-45678-9089808" name="MyBooks"/>
      <Items>
         <Item>
            <X>
               <SKU>SKU-1234567890</SKU>
               <Quantity>1</Quantity>
               <Price>99.95</Price>
            </X>
         </Item>
      </Items>
   </Order>
   <Order id="79223510">
      <Status>new</Status>
      <ShipMethod>Standard International</ShipMethod>
      <ToCity>Tokyo</ToCity>
      <Marketplace id="123-45678-9089808" name="MyBooks"/>
      <Items>
         <Item>
            <X>
               <SKU>SKU-1234554321</SKU>
               <Quantity>1</Quantity>
               <Price>199.95</Price>
            </X>
         </Item>
      </Items>
   </Order>
   <Order id="79223510">
      <Status>new</Status>
      <ShipMethod>Standard International</ShipMethod>
      <ToCity>Tokyo</ToCity>
      <Marketplace id="123-45678-9089808" name="MyBooks"/>
      <Items>
         <Item>
            <Y>
               <SKU>SKU-0987654321</SKU>
               <Quantity>1</Quantity>
               <Price>299.95</Price>
            </Y>
         </Item>
      </Items>
   </Order>
   <Order id="79223510">
      <Status>new</Status>
      <ShipMethod>Standard International</ShipMethod>
      <ToCity>Tokyo</ToCity>
      <Marketplace id="123-45678-9089808" name="MyBooks"/>
      <Items>
         <Item>
            <Y>
               <SKU>SKU-0987667890</SKU>
               <Quantity>1</Quantity>
               <Price>399.95</Price>
            </Y>
         </Item>
      </Items>
   </Order>
</t>

.3. The second pass processes the children elements of the top element of the intermediate result -- the processing is done in mode "pass2".

This second-pass processing is rather simple -- all descendent attributes or descendent text nodes are processed in document order, and their values are output with a separator corresponding to the type of the node ('|' for text node and '/' for attribute).

Community
  • 1
  • 1
Dimitre Novatchev
  • 240,661
  • 26
  • 293
  • 431