Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Append on VB.net

Posted on 2011-02-23
12
Medium Priority
?
253 Views
Last Modified: 2012-08-13
Hi Experts

I inputting values in a xml but I am only getting one row. Can I have some assistance to extract all row

Mu current output look like this

<?xml version="1.0"?>
<transferupload>
            <transfer>
                  <Currency>USD</Currency>
                  <Legal Entity>IBL ICM COLLATERAL</Legal Entity>
                  <Accountid>1038</Accountid>
                  <Movement>-118698</Movement>
                  <Value Date>2/1/2011 12:00:00 AM</Value Date>
                  <Narration>Collateral call</Narration>
                  <Counterparty>INBOUND_SWIFT</Counterparty>
            </transfer>
</transferupload>

But I need it to look like this depending on the rows that I am extracting

<?xml version="1.0"?>
<transferupload>
            <transfer>
                  <Currency>USD</Currency>
                  <Legal Entity>IBL ICM COLLATERAL</Legal Entity>
                  <Accountid>1038</Accountid>
                  <Movement>-118698</Movement>
                  <Value Date>2/1/2011 12:00:00 AM</Value Date>
                  <Narration>Collateral call</Narration>
                  <Counterparty>INBOUND_SWIFT</Counterparty>
            </transfer>
            <transfer>
                  <Currency>USD</Currency>
                  <Legal Entity>IBBL ICM COLLATERAL</Legal Entity>
                  <Accountid>10381</Accountid>
                  <Movement>-1186981</Movement>
                  <Value Date>2/1/2011 12:00:00 AM</Value Date>
                  <Narration>Collateral call</Narration>
                  <Counterparty>INBOUND_SWIFT_New</Counterparty>
            </transfer>

</transferupload>
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        '
        ' Add your code here
        '
        ' Creates an XML file is not exist.
        Dim writer As New XmlTextWriter("C:\Users\anthonio.booysen\xmltest.xml", Nothing)

        ' Starts a new document.


        writer.WriteStartDocument()

        ' Add elements to the file.
        writer.WriteStartElement("transferupload")
        writer.WriteStartElement("transfer")
        writer.WriteStartElement("Currency", "")
        writer.WriteString(Row.Currency.ToString)
        writer.WriteEndElement()
        writer.WriteStartElement("Legal Entity", "")
        writer.WriteString(Row.LegalEntity.ToString)
        writer.WriteEndElement()
        writer.WriteStartElement("Accountid", "")
        writer.WriteString(Row.Accountid.ToString)
        writer.WriteEndElement()
        writer.WriteStartElement("Movement", "")
        writer.WriteString(Row.Movement.ToString)
        writer.WriteEndElement()
        writer.WriteStartElement("Value Date", "")
        writer.WriteString(Row.Valuedate.ToString)
        writer.WriteEndElement()
        writer.WriteStartElement("Narration", "")
        writer.WriteString(Row.Narration.ToString)
        writer.WriteEndElement()
        writer.WriteStartElement("Counterparty", "")
        writer.WriteString(Row.Counterparty.ToString)
        writer.WriteEndElement()



        ' Ends the document 
        writer.WriteEndDocument()
        writer.Close()


    End Sub

Open in new window

0
Comment
Question by:abooysen2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 17

Expert Comment

by:nepaluz
ID: 34959384
where (how) are you getting the data?

You need to iteratethe datasource to include the second item.
0
 

Author Comment

by:abooysen2
ID: 34959415
from an Excel source Which is stored in "Row"
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 34959429
So how are you connecting to the excel file (or is it a plain csv file that you read using VB.net)?

You'll need to show some code of how you are reading the file.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:abooysen2
ID: 34959468
I am doing this in SSIS using an Excel Source and then connecting to a Script Component and the VB code is in the Script Component
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 34959738
not sure how you are getting the data, but here is a working example of what I meant you should you.

        'first declare your root element
        Dim xNew As XElement = New XElement("transfer")
        'some test data in a list of string
        Dim TestData As New List(Of String) From {"USD IBL 1038 -118 2/1/2011 Collateral Inbound", "USD IBL 1039 -118 3/1/2011 Collateral Outbound"}
        'then iterate through the source here to call the function
        For Each x As String In TestData
            Dim Res = x.Split(" ")
            xNew.Add(ProcXML(Res(0), Res(1), Res(2), Res(3), Res(4), Res(5), Res(6)))
        Next
        Dim s As XmlSerializer = New XmlSerializer(xNew.GetType)
        Using w As New XmlTextWriter("Path", System.Text.Encoding.UTF8)
            w.Formatting = Formatting.Indented
            s.Serialize(w, xNew)
        End Using

