Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I'm parsing with flash a XML file generated by this code:

:XML ON  
USE MyDatabaseName 
GO 
SET NOCOUNT ON 

SELECT * FROM ProgramacionDia as programa order by hora 
FOR XML AUTO,
ROOT ('toflash'), 
ELEMENTS 

SET NOCOUNT OFF

But I get a XML file with the first line empty. Removing this first empty line in the generated XML works ok with flash, but with the generated XML no.

How can I remove that line? Is my script wrong? I have no much idea about this code.

I'm running SQL Server 9.0.

share|improve this question

1 Answer

up vote 3 down vote

After a bit of trial and error, I found this is caused by the USE statement occuring after XML mode is turned on. I have no idea why; I assume it's simply an artifact of sqlcmd processing.

Here are three solutions I came up with from playing around with the code:

  1. Remove the USE completely, and specify the database name on the command line using the -d parameter instead.

    :XML ON  

SET NOCOUNT ON 

SELECT * FROM ProgramacionDia as programa order by hora 
    FOR XML AUTO, ROOT ('toflash'), ELEMENTS 

SET NOCOUNT OFF

  2. Remove the USE completely, and three-part-name the table.

    :XML ON  

SET NOCOUNT ON 

SELECT * FROM [MyDatabaseName]..[ProgramacionDia] as programa order by hora 
    FOR XML AUTO, ROOT ('toflash'), ELEMENTS 

SET NOCOUNT OFF

  3. Move the USE statement above the :XML ON directive, and delete the GO (this will suppress the "Changed database context to 'MyDatabaseName'" message in the output). I have no idea why this also fixes the issue.

    USE MyDatabaseName

:XML ON  

SET NOCOUNT ON 

SELECT * FROM ProgramacionDia as programa order by hora 
    FOR XML AUTO, ROOT ('toflash'), ELEMENTS 

SET NOCOUNT OFF

Depending on what your actual script looks like, one of these options should be adaptable for you.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.