FOR XML Query

Question

Post reply

FOR XML Query

DB Dude

Old Hand

Points: 364
More actions
June 21, 2011 at 9:53 am

#240243

Hi Folks,
Need some quick help here. I have a query that returns a single column. I need to present it in the form of the following XML format:
<fields><field>asdf</field><field>qwer</field><fields>
Straight forward, but I cannot get the right combination to create this output.
Thanks in advance!!

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

There is probably another way to do this but I have always done that like this.

select '<fields>' +

(

select [Your Field] as field from [Your Table] for xml path ('')

)

+ '</fields>'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DB Dude Old Hand Points: 364 More actions · Answer 2

DB Dude

Old Hand

Points: 364

June 21, 2011 at 10:20 am

#1341938

Thank you very much! That will work just fine.

winash SSChampion Points: 11446 More actions · Answer 3

Here's another way:

SELECT name as field FROM sys.tables FOR XML PATH(''),ROOT('fields');

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

A second way is to simply specify the root. Stealing form Sean's example:

select [Your Field] as field from [Your Table] for xml path (''), root('fields')

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Thanks Matt. I knew I had seen a way to do that somewhere in my past but couldn't think of it for the life of me.