使用XQuery解析XML时如何避免SQL Server返回重复值

问题描述 #

将XML文档解析为SQL结果集时，获取Barcode（条码）和Quantity（数量）出现值重复：每个条码都关联了文档中所有数量，导致条码重复出现且对应不同数量；仅使用一个Outer apply则返回NULL值。

原SQL代码 #

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)

当前错误结果 #

解决方案 #

问题根源：直接对整个XML的Item和Summations节点做outer apply会产生笛卡尔积，导致每个条码关联所有数量。正确做法是先定位到每个Line节点，再从Line内部关联对应的Item和Summations。

修改后的SQL代码：

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 
    LineItem.N.value('(*:GlobalTradeItemNumber)[1]', 'varchar(100)') as BarCode,
    LineSummation.N.value('(*:Quantity)[1]', 'varchar(100)') as Quantity
From cte
-- 先获取每个Line节点
outer apply cte.XMLContent.nodes('/*:SalesDespatchAdvice_Xml_Full/*:Lines/*:Line') as Lines(N)
-- 从当前Line节点下获取Item
outer apply Lines.N.nodes('*:Item') as LineItem(N)
-- 从当前Line节点下获取Summations
outer apply Lines.N.nodes('*:Summations') as LineSummation(N)

这样就能保证每个条码和对应的数量一一对应，不会出现重复关联的情况。

内容的提问来源于stack exchange，提问作者Tiago Alencar