Can I have SQL incrementally count XML elements while parsing?

So this is my first foray into parsing XML, and I’m trying to figure out how to get this to work how I want.

Given the following XML format:

<Tiles>
   <TileRow>
      <TileValue>2</TileValue>
      <TileValue>3</TileValue>
      <TileValue>4</TileValue>
   </TileRow>
   <TileRow>
      <TileValue>2</TileValue>
      <TileValue>7</TileValue>
   </TileRow>
</Tiles>

I want it to put it in a SQL table as the following:

| X | Y | Val |
|---|---|-----|
| 1 | 1 |  2  |
| 1 | 2 |  3  |
| 1 | 3 |  4  |
| 2 | 1 |  2  |
| 2 | 2 |  7  |

Basically, imagine a grid, and each “TileRow” starts a new Row in that grid. Each “TileValue” assigns the Column position in that grid, with the actual TileValue being what’s in the ‘cell’ in that grid.

Is there a way to make SQL ‘count’ each time it passes over an element, or something to that effect?

Answer

Please try the following solution.

It is based on the Node Order Comparison operator in XQuery.

Node Order Comparison Operators

SQL

DECLARE @xml XML =
N'<Tiles>
   <TileRow>
      <TileValue>2</TileValue>
      <TileValue>3</TileValue>
      <TileValue>4</TileValue>
   </TileRow>
   <TileRow>
      <TileValue>2</TileValue>
      <TileValue>7</TileValue>
   </TileRow>
</Tiles>';

SELECT c.value('for $i in . return count(/Tiles/TileRow[. << $i])', 'INT') AS [X]
    , c.value('for $i in . return count(../*[. << $i]) + 1', 'INT') AS [Y]
    , c.value('(./text())[1]', 'INT') as Value
FROM @xml.nodes('/Tiles/TileRow/TileValue') AS t(c);

Output

+---+---+-------+
| X | Y | Value |
+---+---+-------+
| 1 | 1 |     2 |
| 1 | 2 |     3 |
| 1 | 3 |     4 |
| 2 | 1 |     2 |
| 2 | 2 |     7 |
+---+---+-------+