Post reply

FOR XML returning multiple rows in the result set

  • July 19, 2004 at 8:25 am

    #66517

    I'm using SELECT ... FOR XML AUTO in QA to return a result set, and saving this to a .XML file (then adding the root node tag).  When viewing in IE, I get an error because the result set is split over multiple lines, and a keyword has been cut in the middle.

    Could anyone suggest an elegant method for solving this problem?  Any input would be much appreciated!

    The query I am using in this example is:

    SELECT [name], [id], xtype FROM sysobjects FOR XML AUTO

    When in doubt - test, test, test!

    Wayne

  • July 19, 2004 at 9:20 am

    #515084

    Try this:

    master..xp_cmdshell 'bcp "SELECT [name], [id], xtype FROM sysobjects FOR XML AUTO" queryout "C:\bcptest.rpt" -T -c'

    /rockmoose


    You must unlearn what You have learnt

  • July 19, 2004 at 9:46 am

    #515089

    I'm afraid cmdshell results in the same problem.  The 'chopping off' is an ODBC limitation.

    When in doubt - test, test, test!

    Wayne

  • July 19, 2004 at 10:34 am

    #515097

    Would something like this help...

    select 'xmlroot>' as Xml_Data

    union all

    select

    'name>'+ltrim(name)+'/name>'+

    'id>'+ltrim(id)+'/id>'

    -- .........

    from dbo.sysobjects

    union all

    select '/xmlroot>' as Xml_Data

    The tags get messed up.. but you get the idea.

    /rockmoose


    You must unlearn what You have learnt

  • July 20, 2004 at 1:31 am

    #515169

    That's what we have currently, and that is what my boss wants to move away from - i.e. hard-coding the tags.  He is looking for something generic where he can pass any query string (without stringing the tags into the query), and out pops an XML file.

     

     

    When in doubt - test, test, test!

    Wayne

Viewing 5 posts - 1 through 4 (of 4 total)

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