XQuery is a query language that is designed to query collection of XML data. XQUERY provides a interface to manipulate data from XML or any other data source(relational). No doubt XQUERY is the faster interface to extract data from XML, as compared to read XML in dataest and read it. Reading XML data in dataset becomes pathetic when there is a huge amount of data in the XML file.  Inspite of the above fact, XQUERY is still not so much popular among developers. I tried to search GROUP BY statement in the XQUERY, I didn't find a single clue to perform it. Following is the Stored Procedure I had converted into XQUERY, and produce a GROUP BY and ORDER BY clauses with the loops & datset.   SQL STORED PROCEDURE:  

SELECT
Name,sum(Qty) as TotalQty FROM Customer WHERE CustOrderID = @CustOrderID

GROUP BY Name

ORDER BY Name

Here I would get the Various Names of customer with their SUm of product Quantity.

Now, To convert the same in XQUERY I used the following Code:

VB.NET:

Dim dsCAQuanity As New DataSet()

Dim dsTemp As New DataSet

Dim dsCKT As New DataSet

Dim dtCKT As New DataTable

Dim drCKT As DataRow

Dim dcCkt(0) As DataColumn

dcCkt(0) = New DataColumn("Name", GetType(String))

dtCKT.Columns.Add(dcCkt(0))

dtCKT.PrimaryKey = dcCkt

dtCKT.Columns.Add("TotalQty", GetType(String))

query = "(document(""Doc"")//Table)[CustOrderID=""" & CustOrderID & """ ]"

dsCKT = xmlXqury.ReadXMLFile("C:\XMLDatabase\Customer\", query)
For Each rowCkt As DataRow In dsCKT.Tables(0).Rows
query =
"LET $doc := (document(""Doc"")//Table)[Name='" & rowCkt("Name") & "' and CustOrderID=""" & CustOrderID & """ ] return <qty> { sum($doc/Qty)} </qty>"
dsTemp =
New DataSet
dsTemp = xmlXqury.ReadXMLFile("C:\XMLDatabase\Customer\", query)
If dsTemp.Tables.Count > 0 Then If dsTemp.Tables(0).Rows.Count > 0 And dtCKT.Rows.IndexOf(dtCKT.Rows.Find(rowCkt("Name").ToString)) < 0 Then
drCKT = dtCKT.NewRow

drCKT(
"Name") = rowCkt("Name")
drCKT(
"TotalQty") = dsTemp.Tables(0).Rows(0)(0)
dtCKT.Rows.Add(drCKT)
End If End If Next
dsCAQuanity =
New DataSet()
dsCAQuanity.Tables.Add(dtCKT)

C#:

    DataSet dsCAQuanity = new DataSet();

    DataSet dsTemp = new DataSet();

    DataSet dsCKT = new DataSet();

    DataTable dtCKT = new DataTable();

    DataRow drCKT = default(DataRow);

    DataColumn[] dcCkt = new DataColumn[1];

     dcCkt(0) = new DataColumn("Name", typeof(string));

        dtCKT.Columns.Add(dcCkt(0));

        dtCKT.PrimaryKey = dcCkt;

        dtCKT.Columns.Add("TotalQty", typeof(string));

        query = "(document(\"Doc\")//Table)[CustOrderID=\"" + CustOrderID + "\" ]";

        dsCKT = xmlXqury.ReadXMLFile("C:\\XMLDatabase\\Customer\\", query);

        foreach (DataRow rowCkt in dsCKT.Tables(0).Rows) {

                query = "LET $doc := (document(\"Doc\")//Table)[Name='" + rowCkt("Name") + "' and CustOrderID=\"" + CustOrderID + "\" ] return <qty> { sum($doc/Qty)} </qty>";

               dsTemp = new DataSet();

               dsTemp = xmlXqury.ReadXMLFile("C:\\XMLDatabase\\Customer\\", query);

                if (dsTemp.Tables.Count > 0) {

                        if (dsTemp.Tables(0).Rows.Count > 0 & dtCKT.Rows.IndexOf(dtCKT.Rows.Find(rowCkt("Name").ToString)) < 0) {

                                drCKT = dtCKT.NewRow;

                                drCKT("Name") = rowCkt("Name");

                                drCKT("TotalQty") = dsTemp.Tables(0).Rows(0)(0);

                              

                dtCKT.Rows.Add(drCKT);

                          }

                    }

    }

        dsCAQuanity = new DataSet();

        dsCAQuanity.Tables.Add(dtCKT);

The data set dsCAQuanity will return the same record as given by the above stored procedure, means it will reterive the record with GROUP BY and ORDER BY clause.

Hope it will sort out your illusion for XQUERY group BY & order by statement. XQUERY provides Order by clause, whcih can also be used with different aspects.

Happy Coding.