Beginner help help needed to provide XML format output

Question

Beginner help help needed to provide XML format output

melster

SSC Journeyman

Points: 75
More actions
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?

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

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

DanKennedy Hall of Fame Points: 3736 More actions · Answer 1

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

bledu SSChampion Points: 12335 More actions · Answer 2

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

Everything you can imagine is real.

melster SSC Journeyman Points: 75 More actions · Answer 3

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

bledu SSChampion Points: 12335 More actions · Answer 4

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.

bledu SSChampion Points: 12335 More actions · Answer 5

goto to this post, hope it will help you

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

Everything you can imagine is real.

melster SSC Journeyman Points: 75 More actions · Answer 6

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!

pcq0125 SSCrazy Points: 2890 More actions · Answer 7

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.