How to avoid SQL Server returning duplicates values when using XQuery

Question

I found some questions here that are close to what I've been facing, but none of them fixed my query. When I parse out an XML document into a SQL result set, I'm getting some values duplicates. It seems the information is getting through twice, but when I try to use only one Outer apply, it brings only NULL values.

Here I'm trying to get the Barcode and quantity, but the barcode repeats changing the quantity total. It looks like every single barcode has been attached to any quantity in the document.

I've been stuck on this issue for quite a while, and I would really appreciate any light on this.

Thank you in advance.

    With cte as
    (Select

    Id,
    Filename,
    Cast(replace(cast(XMLData as nvarchar(max)), 'utf-8', 'utf-16') as xml) as XMLContent,
    LoadedDateTime

    From XMLFilesDESADV
    Where Processed Is Null)

    Select 

    --Id,
    --Filename,
    --Header.N.value ('(*:SalesOrderNumber)[1]', 'Varchar(50)') as SalesOrderNumber,
    --Header.N.value ('(*:SalesOrderNumber)[1]', 'Varchar(50)') as SalesOrderNumber1,
    --Header.N.value ('(*:CustomerReference)[1]', 'Varchar(50)') as PONumber,
    --PVDetail.N.value ('(*:CustomerNumber)[1]', 'Varchar(100)') as StoreID,
    --PartyVariant.N.value ('(*:Name)[1]', 'Varchar(100)') as StoreName,
    LinesVariant.N.value ('(*:GlobalTradeItemNumber)[1]', 'varchar(100)') as BarCode,
    LinesVariant2.N.value ('(*:Quantity)[1]', 'varchar(100)') as Quantity

    From cte


    --outer apply cte.XMLContent.nodes('/*:SalesDespatchAdvice_Xml_Full/*:Header') as Header(N)
    --outer apply cte.XMLContent.nodes('/*:SalesDespatchAdvice_Xml_Full/*:Header/*:Parties/*:BuyerParty/*:PartyIdentificationDetail') as PVDetail(N)
    --outer apply cte.XMLContent.nodes('/*:SalesDespatchAdvice_Xml_Full/*:Header/*:Parties/*:BuyerParty') as PartyVariant(N)
    outer apply cte.XMLContent.nodes('/*:SalesDespatchAdvice_Xml_Full/*:Lines/*:Line/*:Item') as LinesVariant(N)
    outer apply cte.XMLContent.nodes('/*:SalesDespatchAdvice_Xml_Full/*:Lines/*:Line/*:Summations') as LinesVariant2(N)

This is the XML sample:

XML Sample

And this is the result I've been getting:

BarCode	Quantity
5715502955189	6
5715502955189	4
5715502955189	2
5715502955189	4
5715502955189	2
5715502955264	6
5715502955264	4
5715502955264	2
5715502955264	4
5715502955264	2
5715512355238	6
5715512355238	4
5715512355238	2
5715512355238	4
5715512355238	2
5715512355245	6
5715512355245	4
5715512355245	2
5715512355245	4
5715512355245	2
5715512355252	6
5715512355252	4
5715512355252	2
5715512355252	4
5715512355252	2

Answer 1

I think I founf the issue. I will posted what I've done.

    Select 

    LinesVariant.N.value ('(*:Item/*:GlobalTradeItemNumber)[1]', 'varchar(100)') as BarCode,
    LinesVariant.N.value ('(*:Summations/*:Quantity)[1]', 'varchar(100)') as Quantity

    From cte

    outer apply cte.XMLContent.nodes('/*:SalesDespatchAdvice_Xml_Full/*:Lines/*:Line') as LinesVariant(N)