How to retrive values of each xml node row wise from xml column in mssql 2014 RRS feed

  • Question

  • User-862431657 posted

    I have xml column as below.I need to retrive information as 

    ProductID CombinationID Color Size SKU

    1                     1                   red  large test

    <Product ID="9">
    <Combination ID="1">
    <Combination ID="2">

    How could I achieve this using xquery

    Monday, September 28, 2015 3:26 AM


  • User-271186128 posted

    Hi aasantosh,

    How to retrive values of each xml node row wise from xml column in mssql 2014

    As for this issue, I suggest you could refer to the following code:

            protected void btnRetrive_Click(object sender, EventArgs e)
                XmlDocument xml = new XmlDocument();
                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[5] { new DataColumn("ProductID"), new DataColumn("CombinationID"), new DataColumn("Color"), new DataColumn("Size"), new DataColumn("SKU") });
                XmlNodeList list = xml.SelectNodes("/Product");
                foreach (XmlNode xn in list)
                    string producid = xn.Attributes["ID"].Value;
                    XmlNodeList list2 = xn.SelectNodes(".//Combination");
                    foreach (XmlNode cn in list2)
                        string combinationid = cn.Attributes["ID"].Value;
                        string color = cn["color"].InnerText;
                        string size = cn["size"].InnerText;
                        string sku = cn["sku"].InnerText;
                GridView1.DataSource = dt;

    The output screenshot:

    Best regards,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 29, 2015 1:41 AM