How do I create an xml/txt file from a SQL Query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How do I create an xml/txt file from a SQL Query?

Author

Topic

omro
Starting Member

1 Post

Posted - 2010-12-01 : 17:32:41

Hi there,

I'm new to SQL server, previously used MySQL queries and php, however I now have to take over an old SQL Server db and get some information out of it.

I have an SQL query which gives me a listing of active swipecards. The output is firstnane, lastname and cardnumber. I then need to create an XML file for each active swipecard containing the information in the database. So 31234.xml, 34567.xml, 56345.xml etc for each active swipecard.

The XML will contain some of the below, where the active swipecard is 31234 belonging to Joe Bloggs.

<?xml version="1.0" ?>
<Card Type="SC" Number="31234" OperationType="Update">
<Name>Joe</Name>
<Surname>Bloggs</Surname>
</Card>

If anyone could direct me in the right direction, I would really appreciate it!

How do I

a) create the content to output for each record returned?
b) output the content into an external file?

Thanks!

Sachin.Nand

2937 Posts

Posted - 2010-12-02 : 00:01:06

I am not to sure on how to create a XML file for each record.
I think it will be much more feasible to do it with any application language.The below query will create a new column with xml values in it.


declare @t table(Number int,Name varchar(30),Surname varchar(30),Type varchar(20),OperationType varchar(20))
insert @t
select 31234,'Joe','Bloggs','SC','Update' union 
select 31235,'Joe1','Bloggs1','ST','Insert'
select * from @t

select Number,
convert(xml,(select Type as 'Card/@Type',Number as 'Card/@Number',OperationType as 'Card/@OperationType',
Name,Surname from @t t1 where t1.Number=t2.Number for xml path(''),root('Card')))XmlCol from @t t2

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources