Using DTS to import XML files

Question

Using DTS to import XML files

nohad

SSC Veteran

Points: 260
More actions
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>

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

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

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

Jeet SSCommitted Points: 1536 More actions · Answer 2

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

nohad SSC Veteran Points: 260 More actions · Answer 3

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.

sbalasub SSC Journeyman Points: 85 More actions · Answer 4

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

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 5

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

Set oFolder = Nothing

Set gFile = Nothing

Hope this helps

pcq0125 SSCrazy Points: 2890 More actions · Answer 6

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