SQL View as XML then create output file

Question

Post reply

SQL View as XML then create output file

SteveEClarke

SSCertifiable

Points: 5161
More actions
March 30, 2015 at 3:49 am

#299091

Hi All,
The Goal
I need to create an XML document from a SELECT and store that in a folder on the network.
I have a basic select statement, works fine.
I Then add at the bottom - "FOR XML PATH(''), ROOT('SALES')" - which again works fine when I execute.
If I create a view ( without the XML statement ) - that works fine and creates a view.
If I add the "FOR XML PATH(''), ROOT('SALES')" at the end and execute the "ALTER VIEW", I get the following message.
Msg 4511, Level 16, State 1, Procedure View_SLG_FinanceForecast_ActualSales_XML, Line 5
Create View or Function failed because no column name was specified for column 1.
Should I create the view as XML format - or should I use a stored procedure to drop the output out as XML via BCP ?
Any help would be appreciated.
Thanks
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You need to give an alias to the whole XML document you're selecting:

SELECT 'A'

FOR XML PATH('')

outputs this:

XML_F52E2B61-18A1-11d1-B105-00805F49916B

-----------------------------------------

A

(1 row(s) affected)

With an alias you get this:

SELECT theDoc = (

SELECT 'A'

FOR XML PATH('')

)

outputs this:

theDoc

-------------------

A

(1 row(s) affected)

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

SteveEClarke (3/30/2015)
Hi All,
Should I create the view as XML format - or should I use a stored procedure to drop the output out as XML via BCP ?

The latter seems much more sensible to me. You can use a stored procedure or query directly from the application using FOR XML.

-- Gianluca Sartori

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 3

Thanks for the help.

The views now work a treat -

What would be the best solution for generating an XML file in a specific folder, calling a stored procedure to run every hour ?

Thanks

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

It really depends what you're after. If you want files in a folder every x minutes, you can use a powershell job.

-- Gianluca Sartori

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 5

That will be what I am after - I think I have that bit all sorted now.

Just one more question -

With Regard to the XML format, Is it possible to have Child Nodes as well as the root node ?

So ;

<?xml version="1.0"?>

</SalesData>

Would look like this;

<?xml version="1.0"?>

</SalesDataItem >

</SalesData>

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 6

Sorry - have answered this one now.

I am struggling getting the output to work within "BCP" - keeps asking for a format file !?

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

Sorry for the late reply. Which command line parameters are you using for BCP?

-- Gianluca Sartori

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 8

I think I am getting there - having figured out the bcp command line now.

So I have created an xml format file and specified that when creating the data file. Once created - I try to load this within and XML Editor and it states that the first line is incorrect;

The XML page cannot be displayed

Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.

--------------------------------------------------------------------------------

A semi colon character was expected. Error processing resource 'file://XMLExport/StockCodes.xml'.

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 9

Apologies -

Command line;

Create format file;

bcp [SysproCompanyS].[dbo].[View_SLG_FinanceForecast_StockCodes] format nul -n -f C:\XMLExport\StockCodeFormat.xml -x -T

Create data file;

bcp [SysproCompanyS].[dbo].[View_SLG_FinanceForecast_StockCodes] out c:\xmlexport\StockCodes.xml -f C:\XMLExport\StockCodeFormat.xml -T

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

SteveEClarke (3/31/2015)
Apologies -
Command line;
Create format file;
bcp [SysproCompanyS].[dbo].[View_SLG_FinanceForecast_StockCodes] format nul -n -f C:\XMLExport\StockCodeFormat.xml -x -T
Create data file;
bcp [SysproCompanyS].[dbo].[View_SLG_FinanceForecast_StockCodes] out c:\xmlexport\StockCodes.xml -f C:\XMLExport\StockCodeFormat.xml -T -c

Try with character type format:

bcp [SysproCompanyS].[dbo].[View_SLG_FinanceForecast_StockCodes] out c:\xmlexport\StockCodes.xml -f C:\XMLExport\StockCodeFormat.xml -T -c

-- Gianluca Sartori