Toad World® Forums

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

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