exporting to xml with sqlexpress

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 to xml with sqlexpress

Author

Topic

rafael.soteldo
Starting Member

36 Posts

Posted - 2012-10-05 : 14:52:01

I'm using Sql Server Management Studio 2008 for SqlExpress,

does it have a way to export data to a file in xml format?

Thanks in advance for your time

-------
Rafael Soteldo

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-05 : 15:10:08

Yes, you can use the FOR XML clause of a SELECT statement to format the output as XML, then use the bcp utility to create a file:

bcp "select top 10 * from master..spt_values val for xml auto, root('vals')" queryout xml.txt -Sserver -T -w

That query will generate the following XML:

<vals>
<val name="rpc" number="1" type="A  " status="0"/>
<val name="pub" number="2" type="A  " status="0"/>
<val name="sub" number="4" type="A  " status="0"/>
<val name="dist" number="8" type="A  " status="0"/>
<val name="dpub" number="16" type="A  " status="0"/>
<val name="rpc out" number="64" type="A  " status="0"/>
<val name="data access" number="128" type="A  " status="0"/>
<val name="collation compatible" number="256" type="A  " status="0"/>
<val name="system" number="512" type="A  " status="0"/>
<val name="use remote collation" number="1024" type="A  " status="0"/>
</vals>

I'm pretty sure the bcp utility is installed with SQL Express, if not you can download it separately. Be sure to use the -w parameter to save the file as Unicode rather than ANSI text, XML is a Unicode format.

rafael.soteldo
Starting Member

36 Posts

Posted - 2012-10-05 : 20:28:19

Thank you

I'll try it

Thanks in advance for your time

-------
Rafael Soteldo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources