Exporting query results to xml

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Exporting query results to xml

Author

Topic

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-27 : 09:54:40

Hi,

I need to export the query results in to xml file in sql management studio 2008. Kindly let me know the procedure to do it.

Thanks,
Sandesh

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-27 : 10:18:36

Have a lo0ok at the for xml clause.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-27 : 10:19:41

EXEC master..xp_cmdshell
'bcp "Select Left(NTF.AccNo,6) as programid, convert(char(10), NTF.localdate, 103) as localdate, NTF.localtime, NTF.tlogid, NTF.ItemID,NTF.pan as pan, NTF.billamt, NTF.approvalcode, Convert(char(10),NTF.cortexDate,103) as CortexDate, NTF.termcode,NTF.termlocation,NTF.termstreet, NTF.termcity, NTF.termcountry, NTF.mcc,vw.Cardholderid, vw.CardProduct,vw.PromoCode, NTF.txnCode
From Nomad_TransactionExtractFinAdv NTF
left join dbo.vw_MIREPORTS_ListAllPanSignups vw on vw.Pan = NTF.Pan
Where NTF.cortexDate >= ''2011-10-18''
AND NTF.cortexDate <=''2011-10-18''
AND vw.accesscode in (Select naccesscode from PrepaidCardholder where EPP_ClientID = ''D0273808-345D-45AC-957A-2F5E9B04246D'')
Order By NTF.localDate DESC
FOR XML PATH (''WE''),
ROOT(''WES'')" queryout
"C:\SQL\SampleXMLOutput.xml" -c -T -x'

The file is not created. Once i run this query I get the output as

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

Kindly let me know the error.

Thanks,
Sandesh

Sachin.Nand

2937 Posts

Posted - 2011-10-27 : 11:10:38

The servername is missing in the bcp parameters.

PBUH

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-27 : 11:31:21

I tried with server name but no luck.. same error.

EXEC master..xp_cmdshell
'bcp "Select Left(NTF.AccNo,6) as programid, convert(char(10), NTF.localdate, 103) as localdate, NTF.localtime, NTF.tlogid, NTF.ItemID,NTF.pan as pan, NTF.billamt, NTF.approvalcode, Convert(char(10),NTF.cortexDate,103) as CortexDate, NTF.termcode,NTF.termlocation,NTF.termstreet, NTF.termcity, NTF.termcountry, NTF.mcc,vw.Cardholderid, vw.CardProduct,vw.PromoCode, NTF.txnCode
From Nomad_TransactionExtractFinAdv NTF
left join dbo.vw_MIREPORTS_ListAllPanSignups vw on vw.Pan = NTF.Pan
Where NTF.cortexDate >= ''2011-10-18''
AND NTF.cortexDate <=''2011-10-18''
AND vw.accesscode in (Select naccesscode from PrepaidCardholder where EPP_ClientID = ''D0273808-345D-45AC-957A-2F5E9B04246D'')
Order By NTF.localDate DESC
FOR XML PATH (''''),
ROOT(''WES'')" queryout
"C:\SQL\SampleXMLOutput.xml" -w -T -x -S 380514-DB2\EPPLIVE'

Thanks,
Sandesh

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-27 : 11:39:11

Ok you can use the below method using VS

SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from inventaris", vconn);
DataTable table = new DataTable();

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);

string XMLpad = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
XMLpad += @"\voorraad.xml";

StreamWriter sw = new StreamWriter(XMLpad);
table.WriteXml(sw);
sw.Close();
i hope it useful to you

paul Tech

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources