My query to build XML includes subqueries. The child records are supposed to go into a container element. The XML must have the container element even if there are no child records.
Consider the following. The <Patients>
container element must exist even if there are no child records or validation fails.
<Hospital>
<Patients>
<Patient></Patient>
<Patient></Patient>
<Patient></Patient>
</Patients>
</Hospital>
I've tried a couple of different approaches using FOR XML PATH
:
select
(
select
PatientFields
from
PatientTable
for xml path('Patient'), root('Patients'), elements xsinil, type
)
for xml path('Hospital'), elements xsinil, type
The above approach leaves out the <Patients>
element altogether for an empty set.
The one below includes the tag but adds the xsi:nil="true"
attribute, which also causes validation to fail.
select
(
select
PatientFields
from
PatientTable
for xml path('Patient'), elements xsinil, type
) Patients
for xml path('Hospital'), elements xsinil, type
Is there any way to force the container element to be present?
select
(
select
'John Doe'
where 1 = 0
for xml path('Patient'), type
)
for xml path('Patients'), root('Hospital'), type