asked on

Controlling XML format with FOR XML PATH

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.

Policies
======
policy_id (PK)
driving_restriction

Drivers
======
driver_id (PK)
policy_id (FK)
title
first_name
surname
license_type
years_held

Medical_Conditions
================
condition_id (PK)
driver_id (FK)
type_of_condition
first_diagnosed
dvla_advised

I need my XML to look like this:

<?xml version="1.0" encoding="utf-8"?>
<Insurance_Doc>
<Driving_Restriction restriction_type="Named Drivers Only">
<Driver>
<title>Mr</title>
<first_name>Fred</first_name>
<surname>Bloggs</surname>
<license_type>Full UK</license_type>
<years_held>10+</years_held>
</Driver>
</Driving_Restriction>
<Medical_Condition>
<Driver>
<title>Mr</title>
<first_name>Fred</first_name>
<surname>Bloggs</surname>
<Condition_Detail>
<type_of_condition>Diabetes Type 1</type_of_condition>
<first_diagnosed>01/05/2001</first_diagnosed>
<dvla_advised>Yes</dvla_advised>
</Condition_Detail>
<Condition_Detail>
<type_of_condition>Heart Condition</type_of_condition>
<first_diagnosed>26/05/2005</first_diagnosed>
<dvla_advised>Yes</dvla_advised>
</Condition_Detail>
</Driver>
</Medical_Condition>
</Insurance_Doc>

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.

ASKER CERTIFIED SOLUTION

pivar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Due to the hierachical complexity of the Xml document, you will find that this is not trivial to accomplish using T-SQL alone, I suspect it can be done but you would have to resort to the more complicated FOR XML EXPLICIT clause. But you may be better off using something like .NET to accomplish the same.

irb56

ASKER

Thanks to both. I'm hoping to avoid FOR XML EXPLICIT as it's quite painful to construct and maintain. I've been told that FOR XML PATH avoids the need to use EXPLICIT and is more intuitive to use. My .NET skills aren't up to much so that isn't a quick win for me unfortunately.

Thanks for your T-SQL pivar. I have created and populated the tables I mentioned and adapted your code as follows:

declare @myXML xml
set @myXML = (
select '<?xml version="1.0" encoding="utf-8"?>'+replace(replace(convert(varchar(max),
(
select p.driving_restriction as "Driving_Restriction/@restriction_type",
d.title as "Driving_Restriction/Driver/title",
d.first_name as "Driving_Restriction/Driver/first_name",
d.surname as "Driving_Restriction/Driver/surname",
d.license_type as "Driving_Restriction/Driver/license_type",
d.years_held as "Driving_Restriction/Driver/years_held",
d.title as "Medical_Condition/Driver/title",
d.first_name as "Medical_Condition/Driver/first_name",
d.surname as "Medical_Condition/Driver/surname",
(select m.type_of_condition as "Condition_Detail/type_of_condition",
                         m.first_diagnosed as "Condition_Detail/first_diagnosed",
                         m.dvla_advised as "Condition_Detail/dvla_advised"
      from Medical_Conditions m
      where m.driver_id in (select driver_id from Drivers)
      for xml path('Medical_Condition')) as "Driver"
from Policies p
inner join Drivers d
      on p.policy_id = d.policy_id
for xml path(''), root('Insurance_Doc')
)
),'<','<'),'>','>'))

select @myXML

The resultant XML looks like this:

<Insurance_Doc>
<Driving_Restriction restriction_type="Named Drivers Only">
<Driver>
<title>Mr</title>
<first_name>Fred</first_name>
<surname>Bloggs</surname>
<license_type>Ful UK</license_type>
<years_held>10+</years_held>
</Driver>
</Driving_Restriction>
<Medical_Condition>
<Driver>
<title>Mr</title>
<first_name>Fred</first_name>
<surname>Bloggs</surname>
</Driver>
</Medical_Condition>
<Driver>
<Medical_Condition>
<Condition_Detail>
<type_of_condition>Diabetes Type 1</type_of_condition>
<first_diagnosed>2001-05-01T00:00:00</first_diagnosed>
<dvla_advised>Y</dvla_advised>
</Condition_Detail>
</Medical_Condition>
<Medical_Condition>
<Condition_Detail>
<type_of_condition>Heart Condition</type_of_condition>
<first_diagnosed>2005-05-26T00:00:00</first_diagnosed>
<dvla_advised>Y</dvla_advised>
</Condition_Detail>
</Medical_Condition>
</Driver>
</Insurance_Doc>

This is almost what's need but not quite. You can see that there are three <Medical_Condition> elements instead of two and the latter two are wrapped as children of <Driver>. Is it possible to change the T-SQL above to correct this issue and make the XML match that from my original post?

irb56

ASKER

After a bit of tinkering I found the answer in the following code. Many thanks pivar for your code which was very useful. I'll give you the points.

declare @myXML xml
set @myXML = (
select '<?xml version="1.0" encoding="utf-8"?>'+replace(replace(convert(varchar(max),
(
select p.driving_restriction as "Driving_Restriction/@restriction_type",
d.title as "Driving_Restriction/Driver/title",
d.first_name as "Driving_Restriction/Driver/first_name",
d.surname as "Driving_Restriction/Driver/surname",
d.license_type as "Driving_Restriction/Driver/license_type",
d.years_held as "Driving_Restriction/Driver/years_held",
(select d.title as "Medical_Condition/Driver/title",
d.first_name as "Medical_Condition/Driver/first_name",
d.surname as "Medical_Condition/Driver/surname",
m.type_of_condition as "Medical_Condition/Driver/Condition_Detail/type_of_condition",
m.first_diagnosed as "Medical_Condition/Driver/Condition_Detail/first_diagnosed",
m.dvla_advised as "Medical_Condition/Driver/Condition_Detail/dvla_advised"
from Medical_Conditions m
where m.driver_id in (select driver_id from Drivers) for xml path(''))
from Policies p
left join Drivers d
on p.policy_id = d.policy_id
for xml path(''), root('Insurance_Doc')
)
),'<','<'),'>','>'))

select @myXML

irb56

ASKER

A prompt and very useful response to a problem that I think was quite complex.