SQL View as XML then create output file

  • SteveEClarke

    SSCertifiable

    Points: 5161

    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.

  • spaghettidba

    SSC Guru

    Points: 105673

    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)

  • spaghettidba

    SSC Guru

    Points: 105673

    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.

  • SteveEClarke

    SSCertifiable

    Points: 5161

    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: 105673

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

  • SteveEClarke

    SSCertifiable

    Points: 5161

    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>

    <Customer>NOV001 </Customer>

    <Branch>ME</Branch>

    <Year>2014</Year>

    <Month>1</Month>

    <NetSalesValue>2419.20</NetSalesValue>

    <Customer>BOO001 </Customer>

    <Branch>RH</Branch>

    <Year>2014</Year>

    <Month>1</Month>

    <NetSalesValue>2419.20</NetSalesValue>

    </SalesData>

    Would look like this;

    <?xml version="1.0"?>

    <SalesData>

    <SalesDataItem>

    <Customer>NOV001 </Customer>

    <Branch>ME</Branch>

    <Year>2014</Year>

    <Month>1</Month>

    <NetSalesValue>2419.20</NetSalesValue>

    </SalesDataItem >

    <SalesDataItem>

    <Customer>BOO001 </Customer>

    <Branch>RH</Branch>

    <Year>2014</Year>

    <Month>1</Month>

    <NetSalesValue>2419.20</NetSalesValue>

    <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

    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: 105673

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

  • SteveEClarke

    SSCertifiable

    Points: 5161

    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

    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: 105673

    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

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

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