Help with partial string search

Hi,

How do you modify the code below to search by partial string, for example would like to filter records based on for first 3 digits in SN rather than the entire string.

Dim nodesf1 = (From node In xDocF1.Descendants("Table1")
               Group By Key = New GroupKey With {.ID = node.Element("ID").Value, .CTRY = node.Element("CTRY").Value, .SN = node.Element("SN").Value} Into grouping = Group
               Select grouping).ToList()

Thanks,

Victor

Open in new window

vcharlesAsked:
Who is Participating?
 
Fernando SotoRetiredCommented:
Hi Victor;

When you modified the first two queries in your code you missed the third query. It should be as follows.
Dim matchf1f2 = (From group1 In nodesf1
                 Join group2 In nodesf2 On New GroupKey With {.ID = group1(0).Element("ID").Value, .CTRY = group1(0).Element("CTRY").Value, .SN = group1(0).Element("SN").Value.SubString(0, 3)} Equals New GroupKey With {.ID = group2(0).Element("ID").Value, .CTRY = group2(0).Element("CTRY").Value, .SN = group2(0).Element("SN").Value}
                 Where group2 IsNot Nothing
                 Select group1, group2).ToList()

Open in new window

0
 
Walter RitzelSenior Software EngineerCommented:
I think you can use .Contains(), .StartsWith or .EndsWith()
Dim nodesf1 = (From node In xDocF1.Descendants("Table1")
               Group By Key = New GroupKey With {.ID = node.Element("ID").Value, .CTRY = node.Element("CTRY").Value, .SN.Contains(node.Element("SN").Value)} Into grouping = Group
               Select grouping).ToList()

Open in new window

0
 
Fernando SotoRetiredCommented:
The code has been modified to use the first 3 digits of the value in SN.

Dim nodesf1 = (From node In xDocF1.Descendants("Table1")
               Group By Key = New GroupKey With { .ID = node.Element("ID").Value, .CTRY = node.Element("CTRY").Value, .SN = node.Element("SN").Value.SubString(0, 3)} Into grouping = Group 
               Select grouping).ToList()

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
vcharlesAuthor Commented:
Hi Fernando,
Thank you.
Victor
0
 
vcharlesAuthor Commented:
Hi,

I'm getting error message: "Index and length must refer to a location within the string. Parameter name: length".

Dim xDocF1 As XDocument = XDocument.Load("C:\Working Directory\VictorFile1.XML")
Dim xDocF2 As XDocument = XDocument.Load("C:\Working Directory\VictorFile2.XML")

Dim nodesf1 = (From node In xDocF1.Descendants("Table1")
               Group By Key = New GroupKey With {.ID = node.Element("ID").Value, .CTRY = node.Element("CTRY").Value, .SN = node.Element("SN").Value.Substring(0,3)} Into grouping = Group
               Select grouping).ToList()

Dim nodesf2 = (From node In xDocF2.Descendants("Table1")
               Group By Key = New GroupKey With {.ID = node.Element("ID").Value, .CTRY = node.Element("CTRY").Value, .SN = node.Element("SN").Value.Substring(0,3)} } Into grouping = Group
               Select grouping).ToList()

Dim matchf1f2 = (From group1 In nodesf1
                 Join group2 In nodesf2 On New GroupKey With {.ID = group1(0).Element("ID").Value, .CTRY = group1(0).Element("CTRY").Value, .SN = group1(0).Element("SN").Value} Equals New GroupKey With {.ID = group2(0).Element("ID").Value, .CTRY = group2(0).Element("CTRY").Value, .SN = group2(0).Element("SN").Value}
                 Where group2 IsNot Nothing
                 Select group1, group2).ToList()

For Each grouping In matchf1f2

    Dim g1 As XElement = CType(grouping.group1(0), XElement)
    Dim g2 As XElement = CType(grouping.group2(0), XElement)

    For Each node As XElement In g2.Descendants()

        If node.Name.ToString().Equals("ID") Or node.Name.ToString().Equals("CTRY") Or node.Name.ToString().Equals("SN") Then
            Continue For
        End If

        Dim g1Element As XElement = g1.Element(node.Name.ToString())
        If g1Element IsNot Nothing Then
            g1Element.Value = node.Value
        Else
            g1.Add(node)
        End If
    Next
Next

Open in new window


Thank you for both comments.



Victor
0
 
Walter RitzelSenior Software EngineerCommented:
I think the problem is that you are using the substring on the value to be searched (and I dont at this point if the value itself have 3 positions) instead of using the .Contains() on the field, as I have suggested.
0
 
Fernando SotoRetiredCommented:
Are any of the values in SN less then three characters long?
On what line of the code does it throw the exception?
0
 
Walter RitzelSenior Software EngineerCommented:
@Fernando, the problem with the substring approach is this: the field SN contain the full value, and the parameter being given will come just as a part of the full value. So, why are you going to get a substring of the parameter to make the comparison? For these cases, we should use the .Contains() or .StartsWith or .EndsWith on the field being compared, not on the comparison value.
0
 
