SQL Server 2005 Stored Procedure XML Output

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

Transact-SQL (2005)

SQL Server 2005 Stored Procedure XML Output

Author

Topic

LittlBUGer
Starting Member

19 Posts

Posted - 2009-03-13 : 18:13:55

Hello. Essentially, I'm trying to find a way to run a stored procedure in SQL Server 2005 once per day. What it will do is generate specific XML and need to (hopefully) export that to a file (one file per day, if data exists). I've gotten the stored procedure working exactly as I want it to and it's currently saving the correctly formatted XML into a table with an xml data type. Thus if I just run a SELECT query in Management Studio on that column, the results are a link which once I click on opens a new tab with the properly formatted XML, just as I want.

The problem is, how do I get this out into an xml file on the server somewhere automatically, either using the same stored procedure or some other method? I've tried testing the bcp.exe option running something like:

bcp "SELECT theXML FROM DB.dbo.Table FOR XML RAW" queryout "C:\temp\sql\Test1.xml" -S"server" -U"user" -P"pass" -c -r -t

But the resulting xml file not only adds two new 'root' sections (which is unnecessary as the XML in the database is already exactly how I need it), but it's all on a single line, instead of properly formatted XML. Am I missing something, is there an easier way, or what? Thanks for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 18:19:10

Why FOR XML RAW?
The data is already wellformed...

E 12°55'05.63"
N 56°04'39.26"

LittlBUGer
Starting Member

19 Posts

Posted - 2009-03-13 : 18:49:07

I only used FOR XML RAW as it was something I noticed that someone else had used doing a very similar thing as I needed. Is there a better way then? If so, can you please explain using examples with detail? Thanks!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-16 : 09:55:28

ok show us your data structure if you can and the resulting XML output you want or expect to see. you haev many different options for the FOR XML clause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 10:02:48

The data is already wellformed and have all the information you need.
Change your BCP command and remove the FOR XML RAW part.

CREATE TABLE	tblPeso
		(
			Data XML
		)

INSERT	tblPeso
SELECT	'<root><saints><saint nick="peso">Peter Larsson</saint></saints></root>'

SELECT	*
FROM	tblPeso

-- Error
SELECT Data FROM tblPeso FOR XML RAW

-- Correct
SELECT Data FROM tblPeso

DECLARE	@cmd VARCHAR(1000)

SET	@cmd = 'BCP "SELECT Data FROM Test..tblPeso FOR XML RAW" queryout "C:\Test1.xml" -S phbgpela\sql2008 -T -c -r "" -t ""'
EXEC	master..xp_cmdshell @cmd

SET	@cmd = 'BCP "SELECT Data FROM Test..tblPeso" queryout "C:\Test2.xml" -S phbgpela\sql2008 -T -c -r "" -t ""'
EXEC	master..xp_cmdshell @cmd

DROP TABLE	tblPeso

Content of file 1 is

- <row>    <- extra row
 <Data>  <- extra row
 <root>
 <saints>
  <saint nick="peso">Peter Larsson</saint> 
  </saints>
  </root>
  </Data>  <- extra row
  </row>  <- extra row

and content of file 2 is

<root>
<saints>
  <saint nick="peso">Peter Larsson</saint> 
  </saints>
  </root>

You really shouldn't other people's code without trying to understand it.

E 12°55'05.63"
N 56°04'39.26"

LittlBUGer
Starting Member

19 Posts

Posted - 2009-03-16 : 10:26:53

@Peso
You were right, taking out the FOR XML RAW seemed to mostly fix it. Now it's not adding any extra elements or anything. But the problem where everything is on a single line (instead of multi-lines like regular XML should be) persists. How do I get around this? Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 10:41:06

What?

XML doesn't know of linefeeds between elements.
That is not necessary for XML. That is for human interpretation and readability only.

E 12°55'05.63"
N 56°04'39.26"

LittlBUGer
Starting Member

19 Posts

Posted - 2009-03-16 : 11:01:11

Well, the reason is, I'm creating this XML for a different program to use. I've been given sample XML that this program uses fine and when I open it with, say, TextPad, it's properly formatted on multiple lines. So I thought I would need to mimic that with the XML I create from SQL. I guess I'll just have to test later when I can and see. Thanks for your help. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources