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.

Bogdan Sahlean · Accepted Answer · 2020-03-21T19:20:17.740

46

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

edited Mar 21 '20 at 19:20

answered Feb 05 '13 at 17:31

Bogdan Sahlean

19,233
3
42
57

2

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? – Ian Boyd Feb 05 '13 at 17:57
2

The syntax for `nodes()` calls is `XmlSourceColumn.nodes() AS Table(XmlResultsColumn)`.`x` is an alias for `nodes(...)` method call ([link](http://msdn.microsoft.com/en-us/library/ms188282.aspx)). `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](http://msdn.microsoft.com/en-us/library/ms175156(v=SQL.90).aspx)) is used to call a function/method/query (`nodes()` in this case) for every row from the left side (`Batches`) of the `APPLY`. – Bogdan Sahlean Feb 05 '13 at 18:35
`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. – Bogdan Sahlean Feb 05 '13 at 18:41

score 8 · Answer 2 · edited Nov 27 '15 at 16:12

8

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)

edited Nov 27 '15 at 16:12

Ian Boyd

246,734
253
869
1,219

answered Feb 05 '13 at 17:20

Eric J. Price

2,740
1
15
21

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. – Ian Boyd Feb 05 '13 at 17:54
Bah, sorry, that was my test column name, it should be RawXML in your case. – Eric J. Price Feb 05 '13 at 19:15

score 3 · Answer 3 · edited Nov 27 '15 at 16:11

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

edited Nov 27 '15 at 16:11

Ian Boyd

246,734
253
869
1,219

answered Feb 05 '13 at 17:09

Morv

355
2
9

1

This only grabs the first node for me. – Eric J. Price Feb 05 '13 at 17:21

score 0 · Answer 4 · answered Nov 26 '18 at 09:24

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)

How to query values from xml nodes?

4 Answers4

Linked