XML From the Database

If you have data in your database tables/columns, you can extract and format it in XML format. There are three main ways to do this in SQL Server:

1. FOR XML RAW
2. FOR XML AUTO
3. FOR XML PATH

When you use FOR XML RAW, the rows in the source become elements in the XML output. You columns become attributes in the output.

When you use FOR XML AUTO, the source table and columns become elements in the output. The column order determines the nesting of elements in the output.

When you use FOR XML PATH, you use XPath to define the elements and attributes in the output.

If you need to go the other way around, and insert data into the database from an XML document source, then you will be doing "shredding". You make use of the OPENXML function. You will use the DOM to reference data. The translation from DOM nodes to database rows is defined using XPath.