Missing XML root element when no records selected

Question

User963835749 posted

When the following select query retrieves no results, no XML is generated. Is there a way to at least generate the root element?

SELECT
T2.Field1,
T2.Field2,
T1.Field3
FROM dbo.PCConditions T1
INNER JOIN dbo.CConditions T2 ON T1.CCID = T2.CCID
WHERE T1.OtherID = 1234
FOR XML PATH('Condition'), TYPE, ROOT('Conditions'), ELEMENTS

For example, how can the following be generated, even when the select statement produces no results?

<Conditions />

Answer 1

User-851967432 posted

You can add some logic to the stored procedure to check for rowcount. If there are 0, select a dummy row...here's some pseudo

if ((select count(*) from myTable Where ID  =1234) = 0)

begin

select 0[field1],0[field2],0[field3] for xml...

end

else

Select Field1, field2, field3 from myTable for xml...

Answer 2

User963835749 posted

Hi Adam,

Thank you for your response. The only problem with this is that, although the root element is returned, the child elements are also returned, and with values present (zeros):

<Conditions>
  <Condition>
    <Field1>0</Field1>
    <Field2>0</Field2>
    <Field3>0</Field3>
  </Condition>
</Conditions>

Is there a way to just return the root element, so that it is always present even when no data is present?

Answer 3

User963835749 posted

The following is a workaround, but I was hoping there might be a more elegant solution using FOR XML...

IF NOT EXISTS
	(
	SELECT
	T2.Field1,
	T2.Field2,
	T1.Field3
	FROM dbo.PCConditions T1
	INNER JOIN dbo.CConditions T2 ON T1.CCID = T2.CCID
	WHERE T1.OtherID = 1234
	)
BEGIN
	SELECT CONVERT(xml, ('<Conditions>' + '</Conditions>'))
END
ELSE
BEGIN
	SELECT
	T2.Field1,
	T2.Field2,
	T1.Field3
	FROM dbo.PCConditions T1
	INNER JOIN dbo.CConditions T2 ON T1.CCID = T2.CCID
	WHERE T1.OtherID = 1234
	FOR XML PATH('Condition'), TYPE, ROOT('Conditions'), ELEMENTS
END