• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

XML Sorting

Hi

I have an urgent problem that I am struggling to resolve. I think this might be possible with XSl but I have barely begun to learn that.

I have used T-SQL to generate some XML (First part of the Attached Code)

However, I've been told I need to sort them by CustomerPoLine. Which is a child node of a CommentLines / StockLines. I've set an example of the order I am trying to achieve in the second part of the Attached Code.

I hope this is a simple task for a sort XSL thing. Thanks in advance for help on this.


<SalesOrders>
  <Orders>
    <OrderHeader>
      <CustomerPoNumber>OD0001</CustomerPoNumber>
      <OrderActionType>A</OrderActionType>
      <Customer>CUST001</Customer>
      <OrderDate>2010-04-22T13:39:54</OrderDate>
      <CustomerName>*</CustomerName>
      <ShipAddress1>LINE1</ShipAddress1>
      <ShipAddress2></ShipAddress2>
      <ShipAddress3></ShipAddress3>
      <ShipAddress4>TOWN</ShipAddress4>
      <ShipAddress5>COUNTY</ShipAddress5>
      <ShipPostalCode>PCODE</ShipPostalCode>
      <AlternateReference>ROA00070</AlternateReference>
      <SalesPerson>QWA</SalesPerson>
      <RequestedShipDate>2010-05-20T00:00:00</RequestedShipDate>
    </OrderHeader>
    <OrderDetails>
      <StockLine>
        <CustomerPoLine>1</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>DL001</StockCode>
      </StockLine>
      <StockLine>
        <CustomerPoLine>3</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>ALT01</StockCode>
      </StockLine>
      <StockLine>
        <CustomerPoLine>5</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>RP003</StockCode>
      </StockLine>
      <StockLine>
        <CustomerPoLine>7</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>DL421</StockCode>
      </StockLine>
      <StockLine>
        <CustomerPoLine>8</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>DL521</StockCode>
      </StockLine>
      <StockLine>
        <CustomerPoLine>9</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>UL706</StockCode>
      </StockLine>
      <StockLine>
        <CustomerPoLine>10</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>KB724</StockCode>
      </StockLine>
      <CommentLine>
        <CustomerPoLine>2</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <Comment>Vyflex on arms</Comment>
        <AttachedLineNumber>1</AttachedLineNumber>
      </CommentLine>
      <CommentLine>
        <CustomerPoLine>4</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <Comment>Vyflex on arms</Comment>
        <AttachedLineNumber>1</AttachedLineNumber>
      </CommentLine>
      <CommentLine>
        <CustomerPoLine>6</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <Comment>whatever</Comment>
        <AttachedLineNumber>5</AttachedLineNumber>
      </CommentLine>
    </OrderDetails>
  </Orders>
</SalesOrders>
 


But I need to get it sorted like so...




<SalesOrders>
  <Orders>
    <OrderHeader>
      <CustomerPoNumber>OD0001</CustomerPoNumber>
      <CustomerName>*</CustomerName>
      <ShipAddress1>LINE1</ShipAddress1>
      <ShipAddress2></ShipAddress2>
      <ShipAddress3></ShipAddress3>
      <ShipAddress4>TOWN</ShipAddress4>
      <ShipAddress5>COUNTY</ShipAddress5>
      <ShipPostalCode>PCODE</ShipPostalCode>
      <SalesPerson>QWA</SalesPerson>
    </OrderHeader>
    <OrderDetails>
      <StockLine>
        <CustomerPoLine>1</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>DL001</StockCode>
      </StockLine>
      <CommentLine>
        <CustomerPoLine>2</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <Comment>Vyflex on arms</Comment>
        <AttachedLineNumber>1</AttachedLineNumber>
      </CommentLine>
      <StockLine>
        <CustomerPoLine>3</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>ALT01</StockCode>
      </StockLine>
      <CommentLine>
        <CustomerPoLine>4</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <Comment>Vyflex on arms</Comment>
        <AttachedLineNumber>1</AttachedLineNumber>
      </CommentLine>
      <StockLine>
        <CustomerPoLine>5</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>RP003</StockCode>
      </StockLine>
      <CommentLine>
        <CustomerPoLine>6</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <Comment>whatever</Comment>
        <AttachedLineNumber>5</AttachedLineNumber>
      </CommentLine>
      <StockLine>
        <CustomerPoLine>7</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>DL421</StockCode>
      </StockLine>
      <StockLine>
        <CustomerPoLine>8</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>DL521</StockCode>
      </StockLine>
      <StockLine>
        <CustomerPoLine>9</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>UL706</StockCode>
      </StockLine>
      <StockLine>
        <CustomerPoLine>10</CustomerPoLine>
        <LineActionType>A</LineActionType>
        <StockCode>KB724</StockCode>
      </StockLine>
    </OrderDetails>
  </Orders>
</SalesOrders>

Open in new window

0
Narusegawa
Asked:
Narusegawa
1 Solution
 
lucky_jamesCommented:
one thing can be if you can load the xml, parse it and compose the new one.
for xml operations in c#, check out:
http://www.functionx.com/vcsharp/xml/Lesson06.htm

let me know if you need any help
0
 
Geert BormansInformation ArchitectCommented:
THat is indeed a simple XSLT that you can apply
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" >
    <xsl:output method="xml" version="1.0" indent="yes"/>
    <xsl:strip-space elements="*"/>
    <xsl:template match="node()">
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"/>
        </xsl:copy>
    </xsl:template>
    <xsl:template match="OrderDetails">
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="*">
                <xsl:sort select="CustomerPoLine" data-type="number" order="ascending"/>
            </xsl:apply-templates>
        </xsl:copy>
    </xsl:template>
</xsl:stylesheet>

Open in new window

0
 
NarusegawaAuthor Commented:
Gertone, I believe that XSLT will work thank you.

I'm just trying to get .net 1.1 to sort it out for me :-) I'll accept as solution as soon as I have this working. But I think you have got it.
0
 
Anton_SivovCommented:
Hope the following code will help you.

string result = SortMyXml(xmlToSort);
private static string SortMyXml(string input)
        {
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(input);
            SortNodeChilds(doc.SelectSingleNode("//OrderHeader"));
            SortNodesChilds(doc.SelectNodes("//StockLine"));
            SortNodesChilds(doc.SelectNodes("//CommentLine"));
            
            return doc.InnerXml;
        }

        private static void SortNodesChilds(XmlNodeList nodesToSort)
        {
            if (nodesToSort == null)
                return;

            XmlNode[] nodes = (from node in nodesToSort.Cast<XmlNode>() select SortNodeChilds(node)).ToArray();
            if (nodes.Length != nodesToSort.Count)
                return;

            for (int i = 0; i < nodesToSort.Count; i ++ )
            {
                nodesToSort[i].ParentNode.ReplaceChild(nodes[i], nodesToSort[i]);
            }

            return;
        }

        private static XmlNode SortNodeChilds(XmlNode nodeToSort)
        {
            var childs = from node in nodeToSort.ChildNodes.Cast<XmlNode>()
                         orderby node.Name
                         select node;
            XmlNode[] sortedNodes = childs.ToArray();
            nodeToSort.RemoveAll();
            foreach (XmlNode node in sortedNodes)
            {
                nodeToSort.AppendChild(node);
            }

            return nodeToSort;
        }

Open in new window

0
 
Geert BormansInformation ArchitectCommented:
welcome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now