Loading XML in Sql Server returns nulls



I'm trying to load an xml file in Sql Server but I've got null value when i load an attribute. My xml looks like this:



<POSLog xmlns="http://ift.tt/XqxYA8" xmlns:dtv="http://ift.tt/1u4TSYg" xmlns:cbp="http://ift.tt/1qxi0g5" xmlns:xsi="http://ift.tt/ra1lAU" xsi:schemaLocation="http://ift.tt/Xqy0YG">
<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>


I'm trying to load it whit this code:



;with xmlnamespaces(default 'http://ift.tt/XqxYA8')
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
FROM @xml.nodes('/POSLog/Transaction') doc(col)


The problem is that the value of dtv:AppVersion is always null: where is te mistake ?


Thanks a lot for every suggestion. Regards, FR


No comments:

Post a Comment