sql query to produce xml output

Question

I have these tables

Table 1 tbl1_site [facilityId] [name]

Table 2 tbl2_applicant [pvid] [facilityId] [npi] [firstname]

FK join key: tbl1_site.facilityId = tbl2_applicant.facilityId

Table 3 tbl3_abstraction [pvid] [patientnumber] [diabetesdiagnosis] [dateofbirth]

FK join key: tbl2_applicant.pvId = tbl3_abstraction.pvId

i have problem to create a sql query to reproduce this xml output.

thanks

<account>
    <metadata />
    <practice-sites>
        <practice-site>
            <metadata>
                <data-element id="name">
                    <value>My Own Diabetes Medical Center</value>
                </data-element>
            </metadata>
            <applicants>
                <metadata />
                <applicant>
                    <metadata>
                        <data-element id="npi">
                            <value>1234567890</value>
                        </data-element>
                        <data-element id="firstname">
                            <value>Joseph</value>
                        </data-element>
                    </metadata>
                    <clinical-abstractions>                           
                        <clinical-abstraction>
                            <data-element id="diabetesdiagnosis">
                                <value>Backward</value>
                            </data-element>
                            <data-element id="dateofbirth">
                                <value>02/01/2009</value>
                            </data-element>
                            <data-element id="patientnumber">
                                <value>1</value>
                            </data-element>
                        </clinical-abstraction>
                    </clinical-abstractions>
                </applicant>
            </applicants>
        </practice-site>
    </practice-sites>
</account>

Answer 1

do you really need all those tags? I mean the "metadata" and "data-element"

try this query, it shows the data on the format you need:

select  t1.name as PracticeSite,
        (SELECT t2.npi as NPI, 
                t2.firstname,
                (SELECT t3.patientnumber, 
                        t3.diabetesdiagnosis, 
                        t3.dateofbirth
                        FROM tbl3_abstraction t3
                        WHERE t3.pvId=t2.pvId
                        FOR XML PATH('clinical-abstraction'), TYPE
                        ) as 'clinical-abstractions'
                FROM tbl2_applicant t2
                WHERE t1.[facilityId]=t2.[facilityId]
                FOR XML PATH('Applicant'), TYPE
        ) AS 'Applicants'
from tbl1_site t1
FOR XML path('PracticeSites'), root('account'), ELEMENTS;

Answer 2

I think the simplest thing to do would be craft your SQL statement using "SELECT [...] FROM whatever AS XML" to get the data as the default SQL Server generated XML. Once you have that, transform it to your required XML format using XSLT. This could be done via a SQL Server function or a function in your .NET application.