Linq to XML join

Question

I have one XML file where values are stored in category element separated by comma:

<Company>
<name>Test</name>
<category>Power,Water,Gas</category>
</Company>

<Company>
<name>Test2</name>
<category>Water,Gas</category>
</Company>

In other XML file only one value is stored in category element:

<Bills>
<name>Test</name>
<category>Power</category>
</Bills>

<Bills>
<name>Test2</name>
<category>Water</category>
</Bills>

So now i need to join these two xml files and get all Company nodes that contains category value from Bills node.

I have this query:

Xdocument fRoot = XDocument.Load("company.xml");
Xdocument rRoot = XDocument.Load("bills.xml");

var query = from f in fRoot.Elements("Company")
                        join r in rRoot.Elements("Bills")
                        on (string)f.Element("category").Value equals (string)r.Element("category").Value 
                        orderby(string)f.Element("name").Value
                        select new{...}

With this i get nothing because Company node category values are stored separated by comma, and i don't know how to split them to do JOIN on these two files.

Thanks.

Answer 1

You may try something like this:

var query = from f in fRoot.Elements("Company")
    from r in rRoot.Elements("Bills")
    where ((string)f.Element("category").Value).Split(',').Contains((string)r.Element("category").Value )
    orderby(string)f.Element("name").Value
    select new new{...};