Link to home
Start Free TrialLog in
Avatar of jovid101
jovid101Flag for United Kingdom of Great Britain and Northern Ireland

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;
  • CCOMPANY
  • CCOMPANY_P
  • CFISCYEARPER
  • CFUNCAREA
  • CGLACCT
  • KCENDING_BALANCE_CURRCOMP

Ideally the output format should be in a columnar format such as

CCOMPANY,CCOMPANY_P,CFISCYEARPER,CFUNCAREA,CGLACCT,KCENDING_BALANCE_CURRCOMP
1000, 5000,12017,,111000,44706.20000


Many thanks
XML_Sample.xml
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Hi jovid101;

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.
Imports System.Xml.Linq

'' Loads the XML document from the file system. Change the path and file name to what you need.
Dim xdoc As XDocument = XDocument.Load("C:\Working Directory\XML_Sample.xml")
'' If the XML is in a variable in memory then use this in place of the above command.
Dim xdoc As XDocument = XDocument.Parse(Variable Name Here)

'' The XNamespace used in the document needed to access the nodes.
Dim m As XNamespace = "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
Dim d As XNamespace = "http://schemas.microsoft.com/ado/2007/08/dataservices"

Dim result As List(Of String) = _
  (From entry In xdoc.Root.Descendants(m + "properties") _
   Select entry.Element(d + "CCOMPANY").Value & "," & _
       entry.Element(d + "CCOMPANY_P").Value & "," & _ 
       entry.Element(d + "CFISCYEARPER").Value & "," & _
       entry.Element(d + "CFUNCAREA").Value & "," & _
       entry.Element(d + "CGLACCT").Value & "," & _
       entry.Element(d + "KCENDING_BALANCE_CURRCOMP").Value).ToList()

Open in new window

Avatar of jovid101

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
Make sure that your project imports System.Collections.Generic Namespace otherwise you will get that type of an error.
Did that help?
Hi Fernando,

Sorry having server issues and I will let you know soon.

Thanks again for coming back so fast.

Best Regards,
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.Runtime
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.
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
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent Fernando .. your script has made parsing XML look very easy indeed.

Thanks again for all the help
Not a problem jovid101, glad to help.