How to query values from xml nodes?
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)
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
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