SQL Query output to XML? 1

[gbaughma]

Is there a simple way in a stored procedure to output to an XML File?

Something along the lines of "SELECT * FROM salestable OUTPUT AS XML C:\sales\SomeXMLFile.xml"

(I know that syntax is not correct, but you get the gist of it....)

... then of course, I'd be calling an external script to process the XML file into our accounting system....



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

The syntax is FOR XML.

Select *
From   Table
For XML Auto

Read more here:

http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

I'm going to play with that. Thank you, George.

I owe you SO MUCH beer. lol



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

I owe you SO MUCH beer.

I don't drink beer. Can you make that root beer?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Root beer is good. Especially with some ice cream in it.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

Hmmm.... so I got this working.... and if I generate the XML, and open it in SSMS, it looks fine. But if I open it in, say, Notepad, it looks funky. And it won't open as a regular XML file in IE, etc.

Here's the code (for posterity) for anyone else wondering how to do it...

Part 1: The stored procedure to generate the XML...

USE [DotNetNuke]
GO
/****** Object:  StoredProcedure [dbo].[proc_GetNewCustomers]    Script Date: 07/09/2010 14:12:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_GetNewCustomers]
AS
SELECT (LEFT([Zip],3) + LEFT([FirstName],1) + UPPER(Left([LastName],2))) AS [CustomerNo],
UPPER([FirstName])+' '+ UPPER([LastName]) AS CustomerName,
[Address1] AS AddressLine1, 
COALESCE([Address2],'') AS AddressLine2, 
'' as AddressLine3,
[City], 
[State], 
[Zip] as ZipCode,
[WorkPhone] as TelephoneNo,
[Email] AS EmailAddress 
From mytable 
WHERE [Mas90CustID] IS NULL 
FOR XML RAW('Customer'), ROOT('Doc'), TYPE

... and the stored proc that calls the other stored proc and generates a dated file...

declare @cmd varchar(1000)
declare @outfile varchar(50) 
select @outfile = 'e:\OrdersOutput\Cust' + CAST(CONVERT(Date,GETDATE()) AS VarChar(30)) + '.xml'
select @cmd = 'osql -Umyuserid -Pmypassword -SWEBSERVER2010\SQLEXPRESS -Q"DotNetNuke.dbo.proc_GetNewCustomers" -o"' + @outfile + '" -w500'
exec master..xp_cmdshell @cmd

... and as I said, it's working fine in SSMS, but if I open the file, has about a million blanks, then about a million dashes, *THEN* the XML... it's not "formatted" properly... and it says "x rows affected" at the bottom (which I need to turn off as well)

... but I'm on the right track anyway.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

1. Put [!]SET NOCOUNT ON[/!] in your procedure.

2. "about a million blanks, then about a million dashes" osql is putting that in there. Try putting -h in the command line for osql. I think this will remove the (column) headers.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

((Ships a KEG of Root Beer to George))

That is *almost* Perfect...

Now I just have to figure out why there are no CRLF's in my generated XML file... it's all run together into one huge line, which of course is barfing IE.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

one big long line shouldn't cause a problem. CRLF's are only for the benefit of humans.

Does it load ok in XML Notepad?

http://www.microsoft.com/downlOAds/...49-787D-4118-BA5F-4F30FE913628&displaylang=en

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

No... the width is breaking the closing tags in the XML.

... for example, "The 'CustomerName' start tag on line 1 does not match the end tag of 'Cus'

... because it's wrapping the lines... so it starts with

<CustomerName> bla bla bla </cus
tomerName>

... it's PUTTING a CRLF at the end of the line, which is breaking the tags.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

OK.... I just set -w 65535 in the osql... assuming that I never have a record that's bigger than 64K (which I shouldn't ever) that will fix it. NOW it opens with XML Notepad.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

I'm glad you worked it out.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom