• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

XML Directory Import into SQL Server - no data

I'm using this code to import a directory of XML files into my Database:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
CONST strFilesPath = "<C:\path>"
CONST adOpenKeyset = 1
CONST adLockOptimistic = 3

Function Main()
      Dim objFSO
      Dim objFolder
      Dim objFilesColl
      Dim iFilesCount
      Dim objFile

      Dim objXMLDOM
      Dim objNodes
      Dim objNodeItem
      
      Dim objADORS
      Dim objADOCnn

      Dim strCurFileName

      'Create and initialize (Open) ADO Connection
      Set objADOCnn = CreateObject("ADODB.Connection")
      objADOCnn.Open "PROVIDER=SQLOLEDB;SERVER=<.>;UID=sa;PWD=<.>;DATABASE=<.>;"

      'Create MSXML 4.0 DOM Object and initialize it
      Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
      objXMLDOM.async = False
      objXMLDOM.validateOnParse = False

      'Get a list of files in the specified directory
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      Set objFolder = objFSO.GetFolder(strFilesPath)
      Set objFilesColl = objFolder.Files

      'Load each file in MSXML DOM and use ADO to insert data into the table
      For Each objFile in objFilesColl
            
            strCurFileName = strFilesPath & "\" & objFile.Name

            'Load the XML file
            'No error handling done
            objXMLDOM.load strCurFileName

            Set objNodes = objXMLDOM.selectNodes("/Persons")

            'Create and Open the recordset
            Set objADORS = CreateObject("ADODB.Recordset")
            objADORS.Open "SELECT * FROM Person", objADOCnn, adOpenKeyset, adLockOptimistic

            'Add records
            For Each objNodeItem In objNodes
                  With objADORS
                        .AddNew
                        'Test with 2 fields
                        .fields("Firstname") = objNodeItem.selectSingleNode("FirstName").nodeTypedValue
                        .fields("ID") = objNodeItem.selectSingleNode("ID").nodeTypedValue
                        .Update
                  End With
            Next

            objADORS.Close

      Next

      objADOCnn.Close

      Set objADORS = Nothing
      Set objADOCnn = Nothing
      Set objXMLDOM = Nothing
      Set objFSO = Nothing

      Main = DTSTaskExecResult_Success
End Function


The loop is working, but my database stays empty, no records are added, I think there is something wrong with the XML structure, but I have little XML knowledge
This is a piece of the XML structure


<?xml version="1.0" encoding="UTF-8"?>
<xMRKBEL_SGPBEL xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="<file>">
      <Header>
            <Id>272</Id>
            <CreationDate>20060427</CreationDate>
            <CreationTime>061417</CreationTime>
            <FileName>20060427_00000272.xml</FileName>
            <DocumentVersion>
                  <Major>1</Major>
                  <Minor>0</Minor>
            </DocumentVersion>
            <Channel>
                  <Signature>signature</Signature>
                  <Source>
                        <CompanyName>company</CompanyName>
                        <System>
                              <Id>Id</Id>
                              <Version>
                                    <Major>2</Major>
                                    <Minor>2</Minor>
                              </Version>
                        </System>
                        <Adapter>
                              <Id>Id</Id>
                              <Version>
                                    <Major>1</Major>
                                    <Minor>0</Minor>
                              </Version>
                        </Adapter>
                  </Source>
            </Channel>
      </Header>
      <AppointmentTypes/>
      <Persons>
            <Person>
                  <Id>$00010198032</Id>
                  <TargetId>148597</TargetId>
            </Person>
            <Person>
                  <Id>vagzR.4wGLPQ</Id>
                  <TargetId>615747</TargetId>
            </Person>
            <Person>
                  <Id>$00010194208</Id>
                  <TargetId>64927</TargetId>
            </Person>
            <Person>
                  <Id>$0393849402</Id>
                  <Name>NAMER</Name>
                  <FirstName>PERSONX</FirstName>
                  <Specialty>LAW</Specialty>
                  <Organisation>IIK887</Organisation>
                  <PostalCode>9383</PostalCode>
                  <Town>TOWNVILLE</Town>
            </Person>
                    ...

Any Suggestions, why my fields are not updated ?

Many thx


0
ITBenelux
Asked:
ITBenelux
  • 15
  • 13
1 Solution
 
Anthony PerkinsCommented:
Any reason you are doing it this way.  You may want to consider using OPENXML instead?
0
 
Anthony PerkinsCommented:
Caveat OPENXML is best utilized with a stored procedure.  The big advantage is that you insert the whole Xml with one Insert statement as opposed to cycling through the nodes.
0
 
ITBeneluxAuthor Commented:
There is no specific reason to use this way.  But as said, I am not so familiar with XML.  if there is an easier way to get my XML files (all) into my tables...
Can someone point me into the right direction how to do this?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Anthony PerkinsCommented:
The way that I would do it if the XMl documents are relatively small (less than 1 MB), is open them using the FileSystemObject and pass them in to a stored procedure to process the whole file using OPENXML with one INSERT statement.  If this is the way you would like to go, let me know and I may be able to come up with some code when I get home tonight.  Make sure to include the structure of your Person table.
0
 
