I have a project with a looming deadline that requires me to extract data from a number of related SQL tables into an XML file. I need precise control over the format of the XML file to meet the project's requirements and I think the best way will be to use the FOR XML PATH tag. As this is my first time I would greatly appreciate some expert assistance to kick start the process.
My example below is a greatly simplified form of a project to extract insurance document records from SQL Server 2008 R2 to XML. There are many more tables and columns to extract in the live project but if I can get my head around this fragment I should be able to build rapidly on this knowledge.
Firstly, I have three related tables as below. The relationship between Policies and Drivers is one-to-many and the relationship between Drivers and Medical_Conditions is one-to-many. Every policy must have at least one driver but each driver may have 0, 1 or more medical conditions.
I need my XML to look like this:
<?xml version="1.0" encoding="utf-8"?>
<Driving_Restriction restriction_type="Named Drivers Only">
<type_of_condition>Diabetes Type 1</type_of_condition>
As you can see, the XML file is element centric but does also use attributes. Can some expert please provide the SQL SELECT statement that will result in the above XML output?
Many thanks in advance.