How to get null value from XML empty attribute in SQL Server using OPENXML() function?

ITHUBCITY · August 21, 2015, 4:15am

Hi all,

I have xlm file. These xml file has some empty attribute. i want to convert empty attribute to null value.

declare @xml xml
set @xml = '

<Stg_SystemCode>021</Stg_SystemCode>
** -- empty attribute **

<Stg_SystemCode/>
test
22

'

--select * from @xml.nodes('(/SyncInfo/Transactions/row)') ParamValues(TaskChainerTask)

DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

select * FROM OPENXML( @hdoc, '//SyncInfo/Transactions/row',7) WITH (Stg_SystemCode varchar(5),PrimaryTransaction int ,Customer nvarchar(500)) as xmlfile
EXEC sp_xml_removedocument @hdoc