LINQ related query

Discussion in 'ASP .Net' started by Jackson420, Aug 20, 2010.

  1. Jackson420

    Jackson420 Guest

    Hi

    I have a question regarding joining xml files on a key which is present in
    all file, following is my code

    docFTR is of type XDocument
    xdList is List(Of XDocument)
    Dim doc = From x In docFTR.Descendants("Data") From i In xdList.GetRange(0,
    1) Join d In xdList(0).Descendants("Data") On x.Descendants("Guid").Value
    Equals d.Descendants("Guid").Value

    i need to programmatically replace xdList(0) with the current XDcoument
    which comes from "From i in xdList.getRange(0,2)"

    GetRange(0, could be 2 to 10)

    Any help is welcome

    TIA
    Jacko
    Jackson420, Aug 20, 2010
    #1
    1. Advertising

  2. Jackson420 wrote:
    > Hi
    >
    > I have a question regarding joining xml files on a key which is present in
    > all file, following is my code
    >
    > docFTR is of type XDocument
    > xdList is List(Of XDocument)
    > Dim doc = From x In docFTR.Descendants("Data") From i In xdList.GetRange(0,
    > 1) Join d In xdList(0).Descendants("Data") On x.Descendants("Guid").Value
    > Equals d.Descendants("Guid").Value
    >
    > i need to programmatically replace xdList(0) with the current XDcoument
    > which comes from "From i in xdList.getRange(0,2)"
    >
    > GetRange(0, could be 2 to 10)


    Join d in i.Descendants("Data")
    should do but that sounds too obvious maybe.

    If that does not help then consider to post some sample data, at least I
    am not good at writing queries without seeing some sample data.

    --

    Martin Honnen --- MVP Data Platform Development
    http://msmvps.com/blogs/martin_honnen/
    Martin Honnen, Aug 20, 2010
    #2
    1. Advertising

  3. Jackson420

    Jackson420 Guest

    Here is a sample data of 1 file, you may make 3 more of the same and try to
    join them on Guid and create an xml file

    <?xml version="1.0" encoding="utf-8"?>
    <ColumnData>
    <Data>
    <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    <Data>Sales org#</Data>
    </Data>
    <Data>
    <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    <Data>G146</Data>
    </Data>
    </ColumnData


    Note: the XDocument must not be hard coded in the command

    TIA
    Jacko

    "Martin Honnen" <> wrote in message
    news:...
    > Jackson420 wrote:
    >> Hi
    >>
    >> I have a question regarding joining xml files on a key which is present
    >> in all file, following is my code
    >>
    >> docFTR is of type XDocument
    >> xdList is List(Of XDocument)
    >> Dim doc = From x In docFTR.Descendants("Data") From i In
    >> xdList.GetRange(0, 1) Join d In xdList(0).Descendants("Data") On
    >> x.Descendants("Guid").Value Equals d.Descendants("Guid").Value
    >>
    >> i need to programmatically replace xdList(0) with the current XDcoument
    >> which comes from "From i in xdList.getRange(0,2)"
    >>
    >> GetRange(0, could be 2 to 10)

    >
    > Join d in i.Descendants("Data")
    > should do but that sounds too obvious maybe.
    >
    > If that does not help then consider to post some sample data, at least I
    > am not good at writing queries without seeing some sample data.
    >
    > --
    >
    > Martin Honnen --- MVP Data Platform Development
    > http://msmvps.com/blogs/martin_honnen/
    Jackson420, Aug 20, 2010
    #3
  4. Jackson420 wrote:
    > Here is a sample data of 1 file, you may make 3 more of the same and try to
    > join them on Guid and create an xml file
    >
    > <?xml version="1.0" encoding="utf-8"?>
    > <ColumnData>
    > <Data>
    > <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    > <Data>Sales org#</Data>
    > </Data>
    > <Data>
    > <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    > <Data>G146</Data>
    > </Data>
    > </ColumnData


    Here is some sample code processing a List(Of XDocument) of the above
    structure and creating a merged document:

    Dim doc1 As XDocument = <?xml version="1.0" encoding="utf-8"?>
    <ColumnData>
    <Data>

    <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    <Data>Sales org #1</Data>
    </Data>
    <Data>

    <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    <Data>G146</Data>
    </Data>
    </ColumnData>

    Dim doc2 As XDocument = <?xml version="1.0" encoding="utf-8"?>
    <ColumnData>
    <Data>

    <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    <Data>Sales org #2</Data>
    </Data>
    <Data>

    <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    <Data>G147</Data>
    </Data>
    </ColumnData>

    Dim doc3 As XDocument = <?xml version="1.0" encoding="utf-8"?>
    <ColumnData>
    <Data>

    <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    <Data>Sales org #3</Data>
    </Data>
    </ColumnData>

    Dim doc4 As XDocument = <?xml version="1.0" encoding="utf-8"?>
    <ColumnData>
    <Data>

    <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    <Data>G148</Data>
    </Data>
    </ColumnData>

    Dim docs As New List(Of XDocument)()
    docs.Add(doc1)
    docs.Add(doc2)
    docs.Add(doc3)
    docs.Add(doc4)

    Dim mergedDoc As XDocument = New XDocument( _
    New XElement(doc1.Root.Name, _
    From data In docs.<ColumnData>.<Data> _
    Group data By guid = data.<Guid>.Value Into G =
    Group _
    Select New XElement("Data", G(0).<Guid>, G.<Data>)))

    mergedDoc.Save(Console.Out)

    Output is as follows:

    <ColumnData>
    <Data>
    <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    <Data>Sales org #1</Data>
    <Data>Sales org #2</Data>
    <Data>Sales org #3</Data>
    </Data>
    <Data>
    <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    <Data>G146</Data>
    <Data>G147</Data>
    <Data>G148</Data>
    </Data>
    </ColumnData>

    Is that what you want? Or how should the merged document look exactly?


    --

    Martin Honnen --- MVP Data Platform Development
    http://msmvps.com/blogs/martin_honnen/
    Martin Honnen, Aug 20, 2010
    #4
  5. Jackson420

    Jackson420 Guest

    Excellent job - on the dot

    MANY THANKS
    Jacko


    "Martin Honnen" <> wrote in message
    news:...
    > Jackson420 wrote:
    >> Here is a sample data of 1 file, you may make 3 more of the same and try
    >> to join them on Guid and create an xml file
    >>
    >> <?xml version="1.0" encoding="utf-8"?>
    >> <ColumnData>
    >> <Data>
    >> <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    >> <Data>Sales org#</Data>
    >> </Data>
    >> <Data>
    >> <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    >> <Data>G146</Data>
    >> </Data>
    >> </ColumnData

    >
    > Here is some sample code processing a List(Of XDocument) of the above
    > structure and creating a merged document:
    >
    > Dim doc1 As XDocument = <?xml version="1.0" encoding="utf-8"?>
    > <ColumnData>
    > <Data>
    >
    > <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    > <Data>Sales org #1</Data>
    > </Data>
    > <Data>
    >
    > <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    > <Data>G146</Data>
    > </Data>
    > </ColumnData>
    >
    > Dim doc2 As XDocument = <?xml version="1.0" encoding="utf-8"?>
    > <ColumnData>
    > <Data>
    >
    > <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    > <Data>Sales org #2</Data>
    > </Data>
    > <Data>
    >
    > <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    > <Data>G147</Data>
    > </Data>
    > </ColumnData>
    >
    > Dim doc3 As XDocument = <?xml version="1.0" encoding="utf-8"?>
    > <ColumnData>
    > <Data>
    >
    > <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    > <Data>Sales org #3</Data>
    > </Data>
    > </ColumnData>
    >
    > Dim doc4 As XDocument = <?xml version="1.0" encoding="utf-8"?>
    > <ColumnData>
    > <Data>
    >
    > <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    > <Data>G148</Data>
    > </Data>
    > </ColumnData>
    >
    > Dim docs As New List(Of XDocument)()
    > docs.Add(doc1)
    > docs.Add(doc2)
    > docs.Add(doc3)
    > docs.Add(doc4)
    >
    > Dim mergedDoc As XDocument = New XDocument( _
    > New XElement(doc1.Root.Name, _
    > From data In docs.<ColumnData>.<Data> _
    > Group data By guid = data.<Guid>.Value Into G = Group
    > _
    > Select New XElement("Data", G(0).<Guid>, G.<Data>)))
    >
    > mergedDoc.Save(Console.Out)
    >
    > Output is as follows:
    >
    > <ColumnData>
    > <Data>
    > <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    > <Data>Sales org #1</Data>
    > <Data>Sales org #2</Data>
    > <Data>Sales org #3</Data>
    > </Data>
    > <Data>
    > <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    > <Data>G146</Data>
    > <Data>G147</Data>
    > <Data>G148</Data>
    > </Data>
    > </ColumnData>
    >
    > Is that what you want? Or how should the merged document look exactly?
    >
    >
    > --
    >
    > Martin Honnen --- MVP Data Platform Development
    > http://msmvps.com/blogs/martin_honnen/
    Jackson420, Aug 21, 2010
    #5
  6. Jackson420

    Jackson420 Guest

    Hi Martin

    The final requirement is a bit different, i completed it through 2-stage
    process, lets see if you can do this

    <ColumnData>
    <Data>
    <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    <Data>Sales org #1|Sales org #2|Sales org #3</Data>
    </Data>
    <Data>
    <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    <Data>G146|G147|G148
    </Data>
    </ColumnData>



    "Martin Honnen" <> wrote in message
    news:...
    > Jackson420 wrote:
    >> Here is a sample data of 1 file, you may make 3 more of the same and try
    >> to join them on Guid and create an xml file
    >>
    >> <?xml version="1.0" encoding="utf-8"?>
    >> <ColumnData>
    >> <Data>
    >> <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    >> <Data>Sales org#</Data>
    >> </Data>
    >> <Data>
    >> <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    >> <Data>G146</Data>
    >> </Data>
    >> </ColumnData

    >
    > Here is some sample code processing a List(Of XDocument) of the above
    > structure and creating a merged document:
    >
    > Dim doc1 As XDocument = <?xml version="1.0" encoding="utf-8"?>
    > <ColumnData>
    > <Data>
    >
    > <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    > <Data>Sales org #1</Data>
    > </Data>
    > <Data>
    >
    > <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    > <Data>G146</Data>
    > </Data>
    > </ColumnData>
    >
    > Dim doc2 As XDocument = <?xml version="1.0" encoding="utf-8"?>
    > <ColumnData>
    > <Data>
    >
    > <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    > <Data>Sales org #2</Data>
    > </Data>
    > <Data>
    >
    > <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    > <Data>G147</Data>
    > </Data>
    > </ColumnData>
    >
    > Dim doc3 As XDocument = <?xml version="1.0" encoding="utf-8"?>
    > <ColumnData>
    > <Data>
    >
    > <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    > <Data>Sales org #3</Data>
    > </Data>
    > </ColumnData>
    >
    > Dim doc4 As XDocument = <?xml version="1.0" encoding="utf-8"?>
    > <ColumnData>
    > <Data>
    >
    > <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    > <Data>G148</Data>
    > </Data>
    > </ColumnData>
    >
    > Dim docs As New List(Of XDocument)()
    > docs.Add(doc1)
    > docs.Add(doc2)
    > docs.Add(doc3)
    > docs.Add(doc4)
    >
    > Dim mergedDoc As XDocument = New XDocument( _
    > New XElement(doc1.Root.Name, _
    > From data In docs.<ColumnData>.<Data> _
    > Group data By guid = data.<Guid>.Value Into G = Group
    > _
    > Select New XElement("Data", G(0).<Guid>, G.<Data>)))
    >
    > mergedDoc.Save(Console.Out)
    >
    > Output is as follows:
    >
    > <ColumnData>
    > <Data>
    > <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    > <Data>Sales org #1</Data>
    > <Data>Sales org #2</Data>
    > <Data>Sales org #3</Data>
    > </Data>
    > <Data>
    > <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    > <Data>G146</Data>
    > <Data>G147</Data>
    > <Data>G148</Data>
    > </Data>
    > </ColumnData>
    >
    > Is that what you want? Or how should the merged document look exactly?
    >
    >
    > --
    >
    > Martin Honnen --- MVP Data Platform Development
    > http://msmvps.com/blogs/martin_honnen/
    Jackson420, Aug 24, 2010
    #6
  7. Jackson420 wrote:

    > The final requirement is a bit different, i completed it through 2-stage
    > process, lets see if you can do this
    >
    > <ColumnData>
    > <Data>
    > <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    > <Data>Sales org #1|Sales org #2|Sales org #3</Data>
    > </Data>
    > <Data>
    > <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    > <Data>G146|G147|G148
    > </Data>
    > </ColumnData>


    Yes, that is possible, change the "merge" query to

    Dim mergedDoc As XDocument = New XDocument( _
    New XElement(doc1.Root.Name, _
    From data In docs.<ColumnData>.<Data> _
    Group data By guid = data.<Guid>.Value Into G =
    Group _
    Select New XElement("Data", G(0).<Guid>, New
    XElement("Data", String.Join("|", G.<Data>.Select(Function(d)
    d.Value).ToArray())))))

    (only all on one line).


    --

    Martin Honnen --- MVP Data Platform Development
    http://msmvps.com/blogs/martin_honnen/
    Martin Honnen, Aug 24, 2010
    #7
  8. Jackson420

    Jackson420 Guest

    Thanks

    "Martin Honnen" <> wrote in message
    news:%23$...
    > Jackson420 wrote:
    >
    >> The final requirement is a bit different, i completed it through 2-stage
    >> process, lets see if you can do this
    >>
    >> <ColumnData>
    >> <Data>
    >> <Guid>d2152461-5005-4f22-af65-66f1fe734e81</Guid>
    >> <Data>Sales org #1|Sales org #2|Sales org #3</Data>
    >> </Data>
    >> <Data>
    >> <Guid>4bea02ee-f986-423e-a093-71d78f61ea9b</Guid>
    >> <Data>G146|G147|G148
    >> </Data>
    >> </ColumnData>

    >
    > Yes, that is possible, change the "merge" query to
    >
    > Dim mergedDoc As XDocument = New XDocument( _
    > New XElement(doc1.Root.Name, _
    > From data In docs.<ColumnData>.<Data> _
    > Group data By guid = data.<Guid>.Value Into G = Group
    > _
    > Select New XElement("Data", G(0).<Guid>, New
    > XElement("Data", String.Join("|", G.<Data>.Select(Function(d)
    > d.Value).ToArray())))))
    >
    > (only all on one line).
    >
    >
    > --
    >
    > Martin Honnen --- MVP Data Platform Development
    > http://msmvps.com/blogs/martin_honnen/
    Jackson420, Aug 24, 2010
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ChrisN
    Replies:
    1
    Views:
    526
    Alexey Smirnov
    Aug 7, 2007
  2. shapper

    LINQ Query

    shapper, Oct 17, 2007, in forum: ASP .Net
    Replies:
    2
    Views:
    367
    sloan
    Oct 18, 2007
  3. Alex Sauceda

    SQL Query to Linq (Group By and Sum)

    Alex Sauceda, Feb 4, 2008, in forum: ASP .Net
    Replies:
    6
    Views:
    40,999
    Alex Sauceda
    Feb 4, 2008
  4. Raynald
    Replies:
    0
    Views:
    284
    Raynald
    Aug 12, 2008
  5. George

    Linq or not Linq

    George, Nov 4, 2008, in forum: ASP .Net
    Replies:
    4
    Views:
    344
    Mike Gleason jr Couturier
    Nov 5, 2008
Loading...

Share This Page