Open in new window


The above code callthe following function

    Public Function ProcXML(ByVal Currency As String, ByVal Entity As String, ByVal AccountID As String, _
                            ByVal Movement As String, ByVal ValueDate As String, ByVal Narration As String, _
                            ByVal CounterParty As String) As List(Of XElement)

        Dim CurELE As XElement = New XElement("Currency", Currency)
        Dim EntELE As XElement = New XElement("Legal Entity", Entity)
        Dim ActELE As XElement = New XElement("Accountid", AccountID)
        Dim MovELE As XElement = New XElement("Movement", Movement)
        Dim ValELE As XElement = New XElement("Value Date", ValueDate)
        Dim NarELE As XElement = New XElement("Narration", Narration)
        Dim CouELE As XElement = New XElement("Counterparty", CounterParty)
        'if you areusing .NET 4.0 you can use this else add to the list per usual
        Dim returnList As New List(Of XElement) From {CurELE, EntELE, ActELE, MovELE, ValELE, NarELE, CouELE}
        Return returnList

    End Function

Open in new window



In your example code, the Sub simply gets ONE element and writes it to disk. There is not iterating through a series of data to achieve what you want. I have tried to get some dummy data for you and elaborate the iteration that is missing from your code, hope it makes sense.
0
 
LVL 17

Accepted Solution

by:
nepaluz earned 500 total points
ID: 34959887
To get to your suggested file, you will have to change the first part of the code I suggested above to:
        'first declare your root element
        Dim xNew As XElement = New XElement("transferupload")
        'some test data in a list of string
        Dim TestData As New List(Of String) From {"USD IBL 1038 -118 2/1/2011 Collateral Inbound", "USD IBL 1039 -118 3/1/2011 Collateral Outbound"}
        'then iterate through the source here to call the function
        For Each x As String In TestData
            Dim Res = x.Split(" ")
            xNew.Add(New XElement("transfer", ProcXML(Res(0), Res(1), Res(2), Res(3), Res(4), Res(5), Res(6))))
        Next
        Dim s As XmlSerializer = New XmlSerializer(xNew.GetType)
        Using w As New XmlTextWriter("C:\NewFile.xml", System.Text.Encoding.UTF8)
            w.Formatting = Formatting.Indented
            s.Serialize(w, xNew)
        End Using

Open in new window


The resultant XML would thus be:


XML-Image.png
0
 

Author Comment

by:abooysen2
ID: 34959934
Hi Have done this in my code and getting the results but my does not look fine
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Xml
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper



Public Class ScriptMain
    Inherits UserComponent


    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        '
        ' Add your code here
        '
        ' Creates an XML file is not exist.
        Dim tw As System.Xml.XmlTextWriter
        Dim FullPath As String = "C:\Users\anthonio.booysen\xmltest.xml"
        'Dim tw As New XmlTexttw("C:\Users\anthonio.booysen\xmltest.xml", System.Text.Encoding.UTF8)

        Dim endtag As Byte() = System.Text.Encoding.UTF8.GetBytes("")
        Dim fs As System.IO.FileStream = System.IO.File.OpenWrite(FullPath)
        fs.Seek(endtag.Length, System.IO.SeekOrigin.End)



        tw = New System.Xml.XmlTextWriter(fs, System.Text.Encoding.UTF8)



        ' Starts a new document.




        tw.WriteStartDocument()

        ' Add elements to the file.
        tw.WriteStartElement("transferupload")


        tw.WriteStartElement("transfer")
        tw.WriteStartElement("Currency", "")
        tw.WriteString(Row.Currency.ToString)
        tw.WriteEndElement()
        tw.WriteStartElement("Legal Entity", "")
        tw.WriteString(Row.LegalEntity.ToString)
        tw.WriteEndElement()
        tw.WriteStartElement("Accountid", "")
        tw.WriteString(Row.Accountid.ToString)
        tw.WriteEndElement()
        tw.WriteStartElement("Movement", "")
        tw.WriteString(Row.Movement.ToString)
        tw.WriteEndElement()
        tw.WriteStartElement("Value Date", "")
        tw.WriteString(Row.Valuedate.ToString)
        tw.WriteEndElement()
        tw.WriteStartElement("Narration", "")
        tw.WriteString(Row.Narration.ToString)
        tw.WriteEndElement()
        tw.WriteStartElement("Counterparty", "")
        tw.WriteString(Row.Counterparty.ToString)
        tw.WriteEndElement()






        ' Ends the document 
        tw.WriteEndDocument()
        tw.Close()


    End Sub
    
