sql server dts: I would like to utilise DTS to perform the followings. Are they possible?

1. Export data to XML files. Perform conflict resolution against the files.

2. Send the files to external users.

3. Import the XML files to the database. Perform conflick resolution against
the database.

Export Data to XML files..possible.......
send files to external users..possible
import xml files to a database ..possible.

Wat do u mean by conflict checks...need to know whta you exactly mean
so can

This script imports an XML file into a table.

As for sending the file to an external user, if the server has SQLMail
setup, use that in the dts package. Or use an ActiveX Step with VBScript
calling CDOSYS.
===================================

USE WORKINPROGRESS
GO

SET NOCOUNT ON

DECLARE @xml_file varchar(200)
SET @xml_file = 'd:\temp\mapping54149.xml'


-- Drop and Recreate Tables Being Used by this script
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tmp_XML_Data' AND type = 'U')
DROP TABLE tmp_XML_Data

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tmp_XML_Fields' AND type = 'U')
DROP TABLE tmp_XML_Fields

CREATE TABLE tmp_XML_Data(
Row_ID INTEGER IDENTITY(1,1) PRIMARY KEY,
XML_Text text
)

CREATE TABLE [tmp_XML_Fields] (
[id] [varchar] (100) NULL ,
[taborder] [int] NULL ,
[field_value] [varchar] (2000) NULL
) ON [PRIMARY]



-- Insert a blank row for use of TEXTCOPY
INSERT INTO [tmp_XML_Data] SELECT ''


DECLARE @cmdstring varchar(2000)

-- User running this script requires acces to master..xp_cmdshell
-- the command string (cmdstring) requires the following:
-- - Shortname location of the textcopy.exe
-- S/ servername (. for local)
-- U/ local user name ( with rights to the database )
-- P/ password
-- D/ database
-- T/ table to insert the data
-- C/ column to insert data (xml file

SET @cmdstring = 'D:\Progra~1\Micros~1\MSSQL\Binn\TextCopy.exe /S . /U
test_user /P password /D workinprogress /T tmp_XML_Data /C XML_Text /F ' +
@xml_file + ' /W "WHERE Row_ID=1" /I'
EXEC master..xp_cmdshell @cmdstring, no_output
-- After this is successful, you will have 1 row with the entire XML_Text
inserted into it.


DECLARE @hdoc int
DECLARE @doc varchar(8000)
SELECT @doc = XML_Text FROM tmp_XML_Data

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc


-- Read the XML file into a temp table, breaking it down
INSERT INTO tmp_XML_Fields ([id],taborder,field_value)
SELECT * FROM OpenXML(@hdoc, 'filemap/mapping/field', 3) --data structure
WITH
dbo.tmp_XML_Fields

-- Remove the internal representation.
exec sp_xml_removedocument @hdoc

SELECT * from tmp_XML_Data

SELECT * from tmp_XML_Fields

SET NOCOUNT OFF





[quoted text, click to view]