SQL Server 2000 Enterprise Edition SP3a
Windows 2003 Enterprise Edition
I am trying to export a subset of a table to XML (using the where clause) and I am not getting all fields exported. I have tried using Auto, Raw, and Explicit and they all omit these fields. Here is my schema:
CREATE TABLE [dbo].[TableName] (
[TableID] [uniqueidentifier] NOT NULL ,
[Field01] [char] (6) NOT NULL ,
[Field02] [char] (4) NOT NULL ,
[Field03] [int] NOT NULL ,
[Field04] [varchar] (50) NOT NULL ,
[Field05] [char] (4) NULL ,
[Field06] [smalldatetime] NOT NULL ,
[Field07] [smallint] NULL ,
[Field08] [tinyint] NULL ,
[Field09] [tinyint] NULL ,
[Field10] [tinyint] NULL ,
[Field11] [smalldatetime] NULL ,
[Field12] [smalldatetime] NULL ,
[Field13] [money] NULL ,
[Field14] [money] NULL ,
[Field15] [smalldatetime] NULL ,
[Field16] [varchar] (50) ,
[Field17] [money] NULL ,
[Field18] [money] NULL ,
[Field19] [bit] NULL
) ON [PRIMARY]
When the XML is generated, I am not getting Field07, Field08, Field09, Field10, Field12, Field13, Field14, Field15, and Field16.
Here is my query:
CREATE PROCEDURE dbasp_get_TableData
(@.TableID uniqueidentifier) AS
SELECT
TableID, Field01, Field02, Field03, Field04, Field05, Field06, Field07,
Field08, Field09, Field10, Field11, Field12,
Field13, Field14, Field15, Field16,
Field17, Field18, Field19
FROM database.dbo.TableName
WHERE tableid = @.TableID
FOR XML AUTO, ELEMENTS
I still get the same problem if I do a Select * statement. All fields have data, so NULL cannot be an issue.
Please help!!! It does not make sense.
Thank you in advance.
I couldn't repro your problem. Following is my repro and result. Please provide your repro script and the version of your SQL 2000.
use tempdb
go
CREATE TABLE [dbo].[TableName] (
[TableID] [uniqueidentifier] NOT NULL ,
[Field01] [char] (6) NOT NULL ,
[Field02] [char] (4) NOT NULL ,
[Field03] [int] NOT NULL ,
[Field04] [varchar] (50) NOT NULL ,
[Field05] [char] (4) NULL ,
[Field06] [smalldatetime] NOT NULL ,
[Field07] [smallint] NULL ,
[Field08] [tinyint] NULL ,
[Field09] [tinyint] NULL ,
[Field10] [tinyint] NULL ,
[Field11] [smalldatetime] NULL ,
[Field12] [smalldatetime] NULL ,
[Field13] [money] NULL ,
[Field14] [money] NULL ,
[Field15] [smalldatetime] NULL ,
[Field16] [varchar] (50) ,
[Field17] [money] NULL ,
[Field18] [money] NULL ,
[Field19] [bit] NULL
) ON [PRIMARY]
go
insert TableName values(newid(), 'abc', 'def', 3, '04', '05', getdate(), 7, 8, 9, 10, getdate(), getdate(), 13.1, 14.5, getdate(), '16varchar', 17.3, 18.5, 1)
go
CREATE PROCEDURE dbasp_get_TableData
(@.TableID uniqueidentifier) AS
SELECT
TableID, Field01, Field02, Field03, Field04, Field05, Field06, Field07,
Field08, Field09, Field10, Field11, Field12,
Field13, Field14, Field15, Field16,
Field17, Field18, Field19
FROM tempdb.dbo.TableName
WHERE tableid = @.TableID
FOR XML AUTO, ELEMENTS
go
exec dbasp_get_TableData '9883A32F-3EF4-408F-BFEC-6540598FEBC1'
Result I get:
==================
<tempdb.dbo.TableName><TableID>9883A32F-3EF4-408F-BFEC-6540598FEBC1</TableID><Field01>abc </Field01><Field02>def </Field02><Field03>3</Field03><Field04>04</Field04><Field05>05 </Field05><Field06>2007-01-16T10:21:00</Field06><Field07>7</Field07><Field08>8</Field08><Field09>9</Field09><Field10>10</Field10><Field11>2007-01-16T10:21:00</Field11><Field12>2007-01-16T10:21:00</Field12><Field13>13.1000</Field13><Field14>14.5000</Field14><Field15>2007-01-16T10:21:00</Field15><Field16>16varchar</Field16><Field17>17.3000</Field17><Field18>18.5000</Field18><Field19>1</Field19></tempdb.dbo.TableName>
|||I did find where the problem was coming from. I reproduced the same result as your example. Somehow I had changed the GUID that was used when I knew that all fields were accounted for (not null) to a GUID that had several NULL values. Those fields don't get exported (not even the field names) when it is null. I have been able to get my code to work by utilizing ISNULL. Thank you for your time in helping me.