Including NULL columns as empty elements in SELECT FOR XML

Hi everyone,

I was wondering if it is possible for a SELECT FOR XML statement to map a row with a NULL value in a column to an empty element in XML?

For example, let's say I have the following table:

CREATE TABLE NetworkAdapter

(

ID int PRIMARY KEY

MacAddress char(17)

)

The table has one row with the values (10, NULL). Can I use the SELECT FOR XML statement to return the following XML:

<NetworkAdapter>

<ID>10</ID>

<MacAddress /> -- Or <MacAddress></MacAddress>, doesn't matter

</NetworkAdapter>

Is it possible to do this without using ISNULL on the MacAddress column? Or if not, how would you do it using ISNULL?

Another somewhat related question ... Is it possible to use the SELECT FOR XML statement to return a set of empty elements for a SELECT statement that has no results? Using the NetworkAdapter table with just that one row listed above, let's say I have the following query:

SELECT *

FROM NetworkAdapter

WHERE ID = '5'

This query returns no results, but I would like to use it in conjunction with FOR XML to return this:

<NetworkAdapter>

<ID />

<MacAddress />

</NetworkAdapter>

Thanks.

There is a directive ELEMENTS XSINIL that causes NULL database values to be returned as an empty element with the attribute xsi:nil="true" e.g.

Code Snippet

SELECT ID, MacAddress

FROM NetworkAdapter

FOR XML AUTO, ELEMENTS XSINIL;

will then return

Code Snippet

<NetworkAdapter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ID>1</ID>

<MacAddress xsi:nil="true" />

</NetworkAdapter>

for rows where MacAdress is NULL. See http://msdn2.microsoft.com/en-us/library/ms178079.aspx

|||

Great! Thanks for your reply.

Inadvertantly deleted detail row...

All,
I am a newbie and in working on an existing report, I inadvertantly
deleted the only detail row. I was able to get the report working, but
I had to use a group header as a detail row (fortunately I had a unique
value column), because I could not see how to add a detail row. MS is
very clear - whatever kind of row you are on when you choose to Insert
a Row Above or Below is the kind of row that will be created. Since I
did not have a detail row, I couldn't add one.
So, can you add a detail row when there isn't one? If so, how?
Any ideas, references, resources, war stories or good clean jokes on
the subject would be greatly appreciated.
Thanks,
KathrynClick on the table so that the handles appear. Right click the handle at the
end of the row on the table header row and then choose table details. Then
the row will reappear.
wrote:
> All,
> I am a newbie and in working on an existing report, I inadvertantly
> deleted the only detail row. I was able to get the report working, but
> I had to use a group header as a detail row (fortunately I had a unique
> value column), because I could not see how to add a detail row. MS is
> very clear - whatever kind of row you are on when you choose to Insert
> a Row Above or Below is the kind of row that will be created. Since I
> did not have a detail row, I couldn't add one.
> So, can you add a detail row when there isn't one? If so, how?
> Any ideas, references, resources, war stories or good clean jokes on
> the subject would be greatly appreciated.
> Thanks,
> Kathryn
>|||Kim,
Thanks so much! It's simple once you know it, but I couldn't find it
ANYWHERE online or in BOL.
Kathryn

In Stored Proc - How do i find the next key value (integer) and use it to populate a field

Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005.

What I want is that -

1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)

2). and set COMPANY_ID = (new) ID

Any thoughts?

Thanks

ALTER PROCEDURE usp_tbl_Company_Insert
@.Company_ID int,
@.Name varchar(200),

AS

<FIND THE NEW ID of the new row in the database>

@.Company_ID = (new ID)

INSERT INTO tbl_Company (Company_ID, Name,)
VALUES (@.Company_ID, @.Name)

If its an IDENTITY column then you cannot insert any value into the field. SQL Server will insert that value for you. If you want to find out the value that was inserted use SCOPE_IDENTITY() function immediately after the insert. You can also get the value into a variable and use it accordingly.