ITBeneluxAuthor Commented:
I sounds like a good idea, but as you proposed. I would need some code (due to the urgency of this project).  I put the a full XML file as example on http://users.pandora.be/wn/MSD20060101_00000162.xml .  Thx in advance
0
 
Anthony PerkinsCommented:
Don't forget to post the structure of your "Person" table or I will have to make one up.
0
 
ITBeneluxAuthor Commented:
Hereby the table structure for the person table

3      PrimaryKey      int      4      0
0      ForeignKey      int      4      1
0      Firstname      varchar      50      1
0      Id      char      10      1
0      Name      varchar      50      1
0      PostalCode      char      10      1
0      Specialty      char      10      1
0      TargetId      char      10      1
0      Town      varchar      50      1

0
 
Anthony PerkinsCommented:
Here is the tested code, but first some caveats:
1. For some reason, I had to switch to UTF-16 encoding, the SQL Server Xml Parser did not like names like "BJÖRN".  If you cannot get your Xml documents with that encoding, you can use the cheesy workaround I use.  Since I am using UTF-16 I have to switch to unicode.

2. Since the Xml document contains apostrophes (single quotes) they need to be escaped to pass them into the Stored Procedure as a string.

3. The ActiveX code only handles one file, but it would be trivial to put this in a function and call it from a loop that loops through all the files.  You may want to just open the ADODB Connection and FileSystemObject once.

Here is the Stored Procedure code:

CREATE PROCEDURE usp_AddPersons
                  @XmlPersons ntext       -- Using ntext as the Xml is UTF-16 encoded
AS

Declare @iDoc integer

SET NOCOUNT ON

EXEC sp_xml_preparedocument @iDoc OUTPUT, @XmlPersons

Insert      Person(Id, FirstName)
Select      Id, Firstname
From      OpenXml(@idoc, 'xMRKBEL_SGPBEL/Persons/Person', 2) With (
            Id char(10) 'Id',
            Firstname varchar(50) 'FirstName')

EXEC sp_xml_removedocument @iDoc
GO

Here is the ActiveX code:

Const ForReading = 1
Const adVarWChar = 202, adParamInput = 1, adExecuteNoRecords = 128
Dim FSO
Dim t
Dim XmlPersons
Dim cn
Dim cmd

' First read in the file
Set FSO = CreateObject("Scripting.FileSystemObject")
Set t = FSO.OpenTextFile("Your File Name Goes Here", ForReading, False)
XmlPersons = t.ReadAll
t.Close
Set t = Nothing
Set FSO = Nothing

' Then open the connection and submit the Xml document to the Stored Procedure
Set cn = CreateObject("ADODB.Connection")
With cn
    .ConnectionString = "Your Connection string goes here"
    .Open
End With

Set cmd = CreateObject("ADODB.Command")
With cmd
    Set .ActiveConnection = cn
    .CommandText = "usp_AddPersons"
    .CommandType = 4            ' adCmdStoredProc
    XmlPersons = Replace(XmlPersons, "UTF-8", "UTF-16", 1, 1, vbTextCompare)
    XmlPersons = Replace(XmlPersons, "'", "''")
    .Parameters.Append .CreateParameter("@XmlPersons", adVarWChar, adParamInput, 1073741823, XmlPersons)
    .Execute , , adExecuteNoRecords
End With
Set cmd = Nothing

cn.Close
Set cn = Nothing

You will like the speed with which it process all the nodes.

Enjoy.
0
 
ITBeneluxAuthor Commented:
On the ActiveX script, I get an error message: function not found.  Can this have something to do with any XML add-ons which are not installed ?
0
 
ITBeneluxAuthor Commented:
One additional remark; I noticed that the person table however is filled, so the script works. Should you have any idea on the function error. should be perfect.  I will try to add the loop for the directory now.
0
 
ITBeneluxAuthor Commented:
I dont want to abuse your help, because the procedure you posted is working, I accept the answer.  However, I would appriciate some additional assistence.  For example. I already spend hours on this.  and my deadline is getting closer and closer.
I add the other fields of the person table in the (I believe) proper way, but the data is not showing up in the table.

Insert     Person(Id, FirstName, Name, specialty, organisation, town)
Select     Id, Firstname, Name, specialty, organisation, town
From     OpenXml(@idoc, 'xMRKBEL_SGPBEL/Persons/Person', 2) With (town varchar(50) 'town',Id char(10) 'Id',Firstname varchar(50) 'FirstName',Name varchar(50) 'Name',specialty varchar(50) 'specialty', organisation varchar(50) 'organisation')

any suggestions
0
 
Anthony PerkinsCommented:
What is the problem?
0
 
Anthony PerkinsCommented:
Xml is case sensitive. Change:
From     OpenXml(@idoc, 'xMRKBEL_SGPBEL/Persons/Person', 2) With (town varchar(50) 'town',Id char(10) 'Id',Firstname varchar(50) 'FirstName',Name varchar(50) 'Name',specialty varchar(50) 'specialty', organisation varchar(50) 'organisation')