vcharlesAuthor Commented:
Hi,

The values of SN may be greater then three charaters from either file1 or file2, but I'm only interested  on the first three characters.

Thanks,

Victor
0
 
Fernando SotoRetiredCommented:
Hi Victor;

Can you post samples of the two XML files please. Thanks.
0
 
vcharlesAuthor Commented:
Hi,

Below is an example of the xml files, similar from an old post.

<Root>
<Table1>
<ID>1</ID>
<CTRY>BEL</CTRY>
<SN>101XYZ</SN>
<ItemB>ItemB</ItemB>
<ItemX>UtemX</ItemX>
<ItemY>ItemY</ItemY>
<ItemZ>ItemZ</ItemZ>
</Table1>
<Table1>
<ID>2</ID>
<CTRY>BEL</CTRY>
<SN>102</102>
<ItemB></ItemB>
<ItemX>D</ItemX>
<ItemY></ItemY>
<ItemZ></ItemZ>
</Table1>
<Table1>
<ID>3</ID>
<CTRY>USA</CTRY>
<SN>103</SN>
<ItemB></ItemB>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ></ItemZ>
</Table1>
</Root>

and file2 contains:

<Root>
<TableX>
<ID>1</ID>
<CTRY>BEL</CTRY>
<SN>101</SN>
<ItemB>NewA</ItemB>
<ItemX>NewX</ItemX>
<ItemY>NewY</ItemY>
<ItemZ>NewZ</ItemZ>
</Table1>
<Table1>
<ID>2</ID>
<CTRY>USA</CTRY>
<SN>104</102>
<ItemB></ItemB>
<ItemX>D</ItemX>
<ItemY></ItemY>
<ItemZ></ItemZ>
</Table1>
<Table1>
<ID>3</ID>
<CTRY>USA</CTRY>
<SN>106</SN>
<ItemB></ItemB>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ></ItemZ>
</Table1>
<Table1>
<ID>4</ID>
<CTRY>ITA</CTRY>
<SN>106</SN>
<ItemB></ItemB>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ></ItemZ>
</Table1>
<Table1>
<ID>5</ID>
<CTRY>GBR</CTRY>
<SN>109</SN>
<ItemB></ItemB>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ></ItemZ>
</Table1>
</Root>



New File 1 should contain the following data because SN:101 in file2 matches the first three characters in SN:101XYZ in file1 and they have the same CTRY.

<Root>
<Table1>
<ID>1</ID>
<CTRY>BEL</CTRY>
<SN>101XYZ</SN>
<ItemB>NewA</ItemB>
<ItemX>NewX</ItemX>
<ItemY>NewY</ItemY>
<ItemZ>NewZ</ItemZ>
</Table1>
<Table1>
<ID>2</ID>
<CTRY>BEL</CTRY>
<SN>102</102>
<ItemB></ItemB>
<ItemX>D</ItemX>
<ItemY></ItemY>
<ItemZ></ItemZ>
</Table1>
<Table1>
<ID>3</ID>
<CTRY>USA</CTRY>
<SN>103</SN>
<ItemB></ItemB>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ></ItemZ>
</Table1>
</Root>


Thanks,

Victor
0
 
Fernando SotoRetiredCommented:
Hi Victor;

In your post of this query you added an extra } just before the Into keyword in the Group By line.
Dim nodesf2 = (From node In xDocF2.Descendants("Table1")
               Group By Key = New GroupKey With {.ID = node.Element("ID").Value, .CTRY = node.Element("CTRY").Value, .SN = node.Element("SN").Value.Substring(0,3)} } Into grouping = Group
               Select grouping).ToList()

Open in new window

This below code is correct.
Dim nodesf2 = (From node In xDocF2.Descendants("Table1")
               Group By Key = New GroupKey With {.ID = node.Element("ID").Value, .CTRY = node.Element("CTRY").Value, .SN = node.Element("SN").Value.Substring(0,3)} Into grouping = Group
               Select grouping).ToList()

Open in new window

0
 
Fernando SotoRetiredCommented:
@Walter, SN is a XML node in a XML document. Victor wants to use the first 3 character on the inner text of that node. Not knowing what string value is in the inner text using the Contains or StartsWith methods will not help here.
0
 
vcharlesAuthor Commented:
Hi,

I made the corrections but still the same error. The error occurs at the first instance with substring. Contains also does not work because  Element  property does not exist.

Thanks,

Victor
0
 
Fernando SotoRetiredCommented:
Hi Victor;

The code I posted does work with the sample XML files you posted. If you are getting errors one of two things is happening, you copied the code incorrectly into your program or the XML files are either missing some nodes or are not meeting the requirements you have stated.

Please look over the code and make sure it is the same as I posted and modified above.

If still not working please attach XML files to a post so I can see what is wrong. Also copy and paste the code here so I can make sure it is correct.
0
 
vcharlesAuthor Commented:
Hi Fernando,  
I will check both xml files and code again.
Thanks,
Victor
0
 
vcharlesAuthor Commented:
Thank you.
0
 
Fernando SotoRetiredCommented:
Not a problem Victor, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.