FOR XML PATH is not creating Child elements if they are coming with null values

indian143

Hi, I have a query that is using FOR XML PATH, when the child elements has values then its giving the Child elements, but when there are no values coming out of query for the Child elements, its finishing the X-path by putting and end tag (\), but I want the all the elements to be created if there are values are not, it doesn't have to repeat the elements as if there are values but at least one occurrence has to be there even if there are values or don't have values. Below are the example for it. Case 1 is the file that it is creating with all the values and elements, there are elements that are repeated I am not putting all that xml here to reduce the size, but I want to create all the elements of the xml even if there are values or no values at least one occurrence.

	DMH
	9xxxxxxx1
	Alameda
	01
	
		
			xxxxxxxx7
			true
			
				Hxxxxx
				HE
				TG
				
				
				19811201
				20030623

The Case 2 xml is as below:

DMH
xxxxxxxxx
Alameda
01

The sql script for the case 1 is:

SELECT
vw_OwnerXML5010.Owner,
vw_OwnerXML5010.Billing_EIN AS TP_EIN,
vw_OwnerXML5010.TP_Name AS TP_NAME,
vw_OwnerXML5010.County_Code AS County_Code,
(
SELECT
(
SELECT
vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
(
Select
vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
vw_ServicesXML5010.Modifier1 As "Modifier1",
vw_ServicesXML5010.Modifier2 AS "Modifier2",
vw_ServicesXML5010.Modifier3 AS "Modifier3",
vw_Serv

CHill60

Last tuesday I made a suggestion to you that would probably help you get assistance.

CHill60 wrote:

You might get a better response with some sample data and expected results

You've given us the outputs but still haven't provided any sample data ... on the tables (or views) vw_ServicesXML5010, vq_ServiceFacilityXML5010, vw_OwnerXML5010 or dbo.vw_SDMCPhaseTwoParentLevelXML You have said that you don't understand how the two scripts are different, yet one of them is reading from vw_OwnerXML5010 but the other is reading from dbo.vw_SDMCPhaseTwoParentLevelXML having been given an alias of vw_OwnerXML5010. I suggest you start by comparing the output of the following sql commands

SELECT * FROM vb_OwnerXML5010

and

SELECT * FROM dbo.vw_SDMCPhaseTwoParentLevelXML

introducing an appropriate WHERE clause to both. We can't help you any further without sample data.