Parsing XML with SSIS transformation script

October 14, 2010 at 9:33 am | Posted in SSIS | Leave a comment

The ssis query component runs a query on the database and return the XML data in text

The transformation script will read the input row, parse the blob ito string and then parse the XML data embeded within the text

Dim xml String As String= System.Text.Encoding.ASCII.GetString(Row.MESSAGE.GetBlobData(0, CInt(Row.MESSAGE.Length)))

Dim sr As New System.IO.StringReader(xmlString)

Dim doc As New Xml.XmlDocument doc.Load(sr)

Dim reader As New Xml.XmlNodeReader(doc)

While reader.Read() Output0Buffer.AddRow()

If reader.Name = “TagName” Then

Try

Output0Buffer.PRECORDID = reader.GetAttribute(1)

….

Be the first to like this post.

Leave a Comment »

Enter your comment here...

Please log in using one of these methods to post your comment:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Connecting to %s