locked
Missing XML root element when no records selected RRS feed

  • 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 />


    Wednesday, February 20, 2013 12:09 PM

Answers

  • 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
    


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 21, 2013 4:18 AM

All replies

  • 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...

    Wednesday, February 20, 2013 12:29 PM
  • 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?

    Thursday, February 21, 2013 4:08 AM
  • 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
    


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 21, 2013 4:18 AM