CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ashley John
Ashley John

Reputation: 2453

Handling NULL value in FOR XML

            SELECT Col1
    FROM Table
    WHERE Col2 = 63895746
    ORDER BY Col
    FOR XML PATH,TYPE

I dont need NULLS in the result. i want result to be wrapped by something like < Cols >< /Cols > even if col1 is null or not

In this example Table doesn't contain any rows for col2 = 63895746 , so i will get an empty result set, so the type directive will make it NULL and return it

Upvotes: 3

Views: 27435

Answers (6)

elle0087
elle0087

Reputation: 911

i think, in according with microsoft, is use 

ELEMENTS XSINIL

as option, after for xml path

https://learn.microsoft.com/en-us/sql/relational-databases/xml/columns-that-contain-a-null-value-by-default?view=sql-server-2017

Upvotes: 2

shunty
shunty

Reputation: 3758

How about

;with mytmp (f1) as (
  SELECT Col1
  FROM Table
  WHERE Col1 = 123456
  ORDER BY Col1
  FOR XML PATH('col'),root('cols'),TYPE
  )
select isnull(f1, '<cols></cols>')
from mytmp;

Upvotes: 5

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

SELECT T.X
FROM
  (
  SELECT Col1
  FROM Table
  WHERE Col2 = 63895746
  ORDER BY Col
  FOR XML PATH, TYPE
  ) AS T
FOR XML PATH('Cols'), TYPE

Upvotes: 3

twoleggedhorse
twoleggedhorse

Reputation: 5048

You need to use ELEMENTS XSINIL;

including-null-columns-empty-elements

Failing that you could nest your query:

SELECT ISNULL(data.Col1,'< Cols >< /Cols >') AS Col1
FROM (
SELECT Col1
FROM Table
WHERE Col2 = 63895746
ORDER BY Col
FOR XML PATH,TYPE
) data

Not at my machine so makes it a little tough to test.

Upvotes: 6

steoleary
steoleary

Reputation: 9278

If Col1 is a string then you can use the following to achieve this:

SELECT COALESCE(Col1,'')
FROM Table
WHERE Col2 = 63895746
ORDER BY Col
FOR XML PATH,TYPE

That will give you an empty tag if you have a null value

Upvotes: 1

TechDo
TechDo

Reputation: 18629

Try:

SELECT 
    ISNULL(Col1, '< Cols >< /Cols>') AS Col1
FROM Table
WHERE Col2 = 63895746
ORDER BY Col
FOR XML PATH,TYPE

Upvotes: 0

Related Questions