How to filter rows in XmlDocument, xmlDocument.LoadXml and xmlDocument.SelectNodes (similar to a where clause)

55 Views Asked by At

I have an SQL server table with an XML column (xmlColumn1) that can contain xml data such as below.

<!--Row 1--column (xmlColumn1)

<parameters>
    <Category>Business</Category>
    <Region>CCF25F36-B2A7-4790-82A4-7EC5E016C197</Region>
    <CountryId>EC7333E0-7BEE-4913-9973-2FCABE3DA6F0</CountryId>
</parameters>

<!--Row 2--column (xmlColumn1)

<parameters>
    <Category>Politics</Category>
    <Region>CCF25F36-B2A7-4790-82A4-7EC5E016C197</Region>
    <CountryId>EC7333E0-7BEE-4913-9973-2FCABE3DA6F0</CountryId>
</parameters>

<!--Row 3--column (xmlColumn1)

<parameters>
    <Category>Business</Category>
    <Region>CCF25F36-B2A7-4790-82A4-7EC5E016C197</Region>
    <CountryId>EC7333E0-7BEE-4913-9973-2FCABE3DA6F0</CountryId>
</parameters>

<!--Row 4--column (xmlColumn1)

<parameters>
    <Category>Sports</Category>
    <Region>CCF25F36-B2A7-4790-82A4-7EC5E016C197</Region>
    <CountryId>EC7333E0-7BEE-4913-9973-2FCABE3DA6F0</CountryId>
</parameters>

I then have an SQL select query similar to the one below:

 SELECT Id, xmlColumn1
  FROM MyTable

The rows returned by the query above are loaded into a list as below.

  List<myData> Data = MyDataSource.GetMyData();

I now process this data and add it to another list as below.

                if (Data.Count.Count > 0)
                {
                    (Data.ForEach(x =>
                    {
                        var xmlDocument = new XmlDocument();
                        xmlDocument.LoadXml(x.id);
                        XmlNodeList paramsList = xmlDocument.SelectNodes("//Region");

                        foreach (XmlNode node in paramsList)
                        {
                            MyOtherList.Add(new OtherList { Id = x.Id, Region = new Guid(node.InnerText) });
                        }
                    });

How can i add a condition to the above code such that ONLY rows where the XML column parameter "Category" is "Business" are added to MyOtherList.

I don't want to add the rows to MyOtherList at MyOtherList.Add(new OtherList { Id = x.Id, Region = new Guid(node.InnerText) }); if the parameter category is NOT Buiness.

0

There are 0 best solutions below