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 2005 Forums
 .NET Inside SQL Server (2005)
 Write SQL data to an XML file

Author  Topic 

TSKraemer
Starting Member

4 Posts

Posted - 2007-08-03 : 16:22:02
I need to write data returned by a stored proc to an XML file. The resultant file will then be consumed by an external application. The file has to adhere to a specific schema. Is there a way to do this through .NET CLR in SQL Serer 2005? Is SSIS a better option? Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-03 : 16:35:15
you can use BCP for that


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

TSKraemer
Starting Member

4 Posts

Posted - 2007-08-06 : 10:27:36
quote:
Originally posted by spirit1

you can use BCP for that

Thanks for the direction. I looked this up in books online but they don't provide an example of how to get the output file into XML format.

I did see that you can use an XML-formatted file to describe how you want your output to look, but the example output files are always in non-XML-format.

Can you provide a sample syntax? Thanks again.
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-06 : 10:32:51
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9336

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

TSKraemer
Starting Member

4 Posts

Posted - 2007-08-06 : 10:37:04
quote:
Originally posted by spirit1

you can use BCP for that

Here's an example of what I need:
For a given query like 'SELECT col1, col2 FROM myTable', I want to produce an output file called "myTableOutput.xml" that looks like this...
<myTable>
<Row col1="aval" col2="anotherVal" />
<Row col1="aval2" col2="anotherVal2" />
</myTable>

Thanks again.

____________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-06 : 10:39:35
have you read the link i gave you?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page
   

- Advertisement -