[SOLVED] Problem converting xsi:nil="true" to NUll by OpenXML

Issue

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'
    );

Solution

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 |
+------------+--------------------------------------+--------------------------------------+

Answered By – Yitzhak Khabinsky

Answer Checked By – Marie Seifert (BugsFixing Admin)

Leave a Reply

Your email address will not be published.