Converting an XML file back to an ADO record set

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marci Weinberger - 29 Oct 2006 06:37 GMT

Hi All,

I would like to take an existing XML file that I've read into a string and
then convert that to an ADO (not ADO.Net) DB record set that I can then
insert into a database table. To this end, I found out how to obtain the XML
schema for an existing ADO database table. I used the following code
fragment:

rsData.Save oStream, adPersistXML
strOriginalXML = oStream.ReadText(adReadAll)

The resulting string, which I placed into a file for easy viewing has a
different format that most XML files that I have looked at. The idea is to
keep the top part of what I downloaded and then replace the "<rs:data><z:row
..... /></rs:data>" portion of the XML file with the data from my XML
document, before converting it back to ADO.

The problem is that an ADO generated XML file uses attributes. Here is an
example of one data row generated by ADO.

<z:row fldIDPrimary='1' fldURLPostDisp='' fldURLPostAddr=''
fldURLPostSub='' fldURLApplyDisp=''
fldURLApplyAddr=''
fldURLApplySub='' fldIDBoard='' fldPostedDate=''
fldPostedTime='44367' fldTitle='Account Executive' fldPosID='3'
fldBoardID='7867' fldAddrLocCity='Philadelphia' fldAddrLocState='38'
fldAddrLocCountry='210' fldPhoneLocNum='0' fldPhoneLocArea='0'
fldPhoneLocCtry='1' fldPhoneLocExt='0' '
/>

Now here is an XML record from a "normal" XML file.

<job>
<start>10/05/2006</start>
<featured>0</featured>
<job_type>Full-Time</job_type>
<expire>11/19/2006</expire>
<rank>10</rank>
<anonymous>0</anonymous>
<preferred>0</preferred>
<user_id>99515</user_id>
<locations>
<location>
<city>Newark</city>
<state>New Jersey</state>
<state_abbr>NJ</state_abbr>
<country>United States</country>
<country_abbr>US</country_abbr>
</location>
</locations>
</job

As you can see a normal XML file has an entirely different structure. Can
ADO read a structure like this one or am I forced to manually translate each
record. The problem with manually translating each record and creating an
ADO compliant XML file is that the conversion time would be an enormous time
hit for a large number of records. I am hoping to do only mimimal tweaking.

The other problem is the multi indent "location" section. Rather than a flat
layer, this nicer looking XML file has two layers, which I can understand
visually, but how would ADO understand that and how would I say that in the
scema at the top?

Thanks in adance,

Marci Weinberger
ButterflyVista

Reply to this Message

sweevil - 31 Oct 2006 17:09 GMT

Marci,

I too had the same problem reading the xml back into a recordset. Have
you considered using the xml parser to parse your xml string and
populate a SHAPEd recordset?

Granted this example uses hard-coded fields, but it should work if you
tweak it a bit.

ex:

Implements IVBSAXContentHandler

Private m_strBuffer     As String
Private m_FirstPass     As Boolean
private Cn      As ADODB.Connection
private rsADO     As ADODB.Recordset
private strShape      As String

Private Sub IVBSAXContentHandler_startDocument()

Set rsADO = Nothing
Set rsADO = New ADODB.Recordset

Set Cn = New ADODB.Connection

'//create temporary new connection
Cn.Open "Provider=MSDataShape;Data Provider=NONE;"

'//fabricate recordset structure via data shaping
strShape = "SHAPE APPEND NEW adChar(len) as FIELD1, " & _
"NEW adChar(len) as FIELD2, " & _
"NEW adChar(len) as FIELD3, " & _
"NEW adChar(len) as FIELD4, " etc

'//open and populate rs with known values
rsADO.Open strShape, Cn, adOpenStatic, adLockOptimistic, -1

End Sub

Private Sub IVBSAXContentHandler_characters(strChars As String)
m_strBuffer = m_strBuffer & Trim$(strChars)
End Sub

Private Sub IVBSAXContentHandler_endElement(strNamespaceURI As String,
_
strLocalname As String, _
strQName As String)

With rsADO
    if strlocalname = "locations"
    .AddNew
    end if

    Select Case UCase$(strLocalname)
        Case "FIELD1"
     .Fields(0) = m_strBuffer
        CASE "FIELD2"
     .Fields(1) = m_strBuffer
        CASE "FIELD3"
     .Fields(2) = m_strBuffer
        CASE "FIELD4"
     .Fields(3) = m_strBuffer

        ....
    End Select

End With

end sub

Private Property Set IVBSAXContentHandler_documentLocator(ByVal RHS As
MSXML2.IVBSAXLocator)

End Property

Private Sub IVBSAXContentHandler_endDocument()

End Sub

Private Sub IVBSAXContentHandler_endPrefixMapping(strPrefix As String)

End Sub

Private Sub IVBSAXContentHandler_ignorableWhitespace(strChars As
String)

End Sub

Private Sub IVBSAXContentHandler_processingInstruction(strTarget As
String, strData As String)

> Hi All,
>
[quoted text clipped - 63 lines]
> Marci Weinberger
> ButterflyVista

Reply to this Message

VB Forum / Database Access / October 2006

Converting an XML file back to an ADO record set