Reading complex xml file by sql2008

Question

Hello;

I am trying to read xml file as below but it returns null 

XML file is as below

 <?xml version="1.0" encoding="utf-8" ?>

- <Transfer>

- <Dispatch DispatchNo="00585857443">

  <Detail DispatchDate="2012-08-29" StartDate="2012-08-30 09:08:47" FinishDate="2012-08-31 09:11:59" CarrierItemCount="2" isCancel="TRUE" />

- <Carrier Code="085854745855544582">

- <SKU Code="87400709">

  <Lot number="28550">58541022A</Lot>

  </SKU>

  </Carrier>

- <Carrier Code="00495295401000534175">

- <SKU Code="852525136">

  <Lot number="29550">2BD0023B</Lot>

  </SKU>

  </Carrier>

  </Dispatch>

  </Transfer>

My code is below

DECLARE @FILE NVARCHAR(500)
DECLARE @SKU NVARCHAR(50)
DECLARE @Lot NVARCHAR(50)
DECLARE @DispatchNo NVARCHAR(50)
DECLARE @DispatchDate NVARCHAR(50)
DECLARE @StartDate    Nvarchar(20)
DECLARE @FinishDate   NVARCHAR(20)
DECLARE @CarrierItemCount NVARCHAR(20)
DECLARE @isCancel         NVARCHAR(20)
DECLARE @Cmd NVARCHAR(255)
DECLARE @Carrier       NVARCHAR(50)

CREATE TABLE #T (IntCol int, XmlCol xml)

  SET @FILE = 'C:\FTP_DIR\DSP_00885854_120903_110002.xml'

SET @Cmd='INSERT INTO #T(XmlCol)
SELECT * FROM OPENROWSET(
   BULK'+''''+@FILE+''''+',
   SINGLE_BLOB) AS x'

EXEC (@Cmd)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

SELECT distinct


                RT.IRT.value('(./@DispatchNo)[1]', 'NVARCHAR (20)')     as DispatchNo,                
                DD.DR.value('(./@DispatchDate)[1]','NVARCHAR (20)')     as DispatchDate,
                DD.DR.value('(./@StartDate)[1]','NVARCHAR(20)')         as StartDate,
                DD.DR.value('(./@FinishDate)[1]','NVARCHAR(20)')        as FinishDate,
                DD.DR.value('(./@CarrierItemCount)[1]','NVARCHAR(20)')  as CarrierItemCount,
                DD.DR.value('(./@isCancel)[1]','NVARCHAR(20)')          as isCancel,
                CC.CR.value('(./@Carrier)[1]','NVARCHAR(50)')           as CarrierCode,
                SK.SR.value('(./@SKU)[1]','NVARCHAR(50)')               as SKU,
                LT.LR.value('(./@LOT)[1]','NVARCHAR(50)')               as LOT


                FROM #t as t

                                                                             CROSS APPLY                     
                                                        t.XmlCol.nodes('./Transfer/Dispatch')  as  RT(IRT)


                                                                        CROSS APPLY                     

                                                  RT.IRT.nodes('Detail')  as  DD(DR)



                                                                  CROSS APPLY                  

                                           RT.IRT.nodes('Carrier')  as  CC(CR)

                                                             CROSS APPLY                  

                                       CC.CR.nodes('SKU')  as  SK(SR)

                                                         CROSS APPLY                  

                                 SK.SR.nodes('LOT')  as  LT(LR)

Would you please help where is my mistake 

Answer 1

XML is case-sensitive. Thus its 'Lot' not 'LOT' in the last CROSS APPLY.

Answer 2

Try this:

SELECT DISTINCT
	RT.IRT.value('(@DispatchNo)[1]', 'NVARCHAR (20)')     as DispatchNo,                
	DD.DR.value('(@DispatchDate)[1]','NVARCHAR (20)')     as DispatchDate,
	DD.DR.value('(@StartDate)[1]','NVARCHAR(20)')         as StartDate,
	DD.DR.value('(@FinishDate)[1]','NVARCHAR(20)')        as FinishDate,
	DD.DR.value('(@CarrierItemCount)[1]','NVARCHAR(20)')  as CarrierItemCount,
	DD.DR.value('(@isCancel)[1]','NVARCHAR(20)')          as isCancel,
	CC.CR.value('(@Code)[1]','NVARCHAR(50)')           as CarrierCode,
	SK.SR.value('(@Code)[1]','NVARCHAR(50)')               as SKU,
	LT.LR.value('(@number)[1]','NVARCHAR(50)')               as LOT
FROM #t as t
	CROSS APPLY t.XmlCol.nodes('Transfer/Dispatch')  as  RT(IRT)
	CROSS APPLY RT.IRT.nodes('Detail')  as  DD(DR)
	CROSS APPLY RT.IRT.nodes('Carrier')  as  CC(CR)
		CROSS APPLY CC.CR.nodes('SKU')  as  SK(SR)
		CROSS APPLY SK.SR.nodes('Lot')  as  LT(LR)

Mistakes were: LOT as already pointed out by Stefan, carrierCode attribute name is @Code not @Carrier, SKU is also @Code not @SKU, and LOT number is @number, DISTINCT was only bringing back one row as CarrierCode and SKU weren't being returned.

Do you understand that you use the @ symbol for attribute names?  Have a look through these articles:

xml Data Type Methods
http://msdn.microsoft.com/en-us/library/ms190798(v=SQL.105).aspx

Introduction to XQuery in SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms345122(SQL.90).aspx

Answer 3

Thank you  for your advices. I it so much usefull