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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
vcharlesAuthor Commented:
Hi Fernando,
Thank you.
Victor
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.