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?
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):
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
zoggling
Member
201 Points
336 Posts
Missing XML root element when no records selected
Feb 20, 2013 04:09 PM|LINK
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'), ELEMENTSFor example, how can the following be generated, even when the select statement produces no results?
adamturner34
Contributor
4394 Points
1102 Posts
Re: Missing XML root element when no records selected
Feb 20, 2013 04:29 PM|LINK
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...
zoggling
Member
201 Points
336 Posts
Re: Missing XML root element when no records selected
Feb 21, 2013 08:08 AM|LINK
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?
zoggling
Member
201 Points
336 Posts
Re: Missing XML root element when no records selected
Feb 21, 2013 08:18 AM|LINK
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