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.

Yucca

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

Jacob

This works; i don't know how, but it works. What is x(XmlCol) doing? i assume x is an arbitrary identifier, and i assume XmlCol is an arbitrary identifier. What is happening in this query? And, i assume that CROSS APPLY is a synonym for FULL OUTER JOIN ON 1=1, so the table is joining to itself. Is it unable to query the values without the extra join? – Yucca

The syntax for nodes() calls is XmlSourceColumn.nodes() AS Table(XmlResultsColumn).x is an alias for nodes(...) method call (link). XmlCol is an alias for the xml column generated by nodes() method. nodes() method will extract all /CasinoDisbursementReportXmlFile/CasinoDisbursementReport elements (alias x). CROSS APPLY (link) is used to call a function/method/query (nodes() in this case) for every row from the left side (Batches) of the APPLY. – Jacob

CROSS APPLY is (somehow) similar with INNER JOIN (and OUTER APPLY is similar with LEFT OUTER JOIN). Answer for the last question: no. There is no other simpler solution. – Jacob

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)  

Unlike

There's a syntax error somewhere around test. But not being able to make heads or tails of the syntax, i cannot suggest any fix. – Yucca

Bah, sorry, that was my test column name, it should be RawXML in your case. – Unlike

Answer 3

Try this:

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

Goodspeed

This only grabs the first node for me. – Unlike

Answer 4

if you have only one xml in your table, you can convert it in 2 steps:

CREATE TABLE Batches( 
   BatchID int,
   RawXml xml 
)

declare @xml xml=(select top 1 RawXml from @Batches)

SELECT  --b.BatchID,
        x.XmlCol.value('(ReportHeader/OrganizationReportReferenceIdentifier)[1]','VARCHAR(100)') AS OrganizationReportReferenceIdentifier,
        x.XmlCol.value('(ReportHeader/OrganizationNumber)[1]','VARCHAR(100)') AS OrganizationNumber
FROM    @xml.nodes('/CasinoDisbursementReportXmlFile/CasinoDisbursementReport') x(XmlCol)

Cadell