CROSS APPLY 到命名空间，子节点返回重复记录 [英] CROSS APPLY to namespace, sub nodes returns duplicate records

问题描述

我有这个 xml(为了可读性删除了大部分).我得到错误的记录.为每个 CAS_Number 提取的化学名称错误.一个 CAS_Number 表示一种化学品.但在这里，对于相同的 CAS_number，我有不同的化学名称.

I have this xml (removed most of it for readability). I get wrong records. Wrong chemical names fetched for every CAS_Number. One CAS_Number means one chemical. But here, for the same CAS_number, I have different chemical names.

这是我交叉申请中的问题，但我无法弄清楚.

It's the problem in my cross apply but I couldn't figure it out.

<ArrayOfCatalogItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<CatalogItem Version="1">
   <Msds xmlns="http://3ecompany.com/webservices/catalogitemxml">
     <ProductIdentifiers xmlns="http://3ecompany.com/webservices/catalogitemxml">
      <Identifier>M007628</Identifier>
      <Source>CPN</Source>
      <FirstExportDate xsi:nil="true" />
      <LastExportDate xsi:nil="true" />
      <FlaggedForResend xsi:nil="true" />
    </ProductIdentifiers>
     <Ingredients>
        <ChemicalName>Hexane</ChemicalName>
        <Cas>000110-54-3</Cas>
        <AvgPercent>20.000000</AvgPercent>
      </Ingredients>
      <Ingredients>
        <ChemicalName>2-Propanone</ChemicalName>
        <Cas>000067-64-1</Cas>
        <AvgPercent>20.000000</AvgPercent>
      </Ingredients>
      <Ingredients>
        <ChemicalName>Petroleum gases, liquefied, sweetened</ChemicalName>
        <Cas>068476-86-8</Cas>
      </Ingredients>
  </Msds>
</CatalogItem>
</ArrayOfCatalogItem>

存储过程是这样的:

DECLARE @XmlTable TABLE (XMLDATA XML)

INSERT INTO @XmlTable(XMLData)
    SELECT CONVERT(XML, BulkColumn) AS BulkColumn 
    FROM OPENROWSET(BULK 'C:\AA.Sample.File.LUS.Pilly-oneCI.xml', SINGLE_BLOB) AS x;

;WITH XMLNAMESPACES ('http://3ecompany.com/webservices/catalogitemxml' as CI)
SELECT 
    --CIVersion = CI.value('@Version', 'int'),
    Identifier = PID.value('(.)\[1\]', 'varchar(9)'),
    Product_Name = MSDSPN.value('(.)\[1\]','varchar(100)'),
    CAS_Number = CAS.value('(.)\[1\]', 'varchar(20)'),
    Chemical_Name = CN.value('(.)\[1\]', 'varchar(100)')

FROM
    @XmlTable
CROSS APPLY
    XMLData.nodes('/ArrayOfCatalogItem/CatalogItem') AS XT(CI)
OUTER APPLY
    CI.nodes('CI:ProductIdentifiers/CI:Identifier') AS XT2(PID)
CROSS APPLY
    CI.nodes('CI:Msds/CI:Ingredients/CI:Cas') AS XT18(CAS)
CROSS APPLY
    CI.nodes('CI:Msds/CI:Ingredients/CI:ChemicalName') AS XT19(CN)

推荐答案

试试这个:

;WITH XMLNAMESPACES ('http://3ecompany.com/webservices/catalogitemxml' as CI)
SELECT 
    Identifier = CI.value('(CI:ProductIdentifiers[1]/CI:Identifier)[1]', 'varchar(9)'),
    CAS_Number = Ingred.value('(CI:Cas)[1]', 'varchar(20)'),
    Chemical_Name = Ingred.value('(CI:ChemicalName)[1]', 'varchar(100)')
FROM
    @XmlTable
CROSS APPLY
    XMLData.nodes('/ArrayOfCatalogItem/CatalogItem/CI:Msds') AS XT(CI)
CROSS APPLY
    CI.nodes('CI:Ingredients') AS XT18(Ingred)

我获得了每个 节点的 XML 片段列表，并从中获取了 ProductIdentifiers 信息.另外，从这个节点，我还获得了 节点的子 XML 片段列表，并从中获取详细信息.

I get a list of XML fragments for each <Msds> node, and from this I grab the ProductIdentifiers info. Plus, from this node, I also get a list of sub-XML-fragments for the <Ingredients> nodes, and grab detailed into from these.

我的输出如下所示:

这篇关于CROSS APPLY 到命名空间，子节点返回重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！