xml and sql server

[steve]

i'v not done a bunch of xml parsing. does anyone have a good resource
showing an example of taking xml data and inserting it into a sql server db
using .net?

any input is appreciated.

tia,

steve

 

[steve]

i've got a unix system and windows. i'm using web services to synchronize an
oracle db on unix w/ a sql server db on windows. i'm dealing with xml in the
form of records.

does that make sense?

 

[Glenn Wilson]

I have done this in the past and have use DTS to create
the link.

Glenn,

 

[steve]

dts is great but it is an additional step during implementation. i'd like to
stick to using web services and manage the code base all in one spot...plus,
the next guy to come along may not know a thing about dts...then it's back
on me. ;^)

thanks for the thought though.

 

[steve]

well, try this and just add the db functionality.

Private Sub SyncStations(ByVal xml As String)
Dim description As String
Dim id As String
Dim record As String
Dim records() As String
Dim stations As String
stations = getSingleSegment("STATIONS", xml)
records = getMultipleSegments("RECORD", stations)
For Each record In records
id = getSingleSegment("ID", record)
description = getSingleSegment("DESCRIPTION", record)
id = getSegmentValue(id)
description = getSegmentValue(description)
MsgBox(id & vbCrLf & description)
Next
End Sub

Private Function getMultipleSegments(ByVal segment As String, ByVal xml
As String) As String()
Dim pattern As String = "<\s*" & segment & "[^>]*>.*?<\s*/\s*" &
segment & "\s*>"
Dim regExp As New Regex(pattern, RegexOptions.Compiled Or
RegexOptions.ExplicitCapture Or RegexOptions.Singleline Or
RegexOptions.IgnoreCase)
If Not regExp.IsMatch(xml) Then Exit Function
Dim match As Match
Dim result() As String
Dim index As Integer
For Each match In regExp.Matches(xml)
ReDim Preserve result(index)
result(index) = match.Value
index += 1
Next
Return result
End Function

Private Function getSingleSegment(ByVal segment As String, ByVal xml As
String) As String
Dim pattern As String = "<\s*" & segment & "[^>]*>.*?<\s*/\s*" &
segment & "\s*>"
Dim regExp As New Regex(pattern, RegexOptions.Compiled Or
RegexOptions.ExplicitCapture Or RegexOptions.Singleline Or
RegexOptions.IgnoreCase)
If Not regExp.IsMatch(xml) Then Exit Function
Dim match As Match = regExp.Match(xml)
Return match.Value
End Function

Private Function getSegmentValue(ByVal xml As String) As String
Dim pattern As String = "<[^>]*>"
Dim regExp As New Regex(pattern)
If Not regExp.IsMatch(xml) Then Exit Function
Return regExp.Replace(xml, "")
End Function

 

[steve]

so i can improve performance by making it slower...;^ )

this is pretty darn simple and i don't have to instanciate node objects all
over the place. i didn't get any hits here about examples or references to
examples...so this is what i came up w/ in lieu of spending time discovering
how to make the xml dom get me the information.

thanks for your generous comments, though, on the state of my mental health.

8 - )

That's insane - i think you can seriously improve preformance by using the
built-in XPath search routines (SelectNode and SelectSingleNode). Even if it
is slower, it will be MUCH simpler.

well, try this and just add the db functionality.

Private Sub SyncStations(ByVal xml As String)
Dim description As String
Dim id As String
Dim record As String
Dim records() As String
Dim stations As String
stations = getSingleSegment("STATIONS", xml)
records = getMultipleSegments("RECORD", stations)
For Each record In records
id = getSingleSegment("ID", record)
description = getSingleSegment("DESCRIPTION", record)
id = getSegmentValue(id)
description = getSegmentValue(description)
MsgBox(id & vbCrLf & description)
Next
End Sub

Private Function getMultipleSegments(ByVal segment As String, ByVal xml
As String) As String()
Dim pattern As String = "<\s*" & segment & "[^>]*>.*?<\s*/\s*" &
segment & "\s*>"
Dim regExp As New Regex(pattern, RegexOptions.Compiled Or
RegexOptions.ExplicitCapture Or RegexOptions.Singleline Or
RegexOptions.IgnoreCase)
If Not regExp.IsMatch(xml) Then Exit Function
Dim match As Match
Dim result() As String
Dim index As Integer
For Each match In regExp.Matches(xml)
ReDim Preserve result(index)
result(index) = match.Value
index += 1
Next
Return result
End Function

Private Function getSingleSegment(ByVal segment As String, ByVal xml As
String) As String
Dim pattern As String = "<\s*" & segment & "[^>]*>.*?<\s*/\s*" &
segment & "\s*>"
Dim regExp As New Regex(pattern, RegexOptions.Compiled Or
RegexOptions.ExplicitCapture Or RegexOptions.Singleline Or
RegexOptions.IgnoreCase)
If Not regExp.IsMatch(xml) Then Exit Function
Dim match As Match = regExp.Match(xml)
Return match.Value
End Function

Private Function getSegmentValue(ByVal xml As String) As String
Dim pattern As String = "<[^>]*>"
Dim regExp As New Regex(pattern)
If Not regExp.IsMatch(xml) Then Exit Function
Return regExp.Replace(xml, "")
End Function

 

[Jeremy Cowles]

thanks for your generous comments, though, on the state of my mental

health.

Any time.

 

[steve]

wink.

health.

Any time.