For XML Path

im using the ROOT directive in association with FOR XML PATH to return results. When the query returns no records then I get no root node either (which makes the XML invalid). Is there an attribute that specifies that the root node should always be returned (even when empty)?

i.e. <ROOT />

thx

Not that I know of. Since no rows were returned, not data would be returned at all. You could do something like this:

select cast(
'<root>' +

coalesce((select *
from sys.objects
where 1=2 --change to 1=1 to get rows
for xml path),'')

+ '</root>' as xml)

This does seem to work, though not 100% sure if there will be much of a performance hit.

|||

thanks for the tip :)

im am confused becuase if i say that i want a resultset typed as xml, then i would expect (for the xml to be valid) that it has a root node regardless..... what concept am i missing if this is not the case?

|||I think the fact is, it isn't invalid XML, it is nothing. So if you return no data, then no XML is created.|||

i agree

BUT :)

that does mean that anything that uses the resultset requires a condition to check if it is Null and either 1)do nothing, or 2) subsitute it for what would be valid xml i.e. an empty parent node eg <Root />

i think a lot of applications would require number 2, and therefore think the XML functionality of sql2005 should have this built in.

|||

And I don't disagree with you, though you can use the cast and concatenation thing I posted earlier as a workaround.

If noone posts that I was wrong, consider posting your suggestion here: https://connect.microsoft.com/SQLServer/Feedback and then post in this thread that you have, and I will vote for it.

|||

how about this:-

CREATE PROCEDURE [dbo].[up_DoStuff]
(
-- params
)
AS
SET NOCOUNT ON;

DECLARE @.pXML XML
SET @.pXML = (
SELECT
...
FROM
...
WHERE
...
FOR
XML Path('Test'),
ELEMENTS,
ROOT('Tests'),
TYPE
)

SELECT ISNULL(@.pXML, '<Tests/>')