End Class

Open in new window

xmltest.xml
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 34960053
1. The code you have given could NOT have written the file you attached. All the elements in your code are empty.

2. The elements in your code (just like in my code) are INVALID and thus you will get errors if you try to use the code as is.

For my code, you will have to replace all elements with spaces in their names with an underscore. If you areeagle eyed, you will have noticed that the imagein my XML file has these elements' names chaged, e.g Value Date changed to Value_Date etc ... In code, the culprit would be

        'change this
        Dim ValELE As XElement = New XElement("Value_Date", ValueDate)

        'to this
        Dim ValELE As XElement = New XElement("Value_Date", ValueDate)

Open in new window



Because of the errors in your XML file, I can NOT auto open it using the default viewer (in my case Explorer).

Finally, your code makes use of a class named UserComponent. This may contain the iteration routine that you need to examine. Having said that, though, the resulting XML will not meet your requirements, because liske I mentioned, all the elements in your routine are BLANK / EMPTY!
0
 

Author Comment

by:abooysen2
ID: 34960084
Hi

I am try to understand how to apply the code to my ENV as I am not fully understanding VB.net


'first declare your root element
        Dim xNew As XElement = New XElement("transferupload")
        'some test data in a list of string
        Dim TestData As New List(Of String) From {"USD IBL 1038 -118 2/1/2011 Collateral Inbound", "USD IBL 1039 -118 3/1/2011 Collateral Outbound"}
        'then iterate through the source here to call the function
        For Each x As String In TestData
            Dim Res = x.Split(" ")
            xNew.Add(New XElement("transfer", ProcXML(Res(0), Res(1), Res(2), Res(3), Res(4), Res(5), Res(6))))
        Next
        Dim s As XmlSerializer = New XmlSerializer(xNew.GetType)
        Using w As New XmlTextWriter("C:\NewFile.xml", System.Text.Encoding.UTF8)
            w.Formatting = Formatting.Indented
            s.Serialize(w, xNew)
        End Using




 Public Function ProcXML(ByVal Currency As String, ByVal Entity As String, ByVal AccountID As String, _
                            ByVal Movement As String, ByVal ValueDate As String, ByVal Narration As String, _
                            ByVal CounterParty As String) As List(Of XElement)

        Dim CurELE As XElement = New XElement("Currency", Currency)
        Dim EntELE As XElement = New XElement("Legal Entity", Entity)
        Dim ActELE As XElement = New XElement("Accountid", AccountID)
        Dim MovELE As XElement = New XElement("Movement", Movement)
        Dim ValELE As XElement = New XElement("Value Date", ValueDate)
        Dim NarELE As XElement = New XElement("Narration", Narration)
        Dim CouELE As XElement = New XElement("Counterparty", CounterParty)
        'if you areusing .NET 4.0 you can use this else add to the list per usual
        Dim returnList As New List(Of XElement) From {CurELE, EntELE, ActELE, MovELE, ValELE, NarELE, CouELE}
        Return returnList

    End Function

Open in new window

0
 
LVL 17

Expert Comment

by:nepaluz
ID: 34960223
The code I gave you should be adequate to write the XML that you wish to write using the test data. A little tweaking to incorporate your data loading routine is all it requires. However, since you are not willing to divulge how you call the routine, I am un-abe to suggest a solution for that code.
0
 

Author Comment

by:abooysen2
ID: 35067582
I manage to get this to work. Took a long time but it works
0
 

Author Closing Comment

by:abooysen2
ID: 35067611
I manage to get this to work. Took a long time but it works
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question