Post reply

Using DTS to import XML files

  • January 9, 2005 at 2:05 am

    #162255

    Hi,

    I want to import a simple XML file into a database table. the problem is thant in DTS, there are no connection to an XML file. Can you help me to do so from a DTS.

    thanks.

    example: xml file is

    <root>

    <person>

    <name>person</name>

    <age>25</age>

    </person>

    </root>

     

  • January 9, 2005 at 3:20 am

    #536581

    I haven't attempted what you are trying to do, but it seems like you could use DTS to import the document using the Text File (source) object.  Do a search in Books On Line for OPENXML and sp_xml_preparedocument to see how to work with XML in SQL Server.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • January 10, 2005 at 2:44 am

    #536611

    The way we have done this is to have a DTS package which contains soe VB script to load in the xml file using the OpenTextFile FSO method. The xml file will then be conatined in a DTS global var.

    You will then need a stored procedure with has a parmeter of type Text. You can then pass this to sp_xml_preparedocument and you will be able to use OPENXML.

    To call this Store Procedure from DTS we used a further script task and used ADO, although it may also work using the DTS SQL Task.

    Hope this helps.


    Thanks Jeet

  • January 11, 2005 at 7:58 am

    #536813

    thank's

    It seems very dificult to do. Are there any method to it "in design way" like importing a csv file ???

    and thanks again.

  • February 7, 2005 at 1:00 pm

    #541014

    Unfortunately there isnt any wasy way of doing that with Sql server 2000. But there are some VB components available to do that job.

  • February 9, 2005 at 9:33 am

    #541382

    We do it like this:

     

    Dim oFS

    Dim gFile

    Dim sFile

    Dim oFolder

    Set oFS = CreateObject("Scripting.FileSystemObject")

     ' Instatiate the Bulk Load object

     Set objXMLBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")

    objXMLBL.ConnectionString = "provider=SQLOLEDB.1;data source=(local);database=your database name;integrated security=SSPI"

     objXMLBL.KeepIdentity = False

     objXMLBL.CheckConstraints =True

     objXMLBL.SchemaGen = True

     objXMLBL.SGDropTables = True

     objXMLBL.XMLFragment = True

     ' Set an error log file

     objXMLBL.ErrorLogFile = "Path for error file\XML_Load_Error.xml"

    ' Run it using the mapping schema and the data file

        Set oFolder = Nothing

        Set gFile = Nothing

        imPos = Len(m_XMLDataPath) + 1

        Set oFolder = oFS.GetFolder(m_XMLDataPath) 'Global Variable for XML Path

        Set gFile = oFolder.Files

        For Each sFile In gFile

            If Right(UCase(sFile), 3) = "XML" Then

                  objXMLBL.Execute "Path to XSD Schema file\DB_Mapping_Schema.xsd", sFile

            End If

        Next

        Set oFS = Nothing

        Set oFolder = Nothing

        Set gFile = Nothing

     

    Hope this helps

  • August 20, 2007 at 10:18 pm

    #728175

    I have a question on this VBscripts. Prior running the script do i have to create a table? Please advise. Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply