ds.GetXML or ds.WriteXML problem with NULLS

[Chad]

I've run into a problem where I create a DataTable, Create an Integer column
and assign the value DBNULL.Value to it.

When I use the ds.GetXML to get the XML represnetation of this table, if the
column contains only NULL values the column does not appear in the table.

I would expect the column to appear with no value between the tags or some
other convention used.

This can't be working as intended, can it?! :-(

When I pass the XML to a SP, and try to query a column that does not
exist,....BOOM!

Suggestions?

 

[Cor Ligthert [MVP]]

Chad,

It is as intended. To go arround problems when you need it (because it can
happen that from one tag there is nothing), you have as well to write the
schema.
\\\
ds.writexml("path",XmlWriteMode.WriteSchema)
///
I hope this helps,

Cor

 

[Chad]

My experience with XML is limited. Please explain to me how I would use the
schema in the following situation.

Rather than passing one record at a time to a database server, I construct a
DataTable, add rows to it, and use the ds.GetXml function to get the XML
representation of the table. I then pass the XML to a Stored Procedure to
process.

Here's the first part of my Stored procedure:

How would I used the schema info to determine that a column exists in the
datatabase but no tags appeared for the column in the XML data because the
values was NULL?

It currently blows up if I attempt to selected a column that is not
represented in the XML because the value is NULL.

Also, the GetXML function returns DateTimes in a weird format, so I end up
taking the first 10 chars to get the date part only: YYYY/MM/DD. I then cast
is as a DateTime. This seeems like an ugly way to handle it. Is there a
better way?


ALTER PROC dbo.SubmitMeasureConfig

(
@AccountID int,
@SystemUserId int,
@SystemUserLanguageId int,
@xmlAccountSkpis ntext
)

AS

BEGIN


/*
SET @AccountId = 20
SET @SystemUserId = 144
SET @SystemUserLanguageId = 65

SET @xmlAccountSkpis =
'<?xml version="1.0" standalone="yes"?>
<Workflow>
<Config>
<AccountSkpiId>344</AccountSkpiId>
<MeasureOwnerId>156</MeasureOwnerId>
<FrequencyId>8</FrequencyId>
<NextRequest>2012-12-12T00:00:00.0000000-05:00</NextRequest>
<NextDueDate>2012-12-31T00:00:00.0000000-05:00</NextDueDate>
</Config>
<Config>
<AccountSkpiId>330</AccountSkpiId>
<MeasureOwnerId>157</MeasureOwnerId>
<FrequencyId>4</FrequencyId>
<NextRequest>2005-06-28T00:00:00.0000000-04:00</NextRequest>
<NextDueDate>2005-07-13T00:00:00.0000000-04:00</NextDueDate>
</Config>
<Config>
<AccountSkpiId>333</AccountSkpiId>
<MeasureOwnerId>157</MeasureOwnerId>
<FrequencyId>4</FrequencyId>
<NextRequest>2005-06-28T00:00:00.0000000-04:00</NextRequest>
<NextDueDate>2005-07-13T00:00:00.0000000-04:00</NextDueDate>
</Config>
</Workflow>'
*/

DECLARE @DocHandle int

-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmlAccountSkpis

--FIRST DO Account SKPI Config part

-- Execute a SELECT statement using OPENXML rowset provider to create
--a temp table representing the Config table from the XML text.

SELECT
AccountSkpiId,
MeasureOwnerId,
FrequencyId,
CAST(LEFT(NextRequest,10) As DateTime) AS NextRequest,
CAST(LEFT(NextDueDate,10) As DateTime) AS NextDueDate
INTO
#AccountSkpis_Config
FROM

OPENXML (@DocHandle, '/Workflow/Config',2)

WITH
(
AccountSkpiId int,
MeasureOwnerId int,
FrequencyId int,
NextRequest varchar(10),
NextDueDate varchar(10)
)


--Update master table with data in temp table that was created from XML
data

UPDATE
AccountSkpi
SET
MeasureOwnerID = #AccountSkpis_Config.MeasureOwnerID,
FrequencyId = #AccountSkpis_Config.FrequencyId,
NextRequest = #AccountSkpis_Config.NextRequest,
NextDueDate = #AccountSkpis_Config.NextDueDate
FROM
#AccountSkpis_Config
WHERE
AccountSkpi.AccountSkpiId = #AccountSkpis_Config.AccountSkpiId

DROP TABLE #AccountSkpis_Config

EXEC sp_xml_removedocument @DocHandle

END

 

[Cor Ligthert [MVP]]

Chad,

I am absolute no SQL expert, moreover I hate that so called program
language, made around usual USA slang. We saw a sample of that in this
newsgroup yesterday. Somebody was searching how to delete a Table in SQL,
most people in the world would expect Delete however it is Drop.

In my opinion can you better ask the rest of your question (now it is so
SQL) in a SQL specialized newsgroup.

I hope this helps anyway.

Cor