SQL表列中的XML解析

2024-05-04 • 问答

我在表中有一个XML列要解析。这是该列中数据的示例：

<product displaygtin="08858011802159" >

/* First Block */

<identification>
    <attributeList>
      <attribute name="gpcSegment">
        <value>Food/Beverage/Tobacco</value>
      </attribute>
    </attributeList>

/* Second Block */ 

</identification>
  <itemCertification>
    <nutrition>
      <attributeList>
        <attribute name="foodLabelVersion">
          <value>P2017</value>
        </attribute>
       </attributeList>
</nutrition>

/*Last Block*/

<certification>
      <attributeList>
        <attribute name="claimKosher">
          <value>false</value>
        </attribute>
      </attributeList>
    </certification>
  </itemCertification>

</product>

我可以轻松地从第一个区块中提取内容，但是第二个和最后一个区块我没有任何结果，请有人研究以下查询并建议如何从其他区块中提取内容

SELECT [BundleID],[XML],[DisplayGTIN],b.x.value('.','VARCHAR(MAX)') AS "Division"
 FROM 
     [ItemCert_Reporting].[dbo].[Retailer_Extract] AS t1
 CROSS APPLY 
     t1.[XML].nodes('product/identification/attributeList/attribute') AS b(x)
 WHERE 
     displaygtin LIKE '%8858011802159'
     AND b.x.value('@name','VARCHAR(MAX)') = 'gpcSegment'

create table #Product (Product XML) insert into #Product values ( '<product displaygtin="08858011802159"> <identification> <attributeList> <attribute name="gpcSegment"> <value>Food/Beverage/Tobacco</value> </attribute> </attributeList> </identification> <itemCertification> <nutrition> <attributeList> <attribute name="foodLabelVersion"> <value>P2017</value> </attribute> </attributeList> </nutrition> <certification> <attributeList> <attribute name="claimKosher"> <value>false</value> </attribute> </attributeList> </certification> </itemCertification> </product>') select * from (select pref.value('(text())[1]','varchar(32)') as Attributes from #Product CROSS APPLY Product.nodes('//attributeList/attribute/value') AS Roles(pref) ) as Result drop table #Product

SQL表列中的XML解析

nimadedajibamaochuiz 回答：SQL表列中的XML解析

大家都在问