Import XML String into Access table

[chrisjohns]

Hi,

I have an XML document im trying to read into Access, i can import it in as a string but then i dont kno how to 'chop it up' into the fields i want.

e.g

<Date>2009-03-17</Date><Name>JOHNSON</Name><Position></Position><Age>21</Age>

Into the Columns for:
Date
Name
Position
Age

Many Thanks

 

[irlbinky]

I can think of 2 ways to do this at the moment - both have their drawbacks and both snipets could be modified to be more pleasing to the eye.

temp1 = "<Date>2009-03-17</Date><Name>JOHNSON</Name><Position></Position><Age>21</Age>"

mylength = Len("<date>")
myinterval = InStr(1, temp1, "<Date>") + mylength 'starts at 1 due to it being first field being looked at
mydate = Mid(temp1, myinterval, InStr(mylength, temp1, "</Date>") - myinterval)
MsgBox mydate

mylength = Len("<name>")
myinterval = InStr(mylength, temp1, "<Name>") + mylength
myname = Mid(temp1, myinterval, InStr(mylength, temp1, "</Name>") - myinterval)
MsgBox myname

and repeat the last 4 lines for each other xml tag


The other method uses the split function
Dim individualword
Dim eachword As Variant
Dim counter As Integer
Dim output(4) As String
Dim temp1 As String

temp1 = "<Date>2009-03-17</Date><Name>JOHNSON</Name><Position></Position><Age>21</Age>"
temp1 = Replace(temp1, "<Date>", "")
temp1 = Replace(temp1, "</Date>", " ***")
temp1 = Replace(temp1, "<Name>", "")
temp1 = Replace(temp1, "</Name>", " ***")
temp1 = Replace(temp1, "<Position>", "")
temp1 = Replace(temp1, "</Position>", " ***")
temp1 = Replace(temp1, "<Age>", "")
temp1 = Replace(temp1, "</Age>", " ")

counter = 0
eachword = Split(temp1, "***")
For Each individualword In eachword
output(counter) = Left(individualword, Len(individualword) - 1)
counter = counter + 1
Next

The split function relies on the fact that *** doesn;t appear normally in the string and you replace the closing tags with ' ***' and then ignore the space when storing it in the array to allow for empty fields like position

 

[keenanbr]

try

http://www.devx.com/getHelpOn/10MinuteSolution/20382

 

[dilettante]

If your XML data is structured as a normal XML document, you'd have some root element for the document itself, and some element representing each row. It would look more like:

<Document>
  <Employee>
    <Date>2009-03-18</Date><Name>JOHNSON</Name><Position></Position><Age>21</Age>
  </Employee>
  <Employee>
    <Date>2009-03-17</Date><Name>SMITH</Name><Position></Position><Age>23</Age>
  </Employee>
  <Employee>
    <Date>2009-03-21</Date><Name>WATSON</Name><Position></Position><Age>19</Age>
  </Employee>
</Document>

XML documents of this form are fairly simple to parse into an ADO Recordset by use of the ADO Simple provider and the MSXML DSO Control:

    Dim rsXML As ADODB.Recordset
    Dim fldXML As ADODB.Field
    
    Set rsXML = New ADODB.Recordset
    With rsXML
        .ActiveConnection = "Provider=MSDAOSP;Data Source=MSXML2.DSOControl;"
        .Open "data.xml"
        'List field names.
        For Each fldXML In .Fields
            Text1.Text = Text1.Text & fldXML.Name & vbTab
        Next
        'List the data.
        Text1.Text = Text1.Text & vbNewLine _
                   & .GetString(, , vbTab, vbNewLine, "*null*")
        .Close
    End With