To:
From     OpenXml(@idoc, 'xMRKBEL_SGPBEL/Persons/Person', 2) With (town varchar(50) 'Town',Id char(10) 'Id',Firstname varchar(50) 'FirstName',Name varchar(50) 'Name',specialty varchar(50) 'Specialty', organisation varchar(50) 'Organisation')
0
 
ITBeneluxAuthor Commented:
Indeed.... case sensative... such detail, but a wise lesson, so indeed that is solved. fantastic !!
0
 
ITBeneluxAuthor Commented:
Any Idea on the "function not found" error when running the ActiveX script ?
0
 
Anthony PerkinsCommented:
>>Any Idea on the "function not found" error when running the ActiveX script ?<<
Not without seeing the code, line and exact error message.
0
 
ITBeneluxAuthor Commented:
when I edit the DTS activescript, I hardly got any options to
track down the error.  Can I open this script in the Query Analyzer ?  
0
 
Anthony PerkinsCommented:
>>when I edit the DTS activescript, I hardly got any options to track down the error. <<
You get an error right?  Ok what is it and where is it, and most important can we see your code.

>>Can I open this script in the Query Analyzer ? <<
No.
0
 
Anthony PerkinsCommented:
P.S. I am still waiting for an explanation as to what I missed in your other thread to merit a "B" grade:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21879641.html#16863106
0
 
ITBeneluxAuthor Commented:
It was not my intention to give a B, but a miss click and trying to to it too fast, has caused this.  If there is a way to change this.  I would, because I really appriciate your help on this one.
0
 
ITBeneluxAuthor Commented:
The error is: ActiveX Scripting : Function not Found

This is the current code

CONST strFilesPath = "C:\Documents and Settings\Desktop\XML\test"
Const ForReading = 1
Const adVarWChar = 202, adParamInput = 1, adExecuteNoRecords = 128
Dim FSO
Dim t
Dim XmlEmployee
Dim cn
Dim cmd
     Dim objFSO
     Dim objFolder
     Dim objFilesColl

     'Get a list of files in the specified directory
     Set objFSO = CreateObject("Scripting.FileSystemObject")
     Set objFolder = objFSO.GetFolder(strFilesPath)
     Set objFilesColl = objFolder.Files

' Then open the connection and submit the Xml document to the Stored Procedure
Set cn = CreateObject("ADODB.Connection")
With cn
    .ConnectionString = "PROVIDER=SQLOLEDB;SERVER=;UID=sa;PWD=;DATABASE=;"
    .Open
End With

       For Each objFile in objFilesColl
      strCurFileName = strFilesPath & "\" & objFile.Name

' First read in the file
Set FSO = CreateObject("Scripting.FileSystemObject")
  Set t = FSO.OpenTextFile(strCurFileName, ForReading, False)
XmlEmployee = t.ReadAll
t.Close
Set t = Nothing
Set FSO = Nothing


Set cmd = CreateObject("ADODB.Command")
With cmd
    Set .ActiveConnection = cn
    .CommandText = "usp_AddEmployee"
    .CommandType = 4          ' adCmdStoredProc
    XmlEmployee = Replace(XmlEmployee, "UTF-8", "UTF-16", 1, 1, vbTextCompare)
    XmlEmployee = Replace(XmlEmployee, "'", "''")
    .Parameters.Append .CreateParameter("@XmlEmployee", adVarWChar, adParamInput, 1073741823, XmlEmployee)
    .Execute , , adExecuteNoRecords
End With
Set cmd = Nothing

      next

cn.Close
Set cn = Nothing

0
 
Anthony PerkinsCommented:
Are you sure that is your entire script?  If this is a DTS ActiveX Script Task it should be wrapped in a function as in:


Function Main()
                ' Your code goes here
               
      Main = DTSTaskExecResult_Success
End Function
0
 
Anthony PerkinsCommented:
>>If there is a way to change this.  I would, because I really appriciate your help on this one.<<

See here from the EE Help:
 
Can I get a grade changed?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18
0
 
ITBeneluxAuthor Commented:
GREAT !! IT works fine now. you saved the day ! Thx again for all your help.
0
 
ITBeneluxAuthor Commented:
>>Can I get a grade changed?<<
I send the request to the moderator...
0
 
ITBeneluxAuthor Commented:
Mr acperkins... I would like to ask one more question (hopefully the last..:-)), up untitl now I worked with a test folder with only 3 xml files in it.  Now in the original folder of 127 XML files, which are generated by system, there are empty XML files (0kb, no header information): the script is generating the following error :

Error Code:0
Error Source: Microsoft VBScript runtime Error
Error Description: Input past end of file
Error on line 39

Is there a way to add a check and skip these empty files ?
0
 
Anthony PerkinsCommented:
Yes.  
If objFile.Size = 0 Then ' Skip
0
 
ITBeneluxAuthor Commented:
Thx Again
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 15
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now