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')

Change my connectionstring for debug and release config

This is part of a multi page blog post where I talk about what I do after I setup my Visual Studio ASP.NET MVC (non-Core) project.

To go back to the previous posts or start at the beginning, please go to the links below:

  1. How I Setup my Visual Studio Website Project Asp.Net MVC (non-Core)

At this point I don’t like to use the LocalDb for my database but SKIP THIS STEP if you’re OK using the LocalDb provided.

In my Web.Config file, find the section which should be on top and change the entry from this:

    <connectionStrings>
        <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-vsStarterKit.mdf;Initial Catalog=aspnet-vsStarterKit-20170302095959;Integrated Security=True" providerName="System.Data.SqlClient" />
    </connectionStrings>

to this:

    <connectionStrings>
        <add name="DefaultConnection" connectionString="Data Source=(local);Initial Catalog=YOUR_DB;Integrated Security=True" providerName="System.Data.SqlClient" />
    </connectionStrings>

At the same time, I also change my Web.Release.config connection string to point to my live database when I publish.

On publish, it will swap the connection string so one less step.

<?xml version="1.0"?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
    <connectionStrings>
        <add name="DefaultConnection" connectionString="Data Source=(local),1533;Initial Catalog=YOUR_DB_NAME;Persist Security Info=True;User ID=YOUR_LOGIN_ID;Password=YOUR_PWD;MultipleActiveResultSets=True" xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
    </connectionStrings>
    <system.web>
        <compilation xdt:Transform="RemoveAttributes(debug)" />
    </system.web>
</configuration>

Step #3 – Coming Soon