Post reply

help with using FOR XML

  • August 9, 2005 at 7:59 am

    #165211

    I'm a complete beginner when it comes to XML and am stuck when using FOR XML.

    When using FOR XML, I find that the results contain carriage returns in the middle of words so if I cut and paste the results into a file I find myself having to re-format the output and remove/add carriage returns.

    For example, if I run the following statement:

    select customerid from northwind.dbo.orders for xml auto, elements

    I get the following results:

    <orders><customerId>VINET</customerId></orders>...<cus

    tomerId>QUEDE...

    I've trimmed the first line but the important thing to note is that the first line ends half way through the <customerId> tag.  So I have to manually re-format that ouput and place a carriage return just before that tag.  For large outputs this becomes a bit tedious.

    Ultimately I'd like to output the results to an xml file - either using osql or dts but until I get past this problem there doesn't seem much point.

    I'm sure I'm being completely stupid and there's an easy answer but I can't figure it out so any help/advice is appreciated.

    thanks

    Karl
    source control for SQL Server

  • August 9, 2005 at 1:50 pm

    #580846

    Hi grambowk, try this code:

    declare @cmd nvarchar(1000)

    set @cmd = 'bcp "select CustomerID from Northwind.dbo.Orders as Orders for xml auto, elements" queryout C:\test.xml -S'+@@servername+' -C ACP -T -w -r -t'

    exec master..xp_cmdshell @cmd, no_output

  • August 10, 2005 at 2:50 am

    #580939

    Thanks a lot.

    That works perfectly.

    Karl
    source control for SQL Server

  • August 10, 2005 at 6:26 am

    #580971

    For info - this is a problem with the "query analyser" front end as it splits up the string back from SQL Server - now if you avail yourself of the 2005 beta and use the new "SQL Server management studio" client app it returns the XML really nicely - if you double click on the XML in the "grid" output it opens it up as a "pretty printed" XML doc

    James Horsley
    Workflow Consulting Limited

  • August 10, 2005 at 7:55 am

    #581030

    Hi 'Grasshopper', can you give more information on what the command is doing here or point to some resources that explain this?

    thanks

  • August 10, 2005 at 10:00 am

    #581102

    Tell me about it.

    We've got a SQL Server 2005 testing environment and I've already being using that for this same XML solution.  The problem is that after using it in SQL 2005 I don't want to go back to SQL 2000.  One more reason, amongst a host of others, that I'm trying to convince the management to upgrade the environment to SQL 2005.

    Karl
    source control for SQL Server

  • August 10, 2005 at 10:04 am

    #581105

    Nanda,

    what part are you interested in knowing more about?

    bcp is a command line utility that enables you to import or export sql server data to/from text files (or in the above case, an xml file).

    The command above, selects the given columns from the northwind.dbo.orders table (using the FOR XML output), and outputs the results to a file test.xml.

    'bcp "select CustomerID from Northwind.dbo.Orders as Orders for xml auto, elements" queryout C:\test.xml

    xp_cmdshell is a sql server extended procedure that allows you to execute command line operations (in the above case, we're running the bcp command).

    Both of these commands are in books online and are pretty well documented.

    If you want to know what the other switches are just run "bcp /?" (without the quotes) from the command line prompt.

    Hope that helps.

    Karl
    source control for SQL Server

  • August 10, 2005 at 11:46 am

    #581131

    Tanks, Karl

    You save my 'Grasshopper' time!

    Vlad

Viewing 8 posts - 1 through 7 (of 7 total)

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