i have a table that contains an XML column: ``` CREATE TABLE Batches( BatchID int, RawXml xml ) ``` The xml contains items such as: ``` 1 4 2 4 3 4 4 4 ``` What i want is to generate a set, that contains: ``` OrganizationReportReferenceNumber OrganizationNumbe…

How to query values from xml nodes?

sql-server, sql-server-2005, sqlxml, xpath

Solution

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

Problem

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.

Original source