Problem converting xsi:nil="true" to NUll by OpenXML-CodePudding

I am sending data from a C# Windows forms application as XML to SQL The problem is that NULL values are converted and sent as xsi:nil="true". And when read by OpenXML it encounters an error Thank you all

Sample Query :

DECLARE @TimeConvert XML
    = '<?xml version="1.0" encoding="utf-8"?>
<ArrayOfTimeConvertCreateVm xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <TimeConvertCreateVm>
    <Characters>02</Characters>
    <TimeLeaveId>ec7c864b-7ebc-4d58-a94d-eb923fd20663</TimeLeaveId>
    <TimeMissionId xsi:nil="true" />
  </TimeConvertCreateVm>
  <TimeConvertCreateVm>
    <Characters>05</Characters>
    <TimeLeaveId xsi:nil="true" />
    <TimeMissionId>f03bb792-5a17-4467-b097-0bd819884fc1</TimeMissionId>
  </TimeConvertCreateVm>
</ArrayOfTimeConvertCreateVm>';

DECLARE @handler INT;
EXEC sp_xml_preparedocument @handler OUT, @TimeConvert;

SELECT Characters,   -- Characters - nvarchar(max)
       TimeLeaveId,  -- TimeLeaveId - uniqueidentifier
       TimeMissionId -- TimeMissionId - uniqueidentifier
FROM
    OPENXML(@handler, 'ArrayOfTimeConvertCreateVm/TimeConvertCreateVm')
    WITH
    (
        [Characters] NVARCHAR(50) 'Characters',
        [TimeLeaveId] UNIQUEIDENTIFIER 'TimeLeaveId',
        [TimeMissionId] UNIQUEIDENTIFIER 'TimeMissionId'
    );

CodePudding user response：

Microsoft proprietary OPENXML() and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000. Their use is diminished just to very few fringe cases. Starting from SQL Server 2005 onwards, it is strongly recommended to re-write your SQL and switch it to XQuery. Also, OPENXML() cannot take advantage of XML indexes while XQuery methods can.

Please try the following solution.

SQL

DECLARE @TimeConvert XML = 
'<?xml version="1.0" encoding="utf-8"?>
<ArrayOfTimeConvertCreateVm xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <TimeConvertCreateVm>
        <Characters>02</Characters>
        <TimeLeaveId>ec7c864b-7ebc-4d58-a94d-eb923fd20663</TimeLeaveId>
        <TimeMissionId xsi:nil="true"/>
    </TimeConvertCreateVm>
    <TimeConvertCreateVm>
        <Characters>05</Characters>
        <TimeLeaveId xsi:nil="true"/>
        <TimeMissionId>f03bb792-5a17-4467-b097-0bd819884fc1</TimeMissionId>
    </TimeConvertCreateVm>
</ArrayOfTimeConvertCreateVm>';

SELECT c.value('(Characters/text())[1]', 'NVARCHAR(50)') AS Characters
    , c.value('(TimeLeaveId/text())[1]', 'UNIQUEIDENTIFIER') AS TimeLeaveId
    , c.value('(TimeMissionId/text())[1]', 'UNIQUEIDENTIFIER') AS TimeMissionId
FROM @TimeConvert.nodes('/ArrayOfTimeConvertCreateVm/TimeConvertCreateVm') AS t(c);

Output

 ------------ -------------------------------------- -------------------------------------- 
| Characters |             TimeLeaveId              |            TimeMissionId             |
 ------------ -------------------------------------- -------------------------------------- 
|         02 | EC7C864B-7EBC-4D58-A94D-EB923FD20663 | NULL                                 |
|         05 | NULL                                 | F03BB792-5A17-4467-B097-0BD819884FC1 |
 ------------ -------------------------------------- --------------------------------------