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/>')

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/>')

For XML data retrieval

How to add line breaks after each node when I use "for xml explicit"? I would appreciate it for any suggestions.

If you are using Query Analyzer with SQL Server 2000

executing "dbcc traceon(257)" will cause XML generated with

FOR XML to be displayed with line breaks.

|||

Mark,

Thank you so much! I feel I almost get there. I use dbcc traceon(257) and see the link breaks. I have a question to save the file?

I have sql script that return xml via "FOR XML EXPLICIT" clause. I can specify output to file in Query Analyzer. Is it possible to save output file as file extension .ctl? I would like to send you the file format to you if it could save your time and understand better for the file format I want. My email address is fancorning4@.yahoo.com

Thank you again