If you use the FOR XML syntax in SQL Server it can be a little tricky to get the XML formatted the way you really want it.

For every table that you have in a join it becomes a child of the table before. What if you have a 3 table join and you want one table to be the parent and the two tables that you are joining to be on the same level, I will show you how you can accomplish that.

First let's create some tables and insert some data

You have a table and you would like to create a XSD schema based on that table. What is the easiest way to do that in SQL Server? The easiest way to do that would be to use FOR XML syntax with AUTO, ELEMENTS and XMLSCHEMA. If your table is named test and you want your schema to be named TestXsdSchema then you would do the following

A couple of days ago onpnt posted the following blogpost: Import directory of XML files into SQL Server 2005

In that post he was using SQLCLR to import a bunch of files. Some of you might not be so familiar with .NET so I am providing a T-SQL way to do something similar

You will need to create a directory testxml on the c drive and put a bunch of XML files in there. If you don't have any XML files then save the following two as file1.xml and file2.xml

...

So you want to spit out some XML from SQL Server into a file, how can you do that? There are a couple of ways, I will show you how you can do it with SSIS. In the SSIS package you need an Execute SQL Task and a Script Task.

Let's get started

First create and populate these two tables in your database