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?
"Jeremy Cowles" <je*************************@asifl.com> wrote in message
news:9I*********************@twister.tampabay.rr.c om...

"steve" <as*@abc.com> wrote in message
news:vg************@corp.supernews.com...

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.

Are you trying to use SQLXML (SQL structured as XML), or do you literally
want to dump XML into SQL byte for byte (putting a file into a text

field)?
Jeremy

[Glenn Wilson]

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

Glenn,

-----Original Message-----
i've got a unix system and windows. i'm using web services to synchronize anoracle db on unix w/ a sql server db on windows. i'm dealing with xml in theform of records.

does that make sense?
"Jeremy Cowles" <je*************************@asifl.com> wrote in messagenews:9I*********************@twister.tampabay.rr. com...

"steve" <as*@abc.com> wrote in message
news:vg************@corp.supernews.com...

> 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.
>
Are you trying to use SQLXML (SQL structured as XML), or do you literally want to dump XML into SQL byte for byte (putting a

file into a textfield)?

Jeremy

.

[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.
"Glenn Wilson" <gw***@hotmail.com> wrote in message
news:91****************************@phx.gbl...

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

Glenn,

-----Original Message-----
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?
"Jeremy Cowles" <je*************************@asifl.com>

wrote in message

news:9I*********************@twister.tampabay.rr. com...

"steve" <as*@abc.com> wrote in message
news:vg************@corp.supernews.com...
> 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.
>

Are you trying to use SQLXML (SQL structured as XML), or do you literally want to dump XML into SQL byte for byte (putting a

file into a text

field)?

Jeremy

.

[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 - )

"Jeremy Cowles" <je*************************@asifl.com> wrote in message
news:lZ*******************@twister.tampabay.rr.com ...

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.
"steve" <al*****@jaslkasdfj.com> wrote in message
news:vg************@corp.supernews.com...

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]

"steve" <as*@abc.com> wrote in message
news:vg************@corp.supernews.com...

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

health.

Any time.

[steve]

wink.

"Jeremy Cowles" <je*************************@asifl.com> wrote in message
news:Dj*******************@twister.tampabay.rr.com ...

"steve" <as*@abc.com> wrote in message
news:vg************@corp.supernews.com...

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

health.

Any time.