Post reply

XML Export

  • November 21, 2006 at 1:57 am

    #94828

    Can I generate an XML document from SQL Server 2000 and save that document to a specified location?

    I know you can generate XML by using a statement such as 'SELECT * FROM table1 FOR XML AUTO'

    but can I save XML generated to a file for future use?

    Thanks

  • November 22, 2006 at 5:33 am

    #673419

    Not the cleanest but it works...

    __________________________

    CREATE  PROCEDURE [usp_ExportXML] AS

    DECLARE @TableName varchar(50)

    DECLARE @BCPSTRING varchar (255)

    DECLARE tableList CURSOR FOR

    select TableName from TableUpdateLog where Updated = 1

    OPEN tableList

    FETCH NEXT FROM tableList

    INTO @TableName

    WHILE @@FETCH_STATUS = 0

    BEGIN

     --Create the temp XML

         SELECT @BCPSTRING = 'bcp "select * from table..'

     SELECT @BCPSTRING = @BCPSTRING + @TableName

     SELECT @BCPSTRING = @BCPSTRING +  ' as '

     SELECT @BCPSTRING = @BCPSTRING + @TableName

     SELECT @BCPSTRING = @BCPSTRING +  ' for xml auto, xmldata" queryout C:\Data\'

     SELECT @BCPSTRING = @BCPSTRING + @TableName

     SELECT @BCPSTRING = @BCPSTRING +  'temp.xml -T -c -r -t'

    queryout

     execute master..xp_cmdshell @BCPSTRING

     FETCH NEXT FROM tableList

     INTO @TableName

    END

    CLOSE tableList

    DEALLOCATE tableList

    GO

  • November 22, 2006 at 6:02 am

    #673423

    Thanks for that but I'm getting an error when I try to run it

    Server: Msg 170, Level 15, State 1, Procedure usp_ExportXML, Line 28

    Line 28: Incorrect syntax near 'queryout'.

  • November 22, 2006 at 6:27 am

    #673429

    Hi,

    you could try the following:

    Create an DTS with one Active X script in it.

    The code for the script is:

    ='**********************************************************************

    '  Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

     dim oCmd, sSql, oDom

     set oDom = CreateObject("Msxml2.DOMDocument")

     set oCmd = CreateObject("ADODB.Command")

     oCmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=SERVERNAME; Initial Catalog=DATABASE; Integrated Security=sspi"

     sSql = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>SELECT * FROM table1 FOR XML AUTO</sql:query></ROOT>"

     oCmd.CommandText = sSql

     oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

     oCmd.Properties("Output Stream") = oDom

     oCmd.Execute, , 1024

     oDom.Save "C:\\test.xml"

     Main = DTSTaskExecResult_Success

    End Function

    change te connection string to your system and your done.

  • November 22, 2006 at 6:44 am

    #673433

    Thanks, gave that a quick try and it seems to work OK, I'll try changing the path now to get it to write to another server.

  • November 27, 2006 at 2:22 am

    #673985

    Regarding the above script,

    If I wanted to redirect the output to another server, how would I change the script security wise, i.e. how would the logon/security be changed via th 'Integrated Security=sspi' part?.

    Would I have to enter the logon credentials for a recognised user for the other server?

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

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