Importing table from SQL server to XML

Question

I am trying to import information from SQL server to XML as well as trying to create an .xml file in E drive. However the code is shown below is not working:

    CREATE PROC x
    AS
    DECLARE @xmlOutput xml,
            @myFile varchar(8000)


    SET @xmlOutput= ( SELECT TOP 1000 [tmc_code]
                                ,[measurement_tstamp]
                                ,[speed]
                                ,[average_speed]
                                ,[reference_speed]
                                ,[travel_time_minutes]
                                ,[confidence_score]
                                ,[cvalue]
                                 FROM [INRIX_DATA].[dbo].[Sample]
                                 FOR XML AUTO)

    SET @myFile='E:\britain.xml'

    EXEC x

Sachin · Accepted Answer · 2013-04-17 23:23:45Z

2

You can use xp_cmdshell, and the bcp utility to achive this

EXEC xp_cmdshell 'bcp "SELECT * FROM MyTable FOR XML AUTO, ELEMENTS" queryout "E:\mytableFile.xml" -c -T'

answered Apr 17, 2013 at 23:23

Sachin

41.1k7 gold badges94 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Importing table from SQL server to XML

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related