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

NarusegawaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Gertone (Geert Bormans)Information 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Gertone (Geert Bormans)Information ArchitectCommented:
welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.