CODEWITHSUNDEEP
sql-serverxml
FrantZ
FrantZ

Reputation: 13

Loading XML in SQL Server returns nulls

I'm trying to load an XML file into SQL Server but I've got null value when I load the attribute dtv:AppVersion.

I'm using this code to 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
FROM @xml.nodes('/POSLog/Transaction') doc(col)

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

Thanks a lot for every suggestion.

Upvotes: 1

Views: 157

Answers (1)

podiluska
podiluska

Reputation: 51514

You need to a reference to the namespace in the xmlnamespaces clause, and refer to that in the query element.

;with xmlnamespaces(default 'http://www.nrf-arts.org/IXRetail/namespace/', 
'http://www.datavantagecorp.com/xstore/' as 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
FROM @xml.nodes('/POSLog/Transaction') doc(col)

Upvotes: 1

Related Questions