Loading XML in SQL Server returns nulls -

Loading XML in SQL Server returns nulls -

i'm trying load xml file sql server i've got null value when load attribute dtv:appversion.

i'm using code load it:

declare @xml xml set @xml = n' <?xml version="1.0" ?> <poslog xmlns="http://www.nrf-arts.org/ixretail/namespace/" xmlns:dtv="http://www.datavantagecorp.com/xstore/" xmlns:cbp="http://www.datavantagecorp.com/xstore/cbp/" mlns:xsi="http://www.w3.org/2001/xmlschema-instance" xsi:schemalocation="http://www.nrf-arts.org/ixretail/namespace/poslog.xsd"> <transaction cancelflag="false" trainingmodeflag="false" offlineflag="false" dtv:appversion="3.4.1.9 - 8.6.0 - 0.0"> <retailstoreid>2001</retailstoreid> <workstationid>1</workstationid> <tillid>0</tillid> <sequencenumber>62602</sequencenumber> </transaction> </poslog>' ;with xmlnamespaces(default 'http://www.nrf-arts.org/ixretail/namespace/') select doc.col.value('@cancelflag', 'varchar(50)') cancelflag, doc.col.value('@trainingmodeflag', 'varchar(50)') trainingmodeflag, doc.col.value('@offlineflag', 'varchar(50)') offlineflag, doc.col.value('@appversion', 'varchar(50)') appversion, doc.col.value('retailstoreid[1]', 'varchar(50)') retailstoreid, doc.col.value('workstationid[1]', 'varchar(50)') workstationid, doc.col.value('tillid[1]', 'varchar(50)') tillid, doc.col.value('sequencenumber[1]', 'varchar(50)') sequencenumber @xml.nodes('/poslog/transaction') doc(col)

the problem value of dtv:appversion null: mistake?

thanks lot every suggestion.

you need reference namespace in xmlnamespaces clause, , refer in query element.

;with xmlnamespaces(default 'http://www.nrf-arts.org/ixretail/namespace/', 'http://www.datavantagecorp.com/xstore/' dtv) select doc.col.value('@cancelflag', 'varchar(50)') cancelflag, doc.col.value('@trainingmodeflag', 'varchar(50)') trainingmodeflag, doc.col.value('@offlineflag', 'varchar(50)') offlineflag, doc.col.value('@dtv:appversion', 'varchar(50)') appversion, doc.col.value('retailstoreid[1]', 'varchar(50)') retailstoreid, doc.col.value('workstationid[1]', 'varchar(50)') workstationid, doc.col.value('tillid[1]', 'varchar(50)') tillid, doc.col.value('sequencenumber[1]', 'varchar(50)') sequencenumber @xml.nodes('/poslog/transaction') doc(col)

sql-server xml