jovid101
asked on
SSIS - Using VB.NET to parse XML file
Hi,
I am new to parsing XML files in SSIS and I was wondering what would be the VB.NET syntax be in trying to parse the attached file so that I could extract the values for the following fields into a CSV file from the XML file - I suspect looping around the attributes will be required as there will be many records within the xml file.
Fields within the XML file that are needed are;
Ideally the output format should be in a columnar format such as
CCOMPANY,CCOMPANY_P,CFISCY EARPER,CFU NCAREA,CGL ACCT,KCEND ING_BALANC E_CURRCOMP
1000, 5000,12017,,111000,44706.2 0000
Many thanks
XML_Sample.xml
I am new to parsing XML files in SSIS and I was wondering what would be the VB.NET syntax be in trying to parse the attached file so that I could extract the values for the following fields into a CSV file from the XML file - I suspect looping around the attributes will be required as there will be many records within the xml file.
Fields within the XML file that are needed are;
- CCOMPANY
- CCOMPANY_P
- CFISCYEARPER
- CFUNCAREA
- CGLACCT
- KCENDING_BALANCE_CURRCOMP
Ideally the output format should be in a columnar format such as
CCOMPANY,CCOMPANY_P,CFISCY
1000, 5000,12017,,111000,44706.2
Many thanks
XML_Sample.xml
ASKER
Hi Fernando,
That looks like it would do the trick however the compiler is having problems interpreting "Dim result As List(Of String)" - being List is not defined. I did include the reference you mentioned however maybe I do need an additional one ? .. by the way i am using VS 2012 for this.
Thanks
That looks like it would do the trick however the compiler is having problems interpreting "Dim result As List(Of String)" - being List is not defined. I did include the reference you mentioned however maybe I do need an additional one ? .. by the way i am using VS 2012 for this.
Thanks
Make sure that your project imports System.Collections.Generic Namespace otherwise you will get that type of an error.
Did that help?
ASKER
Hi Fernando,
Sorry having server issues and I will let you know soon.
Thanks again for coming back so fast.
Best Regards,
Sorry having server issues and I will let you know soon.
Thanks again for coming back so fast.
Best Regards,
ASKER
Ok that Import resolved the error on LIst but now there is an error stating that xdoc.Root.Descendants(m + "properties") is not queryable. Make sure I am not missing a namespace import for the LINQ provider.
The imports I currently have are:
#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Ru ntime
Imports System.Xml.Linq
Imports System.Collections.Generic
#End Region
Are there anymore that I may need ?
Thanks
The imports I currently have are:
#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Ru
Imports System.Xml.Linq
Imports System.Collections.Generic
#End Region
Are there anymore that I may need ?
Thanks
Yes you need to import the System.Linq name space as well.
ASKER
Ok thanks that worked ... finally what is the best way to view the output of the variable "result" being List(Of string). My intention is to read the XML (which you have done) but I also need to write the output out to a file.
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent Fernando .. your script has made parsing XML look very easy indeed.
Thanks again for all the help
Thanks again for all the help
Not a problem jovid101, glad to help.
The below code snippet will do what you need. It is using Linq to XML to parse the document and return the needed values. The result variable holds a collection of strings, one string for a collection of values.
Open in new window