如何从xml节点查询值? [英] How to query values from xml nodes?

问题描述

我有一个包含 XML 列的表:

i have a table that contains an XML column:

CREATE TABLE Batches( 
   BatchID int,
   RawXml xml 
)

该 xml 包含以下项目:

The xml contains items such as:

<GrobReportXmlFileXmlFile>
   <GrobReport>
       <ReportHeader>
          <OrganizationReportReferenceIdentifier>1</OrganizationReportReferenceIdentifier>
          <OrganizationNumber>4</OrganizationNumber>
       </ReportHeader>
  </GrobReport>
   <GrobReport>
       <ReportHeader>
          <OrganizationReportReferenceIdentifier>2</OrganizationReportReferenceIdentifier>
          <OrganizationNumber>4</OrganizationNumber>
       </ReportHeader>
  </GrobReport>
   <GrobReport>
       <ReportHeader>
          <OrganizationReportReferenceIdentifier>3</OrganizationReportReferenceIdentifier>
          <OrganizationNumber>4</OrganizationNumber>
       </ReportHeader>
  </GrobReport>
   <GrobReport>
       <ReportHeader>
          <OrganizationReportReferenceIdentifier>4</OrganizationReportReferenceIdentifier>
          <OrganizationNumber>4</OrganizationNumber>
       </ReportHeader>
  </GrobReport>

我想要的是生成一个集合，其中包含:

What i want is to generate a set, that contains:

OrganizationReportReferenceNumber  OrganizationNumber
=================================  ==================
1                                  4
2                                  4
3                                  4
4                                  4

我试过了:

SELECT 
    foo.value('/ReportHeader/OrganizationReportReferenceIdentifier') AS ReportIdentifierNumber,
    foo.value('/ReportHeader/OrganizationNumber') AS OrginazationNumber
FROM CDRBatches.RawXML.query('/GrobReportXmlFileXmlFile/GrobReport/ReportHeader') foo

但这不起作用.我试过了:

but that doesn't work. i've tried:

SELECT 
    foo.value('/ReportHeader/OrganizationReportReferenceIdentifier') AS ReportIdentifierNumber,
    foo.value('/ReportHeader/OrganizationNumber') AS OrginazationNumber
FROM RawXML.nodes('/GrobReportXmlFileXmlFile/GrobReport/ReportHeader') bar(foo)

但这行不通.XPath 表达式

/GrobReportXmlFileXmlFile/GrobReport/ReportHeader

是正确的；在任何其他 xml 系统中它返回:

is correct; in any other xml system it returns:

<ReportHeader>
    <OrganizationReportReferenceIdentifier>1</OrganizationReportReferenceIdentifier>
    <OrganizationNumber>4</OrganizationNumber>
</ReportHeader>
<ReportHeader>
    <OrganizationReportReferenceIdentifier>2</OrganizationReportReferenceIdentifier>
    <OrganizationNumber>4</OrganizationNumber>
</ReportHeader>
<ReportHeader>
    <OrganizationReportReferenceIdentifier>3</OrganizationReportReferenceIdentifier>
    <OrganizationNumber>4</OrganizationNumber>
</ReportHeader>
<ReportHeader>
    <OrganizationReportReferenceIdentifier>4</OrganizationReportReferenceIdentifier>
    <OrganizationNumber>4</OrganizationNumber>
</ReportHeader>

所以，从我的查询中可以明显看出我喜欢看到的.在阅读了十几个 Stackover 问答后，我离解决问题还很远.

So, it's obvious from my queries that i'd like to see. After reading a dozen Stackover questions and answers, i'm no closer to solving the problem.

推荐答案

SELECT  b.BatchID,
        x.XmlCol.value('(ReportHeader/OrganizationReportReferenceIdentifier)[1]','VARCHAR(100)') AS OrganizationReportReferenceIdentifier,
        x.XmlCol.value('(ReportHeader/OrganizationNumber)[1]','VARCHAR(100)') AS OrganizationNumber
FROM    Batches b
CROSS APPLY b.RawXml.nodes('/CasinoDisbursementReportXmlFile/CasinoDisbursementReport') x(XmlCol);

演示:SQLFiddle

这篇关于如何从xml节点查询值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！