How to query values from xml nodes?

Question

i have a table that contains an XML column:

CREATE TABLE Batches(     BatchID int,    RawXml 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 

i've tried:

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) 

But that doesn't work. The XPath expression

/GrobReportXmlFileXmlFile/GrobReport/ReportHeader 

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> 

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.

Answer 1

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

Demo: SQLFiddle

Answer 2

This works, been tested...

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)