Post reply

Beginner help help needed to provide XML format output

  • March 23, 2006 at 4:44 pm

    #89902

    Hi, I'm in dire need of xml help. I relatively new to sql server and xml. I have a requirement for output to be in xml. I had a developer give me a basic look:

    If this is the case, then you could probably output the XML like this:

    <OUTPUT reportname=”LC WEEKLY” startDate=”3/1/2006” endDate=”3/8/2006”>

                <FIELD1>value</FIELD1>

                <FIELD2>value</FIELD2>

                <FIELD3>value</FIELD3>

                <FIELD4>value</FIELD4>

    </OUTPUT>

     I understand that sql server uses the "FOR XML" feature. Example, I used this query...

    select col1, col2, col3, col4 from table 

    for XML AUTO

    How do I write a stored procedure for this? Is this a correct approach? Is there anything else I need to do to fit the xml into this suggested one? Does it matter?

     

  • March 24, 2006 at 1:26 am

    #628522

    melster,

    I'm not sure what you're asking but, if you want a very specific output schema, as you mention above, I think you will probably have to use FOR XML with the EXPLICIT clause to get fields like reportname and startDate as attributes and the FIELDn list as elements.

    It might be easier if you post the database table structure that you are querying and also an example file that you want to generate (unless the above sample is it). I should then be able to give you an example of the SQL that you can use, either direct or in a stored procedure, to generate the XML you need.

    Dan
    www.firstcs.co.uk

  • March 24, 2006 at 3:46 am

    #628539

    do a search in the forums, this xml issue has been covered extensively before.

    Everything you can imagine is real.

  • March 27, 2006 at 10:44 am

    #628978

    Let's just say, I need to return (2) columns in XML format. Like this query. How can I put this in a stored proc to return xml format. Also, if I need to input the esrw.stationID dynamically. Meaning, stationID will always be different and I want to add that as a parameter. Can you help me get started on the tsql for this? Thanks again.

    select

     esrw.stationID,

     esrw.trackID

    from 

     table esrw

    for XML AUTO

  • March 27, 2006 at 3:12 pm

    #629032

    create your stored proc the normal way and then just return the data as xml.

    create proc cp_stationData

    @staionid int

    as

    select esrw.stationID,esrw.trackID

    from esrw

    where esrw.stationID = @staionid

    for XML AUTO

    on the client side remember your data is returned as xml, therefore read it as such.

    Everything you can imagine is real.

  • March 27, 2006 at 3:14 pm

    #629033

    goto to this post, hope it will help you

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=21&messageid=194568

    Everything you can imagine is real.

  • March 30, 2006 at 1:29 pm

    #629970

    I was able to produce an XML file using FOR XML EXPLICIT. Now my problem is producing the output without:

    a) header - XML_F52E2B61-18A1-11d1-B105-00805F49916B

    b) rows affected - (658 row(s) affected)

    c) carriage returns ^M seen in the editor

    I can do this manually by editing the script and removing it. Is there a way to produce the xml output to a file without this extra data? I can't seem to find it in the tools menu of sql server.

    Thanks!

     

  • April 30, 2007 at 4:30 pm

    #703447

    hi Melster,

    I am having the same problem. Have you found out how to clear the unwanted data and how to include the header? Please advise. Thanks.

     

     

     

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

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