DTS and XML

[klewis10367]

Is there a way to dts an xml file into sql2000? I do not see it as a source option.

 

[AlexCuse]

I'm sure there is an easier/better way to do this, but you could write a stored procedure that uses the sp_xml_preparedocument stored proc and OPENXML function to either insert directly to your table, or to a staging table. THen you'd only need to read your file into a string variable and send it to your proc as a parameter (all from a script task).

If you don't have a lot of manipulation to do this could work though.

Anyway here's an example:

CREATE Procedure TesterXMLInput
    @XML Text
As
SET NOCOUNT ON

Declare @iDoc Integer
Exec sp_xml_preparedocument @iDoc OUTPUT, @XML

-- uncomment the next 2 lines to insert into table
--Insert
--Into    tblA(fldA, fldB, fldC)
Select    fldA, fldB, fldC
From    OpenXML(@iDoc, '//root/data', 1)
With    (
        fldA Int,
        fldB Varchar(10),
        fldC datetime
        )

Test it like this:

execute TesterXMLInput '<root><data fldA = "65" fldB = "B" fldC = "20070905"/></root>'

Hope it helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom