SQL Query to create XML Elements

I recently had to create an XML file from a Microsoft SQL Server table. I was starting like normal to get my query results and then had to loop thru them in order to assign the data to the XML elements.

Since this table had 150+ fields, it was going to be quite literally a data entry nightmare I didn’t want to face so I figured that I would write a query to loop thru all of the database columns and then append the data needed to make my XML elements.

This query worked like a charm and took a couple minutes to create.

SELECT 'xWriter.WriteElementString("' + name + '", item.' + name + ');'
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.YOUR_TABLE_NAME')

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.