June 3, 2011 at 8:41 pm
I have a valid SELECT ... FROM ... FOR XML EXPLICIT, ROOT('XYZ') SQL statement when the select statement has rows returned. How do I force SQL server to output the <XYZ></XYZ> text when no rows are returned by the select statement ?
June 6, 2011 at 10:22 am
I am not seeing a way to do it in one query...maybe someone else will chime in, but something along these lines may work for you:
USE AdventureWorks2008R2
GO
DECLARE @xml XML = (
SELECT 1 AS Tag,
NULL AS Parent,
E.ContactID AS [Employee!1!EmpID],
NULL AS [Name!2!FName],
NULL AS [Name!2!LName]
FROM HumanResources.Employee AS E
INNER JOIN Person.Contact AS P ON E.ContactID = P.ContactID
WHERE 1 = 0 -- change to 1 = 1 to see full output
FOR
XML EXPLICIT,
ROOT('XYZ')
) ;
IF @xml IS NULL
SET @xml = N'<XYZ></XYZ>'
SELECT @xml
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2011 at 10:30 am
Have you tried adding the XSINIL tag to the For XML statement?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 6, 2011 at 10:36 am
XSINIL is a good tip. It is an option if you switch your query from EXPLICIT to ELEMENTS.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2011 at 6:17 am
opc.three (6/6/2011)
XSINIL is a good tip. It is an option if you switch your query from EXPLICIT to ELEMENTS.
Explicit can do the null columns thing, you just have to code it in as a directional for the column. MSDN has details on how to do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 6:30 am
I went looking after your last post and came up empty. Care to share a link?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2011 at 6:53 am
opc.three (6/7/2011)
I went looking after your last post and came up empty. Care to share a link?
The element directive generates a contained element instead of an attribute. The contained data is encoded as an entity. For example, the < character becomes <. For NULL column values, no element is generated. If you want an element generated for null column values, you can specify the elementxsinil directive. This will generate an element that has the attribute xsi:nil=TRUE.
From: http://msdn.microsoft.com/en-us/library/ms189068.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 7:16 am
Hmmm...I saw where you could get NULL for specific columns within a returned result set, but cannot figure out how to do what the OP is asking regarding receiving no rows from the query.
I was playing with the example from http://msdn.microsoft.com/en-us/library/bb522622.aspx...modified below with comments to return 0 rows:
USE AdventureWorks2008R2 ;
GO
SELECT 1 AS Tag,
NULL AS Parent,
E.BusinessEntityID AS [Employee!1!EmpID],
BEA.AddressID AS [Employee!1!AddressID],
NULL AS [Address!2!AddressID],
NULL AS [Address!2!AddressLine1!ELEMENT],
NULL AS [Address!2!AddressLine2!ELEMENTXSINIL],
NULL AS [Address!2!City!ELEMENTXSINIL]
FROM HumanResources.Employee AS E
INNER JOIN Person.BusinessEntityAddress AS BEA ON E.BusinessEntityID = BEA.BusinessEntityID
WHERE E.BusinessEntityID IN (-1) -- make sure no rows are returned
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
E.BusinessEntityID,
BEA.AddressID,
A.AddressID,
AddressLine1,
AddressLine2,
City
FROM HumanResources.Employee AS E
INNER JOIN Person.BusinessEntityAddress AS BEA ON E.BusinessEntityID = BEA.BusinessEntityID
INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID
WHERE E.BusinessEntityID IN (-1) -- make sure no rows are returned
ORDER BY [Employee!1!EmpID],
[Address!2!AddressID]
FOR XML EXPLICIT ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2011 at 9:34 am
This is an old school answer, but consider leaving the ROOT('XYZ') out and starting with a Tag 1 that gives exactly one result. Here I have used a tally table to return a single XYZ node:
SELECT
1 AS Tag,
NULL AS Parent,
1 AS [XYZ!1!force!hide],
NULL AS [customer!2!ClientMoniker]
FROM tsqlc_Tally
WHERE N=1
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL AS [XYZ!1!force!hide],
[ClientID] AS [customer!2!ClientMoniker]
FROM [Clients_M1]
WHERE 1=0
FOR XML EXPLICIT
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply