locked
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">
    <color>red</color>
    <size>large</size>
    <sku>Pr009</sku>
    <price>1010</price>
    <listprice>1020</listprice>
    <quantity>100</quantity>
    <image/>
    </Combination>
    <Combination ID="2">
    <color>green</color>
    <size>large</size>
    <sku>Pr009l</sku>
    <price>1015</price>
    <listprice>1020</listprice>
    <quantity>1001</quantity>
    <image/>
    </Combination>
    </Product>

    How could I achieve this using xquery

    Monday, September 28, 2015 3:26 AM

Answers

  • 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();
                xml.LoadXml(TextBox1.Text);
    
                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;
    
                        dt.Rows.Add(producid,combinationid,color,size,sku);
                    }
                }
    
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }

    The output screenshot:

    Best regards,
    Dillion

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