sql server - How to query values from xml nodes? -

sql server - How to query values from xml nodes? -

i have table contains 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 want generate set, contains:

organizationreportreferencenumber organizationnumber ================================= ================== 1 4 2 4 3 4 4 4

i've tried:

select foo.value('/reportheader/organizationreportreferenceidentifier') reportidentifiernumber, foo.value('/reportheader/organizationnumber') orginazationnumber cdrbatches.rawxml.query('/grobreportxmlfilexmlfile/grobreport/reportheader') foo

but doesn't work. i've tried:

select foo.value('/reportheader/organizationreportreferenceidentifier') reportidentifiernumber, foo.value('/reportheader/organizationnumber') orginazationnumber rawxml.nodes('/grobreportxmlfilexmlfile/grobreport/reportheader') bar(foo)

but doesn't work. xpath expression

/grobreportxmlfilexmlfile/grobreport/reportheader

is correct; in other xml scheme 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 queries i'd like see. after reading dozen stackover questions , answers, i'm no closer solving problem.

select b.batchid, x.xmlcol.value('(reportheader/organizationreportreferenceidentifier)[1]','varchar(100)') organizationreportreferenceidentifier, x.xmlcol.value('(reportheader/organizationnumber)[1]','varchar(100)') organizationnumber batches b cross apply b.rawxml.nodes('/casinodisbursementreportxmlfile/casinodisbursementreport') x(xmlcol);

demo: sqlfiddle

sql-server sql-server-2005 xpath sqlxml