Write XML file using table data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Write XML file using table data

Author

Topic

million.lk
Starting Member

1 Post

Posted - 2009-06-05 : 04:02:52

Hi, I have add script componet to write XML file based on my sql table, it creaete XML file successfully but values getting duplicate. I'll paste my code pl check it where duplication occur.
--------------------------

' 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.Data
Imports System.Math
''Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Imports System.Reflection
Imports System.Globalization

Public Class ScriptMain
Inherits UserComponent

'Private rootElement1 As String = "Exceptions"
'Private rowElement As String = "Exception"
Private rootElement As String = "Root"
Private rowElement As String = "Row"

Private targetFile As String
Private xmlWriter As StreamWriter
Private columns As Integer()
Private columnames As String()

Private strHeader, strContent As String

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
targetFile = CType(Me.Connections.Destination.AcquireConnection(Nothing), String)

End Sub

Public Overrides Sub PreExecute()
xmlWriter = New StreamWriter(targetFile, False)
'xmlWriter.WriteLine(FormatElement(rootElement))

Dim input As IDTSInput90 = ComponentMetaData.InputCollection(0)

ReDim columns(input.InputColumnCollection.Count)
columns = Me.GetColumnIndexes(input.ID)

Dim column As IDTSInputColumn90
ReDim columnames(input.InputColumnCollection.Count)

Dim counter As Integer
counter = 0

For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
columnames(counter) = column.Name
counter = counter + 1
Next
End Sub

Public Overrides Sub PostExecute()
'xmlWriter.WriteLine(FormatElement(rootElement1, True))

xmlWriter.Close()
End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
Dim DestID As String = "nothing"
Dim x As Integer = 0
While Buffer.NextRow()
'xmlWriter.Write(FormatElement(rowElement))

Dim counter As Integer
counter = 0

Dim colVaL, colName As String

For Each index As Integer In columns

Dim value As Object = Buffer(index)
colVaL = Trim(value.ToString())
colName = columnames(counter).ToString()
'xmlWriter.Write(FormatElement(colName) & colVaL & FormatElement(columnames(counter).ToString(), True))

Select Case colName
Case "DestID"
If DestID <> colVaL Then
If DestID <> "nothing" Then
xmlWriter.Write(strHeader)
xmlWriter.Write(strContent)
xmlWriter.WriteLine(" </Exceptions>")
xmlWriter.WriteLine("</UpExReq>")
End If

Select Case DestID
Case "nothing"
Me.Connections.Destination.ReleaseConnection(Nothing)
Case "AR6001"
Me.Connections.DestinationAR.ReleaseConnection(Nothing)
Case "BR6001"
Me.Connections.DestinationBR.ReleaseConnection(Nothing)
Case "CL6001"
Me.Connections.DestinationCL.ReleaseConnection(Nothing)
Case "CO6001"
Me.Connections.DestinationCO.ReleaseConnection(Nothing)
Case "VE6001"
Me.Connections.DestinationVE.ReleaseConnection(Nothing)

End Select

xmlWriter.Close()
xmlWriter = Nothing

DestID = colVaL
Select Case colVaL
Case "AR6001"
targetFile = CType(Me.Connections.DestinationAR.AcquireConnection(Nothing), String)
Case "BR6001"
targetFile = CType(Me.Connections.DestinationBR.AcquireConnection(Nothing), String)
Case "CL6001"
targetFile = CType(Me.Connections.DestinationCL.AcquireConnection(Nothing), String)
Case "CO6001"
targetFile = CType(Me.Connections.DestinationCO.AcquireConnection(Nothing), String)
Case "VE6001"
targetFile = CType(Me.Connections.DestinationVE.AcquireConnection(Nothing), String)

End Select

xmlWriter = New StreamWriter(targetFile, False)

strHeader = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf
strHeader = strHeader & "<UpExReq Version=""4.0.2"" xmlns:com=""urn:com.parcelhouse.rnrconnect.business.xml.common.type"">" & vbCrLf
strHeader = strHeader & " <com:Message TransferDateTime=""" & Now.ToString("d") & " " & Now.ToString("T") & """ TimeZoneGmt=""+3"" Version=""4.0.2"" Originator=""NMS"" BrokerId=""002""/>" & vbCrLf
strHeader = strHeader & " <Authentication>" & vbCrLf
strHeader = strHeader & " <SiteId>" & DestID & "</SiteId>" & vbCrLf

strContent = ""
x = 0
Else
'strHeader = ""
End If

Case "UserID"
If x = 0 Then
strHeader = strHeader & " <UserId>" & colVaL & "</UserId>" & vbCrLf
End If
Case "Password"
If x = 0 Then
strHeader = strHeader & " <Password>" & colVaL & "</Password>" & vbCrLf
End If
Case "ToolType"
If x = 0 Then
strHeader = strHeader & " <ToolType>" & colVaL & "</ToolType>" & vbCrLf
End If
Case "AccessKey"
If x = 0 Then
strHeader = strHeader & " <AccessKey>" & colVaL & "</AccessKey>" & vbCrLf
strHeader = strHeader & " </Authentication>" & vbCrLf
End If
Case "LogPartID"
If x = 0 Then
strHeader = strHeader & " <LogPartID>" & colVaL & "</LogPartID>" & vbCrLf
End If
Case "ClientReferenceNumber"
If x = 0 Then
strHeader = strHeader & " <ClientReferenceNumber>" & colVaL & "</ClientReferenceNumber>" & vbCrLf
End If
Case "LHUB"
If x = 0 Then
strHeader = strHeader & " <LHub>" & colVaL & "</LHub>" & vbCrLf
strHeader = strHeader & " <Exceptions>" & vbCrLf
End If
x = 1
Case "IMEI"
strContent = strContent & " <Exception>" & vbCrLf
strContent = strContent & " <IMEI>" & Trim(colVaL) & "</IMEI>" & vbCrLf
Case "RepairReturn"
strContent = strContent & " <TriggerLeg>" & colVaL & "</TriggerLeg>" & vbCrLf
Case "TriggerEvent"
strContent = strContent & " <TriggerEvent>" & colVaL & "</TriggerEvent>" & vbCrLf
Case "Action"
strContent = strContent & " <Action>" & colVaL & "</Action>" & vbCrLf
Case "ExCode"
strContent = strContent & " <ExCode>" & colVaL & "</ExCode>" & vbCrLf
Case "ExDesc1"
strContent = strContent & " <ExDesc1>" & colVaL & "</ExDesc1>" & vbCrLf
Case "ExDesc2"
strContent = strContent & " <ExDesc2>" & colVaL & "</ExDesc2>" & vbCrLf
strContent = strContent & " </Exception>" & vbCrLf

End Select
counter = counter + 1
Next

'xmlWriter.WriteLine(FormatElement(rowElement, True))
End While

'If DestID = "VE6001" Then
xmlWriter.Write(strHeader)
xmlWriter.Write(strContent)
xmlWriter.WriteLine(" </Exceptions>")
xmlWriter.WriteLine("</UpExReq>")
'End If
End Sub

Private Function FormatElement(ByVal elementName As String) As String
Return FormatElement(elementName, False)
End Function

Private Function FormatElement(ByVal elementName As String, ByVal closingTag As Boolean) As String
Dim returnValue As String
If closingTag Then
returnValue = "</"
Else
returnValue = "<"
End If
returnValue += elementName + ">"

Return returnValue
End Function

End Class

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-05 : 04:25:05

You you heard of the FOR XML PATH statement in SQL Server?
You can have SQL Server to create your XML document for you.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources