GROUP BY IN XQUERY

SELECT

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:XMLDatabaseCustomer", query)

query =

dsTemp =

dsTemp = xmlXqury.ReadXMLFile("C:XMLDatabaseCustomer", query)

drCKT = dtCKT.NewRow

drCKT(

drCKT(

dtCKT.Rows.Add(drCKT)

dsCAQuanity =

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:XMLDatabaseCustomer", 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:XMLDatabaseCustomer", 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.