Comment interroger des valeurs à partir de nœuds xml?

j'ai une table qui contient une colonne XML:

CREATE TABLE Batches( 
   BatchID int,
   RawXml xml 
)

Le xml contient des éléments tels que:

<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>

Ce que je veux, c'est de générer un ensemble, qui contient:

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

j'ai essayé:

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

mais qui ne fonctionne pas. j'ai essayé:

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

Mais qui ne fonctionne pas. Le XPath expression

/GrobReportXmlFileXmlFile/GrobReport/ReportHeader

est correct; dans tout autre système xml il retourne:

<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>

Donc, il est évident qu'à partir de mes questions que j'avais comme à voir. Après avoir lu une dizaine de Stackover les questions et les réponses, je suis pas près de résoudre le problème.

source d'informationauteur Ian Boyd

  1. 22
    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);

    Démo: SQLFiddle

  2. 2

    Cela fonctionne, été testé...

    SELECT  n.c.value('OrganizationReportReferenceIdentifier[1]','varchar(128)') AS 'OrganizationReportReferenceNumber',  
        n.c.value('(OrganizationNumber)[1]','varchar(128)') AS 'OrganizationNumber'
FROM    Batches t
Cross   Apply RawXML.nodes('/GrobXmlFile/Grob/ReportHeader') n(c)
  3. 1

    Essayez ceci:

    SELECT RawXML.value('(/GrobXmlFile//Grob//ReportHeader//OrganizationReportReferenceIdentifier/node())[1]','varchar(50)') AS ReportIdentifierNumber,
       RawXML.value('(/GrobXmlFile//Grob//ReportHeader//OrganizationNumber/node())[1]','int') AS OrginazationNumber
FROM Batches